Multiple Records in One Table Relating to One Record in Another

[E_Hardy]

Hi, I am an archaeology student and am attempting to make a database to store and share my graduate supervisors and colleagues ancient DNA data. Aside from one course in Visual Basic in my undergrad I know next to nothing about computer programming. The problem I am having with the database is when one record in one table relates to multiple records in another or vice versa.

Forgive my inability to explain my problem using the correct language, I will do my best to give an example of the issue that I am having.

The database consists of multiple tables, one for samples, one DNA extractions, etc etc. Each sample relates to a piece of archaeological material that will undergo a DNA extraction. Each DNA extraction involves multiple samples and samples will be used in multiple DNA extractions and I need the extraction IDs to automatically show up in a field in the sample table. I have managed to do this when one extraction contains only one sample, but as soon as multiple samples are involved it no longer works.

Please let me know if you need me to clarify anything, as I said before, I know very little about computers.

Thanks!

[mr_vodka]

you need an additional join table that contains both the SampleID and ExtractionID.

[E_Hardy]

Forgive my computer illiteracy but I am not quite sure how to do that.

I think this might be a better way to explain it. So one table is the sample table, it contains a unique sample ID and many attributes(Fig. 1), the problem that I am having is these samples are used for DNA extraction. Each DNA extraction contains multiple samples (Fig. 2), which would all be contained in one record. So multiple records in table are relating to one singular record in a table. When I set up the relationships with Sample ID from the Sample table to the multiple samples in the DNA extraction table it stops the relationship from working (it works when the relationship is one to one). I know this must be possible because it is the same as processing an order in a warehouse, one table contains all the products (samples) which then are processed in large orders (extractions) with multiple products. It is also very need it to be able to auto-enter into the extraction ID fields in the sample table, I can make that work with a calculation function if there is just a one to one relationship but as soon as more samples in DNA extraction record are included it no longer works.

Fig. 1

Sample ID: Site: Preservation: Extraction ID 1: Extraction ID 2: etc etc

VL-16/01-001 VL-16/01 Good DX001 DX004

VL-16/01-002 VL-16/01 Poor DX001 DX004

etc.

Fig. 2

Extraction ID: Sam_ID_1: Sam_ID_2: Sam_ID_3: etc etc

DX001 VL-16/01-001 VL-16/01-002 SER-471-001

DX004 VL-16/01-001 Vl-16/01-002 VER-06/02/-001

Thanks again!

[mr_vodka]

I would suggests checking out the inventory starter solution that came with 12. You can add some products and then go to the invoice layout to see how you can add line items to the invoice.

Also here are some additional demos.

http://fmforums.com/forum/topic/50942-portal-grouping-problem/#entry239210

http://fmforums.com/forum/topic/63425-auto-fill-one-field-with-text-from-two-fields/#entry300150

[E_Hardy]

Thank you! I will take a look at them.