How do I join parent table to children???

[Guest]

I am designing a database. My parent table has 3 separate fields that make
up the primary key, a two digit number, an autonumber, and a 2 letter field.
These are then concatenated to show a project number i.e. 04-001-AH. But now
I need to join this table together with some children tables (that will have
one-to-many relationships). How do I do it??
I've tried just using the autonumber part of field to join to just a number
field in the child table, but it can't recognize the table relationship.
I've tried concatenating the fields in the parent table first in a query and
then trying to create a relationship to the children tables and that doesn't
work either!!! HELP!!

 

[Duane Hookom]

If you have an autonumber, make it the single field primary key. Add a long
integer field to your child table to use as the Foreign Key field.

 

[John Vinson]

I am designing a database. My parent table has 3 separate fields that make
up the primary key, a two digit number, an autonumber, and a 2 letter field.
These are then concatenated to show a project number i.e. 04-001-AH. But now
I need to join this table together with some children tables (that will have
one-to-many relationships). How do I do it??
I've tried just using the autonumber part of field to join to just a number
field in the child table, but it can't recognize the table relationship.
I've tried concatenating the fields in the parent table first in a query and
then trying to create a relationship to the children tables and that doesn't
work either!!! HELP!!

You can join two tables on up to TEN fields, joining matching field to
matching field.

HOWEVER - your use of an Autonumber here is probably NOT appropriate.
An autonumber is unique in its own right; combining it with other data
doesn't make it any "uniquer"! Also, if you're expecting it to start
over with 001 on January 1 2005, it won't; it will keep incrementing
and soon exceed 1000, breaking your design.

I would suggest two Integer fields and a two byte Text field, in each
table. To relate the tables, join the first integer to the first
integer, the second to the second, the text to the text; set the join
type on all three join lines in the relationship window. You'll
probably want some VBA code to sequentially assign the middle number.

John W. Vinson[MVP]