How do I combine data from multiple child tables through 1 parent

[George Chen]

Parent table fields:
ProteinTableID ProteinTableName Trait1 Trait2

Child table fields:
Protein MeanAmount Protein TableID

There are approximately 1000 records in each child table.
There are approximately 20 child tables.
ProteinTable is a list of the 20 child tables.

I want to be able to identify child tables by trait and then receive an
output of all 1000 records in each child table that has been identified.

I can do it with a single child table, but when I try to do it over multiple
child tables there is no output.

Could somebody tell me what I am doing wrong and/or if there is a better way
to design the database to make it work better?

I am a novice.

Thanks,
George

 

[Lord Kelvan]

why do you have 20 child tables is there not a way to combine them
into one table.

Regards
Kelvan

 

[George Chen]

why do you have 20 child tables is there not a way to combine them
into one table.

Regards
Kelvan

I get the data in chunks that I would like to dump into Access as child
tables. It seemed a more modular way to handle the data rather than put them
all in one table.

 

[Allen Browne]

Create a final table to hold the data.
Add an extra field to distinguish whatever the difference is between your 20
tables if you wish.

Execute 20 append queries, to add the data to the final table.

Use the final table as the source.

 

[Lord Kelvan]

the problem with that is it dosnt work it is not propper DB design

your child table should be

Protein
MeanAmount
ProteinTableID
Dateofinsert or Datadumpid

something along thoes lines

 

[John W. Vinson]

On Mon, 3 Nov 2008 11:52:02 -0800, George Chen <George

Parent table fields:
ProteinTableID ProteinTableName Trait1 Trait2

Child table fields:
Protein MeanAmount Protein TableID

There are approximately 1000 records in each child table.
There are approximately 20 child tables.
ProteinTable is a list of the 20 child tables.

I want to be able to identify child tables by trait and then receive an
output of all 1000 records in each child table that has been identified.

I can do it with a single child table, but when I try to do it over multiple
child tables there is no output.

Could somebody tell me what I am doing wrong and/or if there is a better way
to design the database to make it work better?

I am a novice.

Thanks,
George

What are the meanings of these tables? What real-life entities do they
represent? What is a "Trait"?

Having a different table for each trait is SIMPLY INCORRECT. Data should never
be stored in a table name. Traits should be stored as data in a field. I'm
guessing that you have a Many to Many relationship: each Protein is related to
zero, one, two *or more* Traits, and each Trait is related to zero, one two or
more proteins. if so you need three tables (not 21):

Traits
TraitID
<information about the trait as a thing in itself>

Proteins
ProteinID
<fields about the protein, e.g. names>

DataTable
TraitID <link to Traits>
ProteinID <link to Proteins>
<information about this relationship, e.g. MeanAmount if that's relevant>
[/QUOTE]