Joined Tables

[Guest]

I am working on a customer orders database. I have two tables - the Account
table (containing customer info such as name, address, etc.) - and the Order
table (containing order information such as quantity and item description).
Is there any way to set the tables up so that when an account is added to the
Account table, the AccountID (primary key) is automatically added to the
AccountID field in the Orders table (foreign key)? Or is there another way
for the user to not have to enter the AccountID manually into the Orders
table when setting up the first order for an existing customer?
Thanks!

 

[tina]

create a standard main form (Accounts table) / subform (Orders table) setup
for data entry. make sure that (in the main form) the subform's
LinkChildFields and LinkMasterFields contain the names of the foreign key
field (of the subform table) and primary key field (of the main form table)
respectively. then the foreign key value will be entered in the subform
automatically, each time a new record is added in the subform.

btw, if a single customer may order multiple items on one order, then you
probably need four tables, not two:

tblAccounts - containing customer info such as name, address, etc.
tblItems (or tblProducts) - containing information about each item such as
ItemName and ItemDescription.
tblOrders - containing info about the order as a whole, such as OrderDate.
tblOrderDetails - containing information about each item ordered such as an
ID value from tblItems, and a quantity.

hth

 

[Guest]

Thank you very much! Your solution worked!

create a standard main form (Accounts table) / subform (Orders table) setup
for data entry. make sure that (in the main form) the subform's
LinkChildFields and LinkMasterFields contain the names of the foreign key
field (of the subform table) and primary key field (of the main form table)
respectively. then the foreign key value will be entered in the subform
automatically, each time a new record is added in the subform.

btw, if a single customer may order multiple items on one order, then you
probably need four tables, not two:

tblAccounts - containing customer info such as name, address, etc.
tblItems (or tblProducts) - containing information about each item such as
ItemName and ItemDescription.
tblOrders - containing info about the order as a whole, such as OrderDate.
tblOrderDetails - containing information about each item ordered such as an
ID value from tblItems, and a quantity.

hth

 

[tina]

you're welcome

Thank you very much! Your solution worked!