How do I join parent table to children???

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!!

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.

--
Duane Hookom
MS Access MVP


"lilbomshl" wrote in message
...
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!!

On Thu, 30 Dec 2004 19:15:03 -0800, lilbomshl
wrote:

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]