If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
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!! |
#3
|
|||
|
|||
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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Name not showing ID is | René | Setting Up & Running Reports | 11 | June 29th, 2004 01:40 AM |