How do I join parent table to children???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lilbomshl - 31 Dec 2004 03:15 GMT

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

Reply to this Message

Duane Hookom - 31 Dec 2004 05:42 GMT

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.

Signature

Duane Hookom
MS Access MVP

>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
[quoted text clipped - 12 lines]
> doesn't
> work either!!! HELP!!

Reply to this Message

John Vinson - 31 Dec 2004 07:07 GMT

>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.
[quoted text clipped - 6 lines]
>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]

Reply to this Message