one to many relationship help

[Smitch1]

My Database has tables like this:

tblOrders
OrderID
CustomerID
Date
....etc

tblOrdComponents[/u
OrderID
ComponentID
Qty

Components
ComponentID
CompDescription
Price

I want to setup a 1 to many relationship between tblOrders and tblOrdComponents as one order can include many components.

At the moment the OrderID in tblOrders is an autonumber. To create the relationship OrderID in tblOrdComponents must be an autonumber as well.

How do I get the two ID's to match up all the time. I will be entering the data in using a form. Should I make the two OrderID fields a text field, then have the form create the autonumber somwhow and enter it into both tables?

Thanks for your help
Smitch

 

[Remou]

It does not need to be an Autonumber, it just needs to be a number.
tblOrdComponents
OrderID - FK, numeric
ComponentID - FK, numeric
Qty
If you make tblOrdComponents a subform on a form bound to tblOrders and the Link Child and Master fields are set up properly (the wizard will do it), Order Number will be filled in automatically when you add a component.

 

[PHV]

Have a look at the Northwind sample database you should have installed with access.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Lilliabeth]

At the moment the OrderID in tblOrders is an autonumber. To create the relationship OrderID in tblOrdComponents must be an autonumber as well.

That is where you are mistaken. If OrderId is an autonumber in tblOrders, then it should be a long integer in tblOrdComponents.