Conditional Lookups

[js22]

Hello,

I have a database that I'm working on that I need some help with. The database manages procedures that we run in the lab where I work.

My database has 3 main tables: Samples, PCR, and PUR.

Samples has the vital stats about the plants we collect (species, genus ...). PCR is a table to show which samples we tried to amplify the DNA for. PUR is the purification of the PCR products. [sorry for the vague descriptions]

On my PUR layout (list) I have 5 fields.

The first is an ID for the lane number in the purification gel (1,2,3, ...)

The second field is the PCR# that they reaction comes from. (1,2,3,...)

The third field is what PCR reaction (rxn) will be in the lane on the purification gel (1,2,3...)

The fourth field is the sample number that corresponds in the PCR reaction (123-ABCD)

The fifth field is the species of the sample (Scientific Jargon)

Finally my question - I need to do a lookup. Based on the PCR# and Rxn# I need it to find what the species is for each lane on the purification.

I need to look up field four based on fields 2 and 3 (PCR1-rxn1 = 123-ABCD, PCR1-rxn2 = 234-BCDE, PCR1-rxn3 = 345-CDEF...)

This seems confusing to me but I can't figure out a better way to explain it. Basically it's a two field lookup: use the PCR# and the Rxn# to find the sample name in the "PCR" table for the lane# of the PUR, and then based on that sample name look up the species in the "Samples" table

If anyone can interpret this and has any ideas let me know.

Thanks

js22