one-to-one relationship?

  • Thread starter Thread starter mr.bungle
  • Start date Start date
M

mr.bungle

One table or two?

My main table has the following fields:

Enquiry_ID (autonumber)
Enquiry_Date
Client
Description
Type


When we receive an order, the following info is added:

Project_ID
Project_Date
Status
Engineer
Value
Client_Order_No


Is it easier to have all the above info together?
Or should "enquiries" & "orders" be split, and linked by Enquiry_ID?

I tried the two table approach (with a "one-to-one" relationship), but had
trouble getting it to work right.
The main problem was creating a form which would allow me to add an order
(by selecting an enquiry NOT already a project, from a combo list).
I know this should be straightforward, but I just couldn't get it.

Any help appreciated.
 
One table or two?

My main table has the following fields:

Enquiry_ID (autonumber)
Enquiry_Date
Client
Description
Type


When we receive an order, the following info is added:

Project_ID
Project_Date
Status
Engineer
Value
Client_Order_No


Is it easier to have all the above info together?
Or should "enquiries" & "orders" be split, and linked by Enquiry_ID?

I tried the two table approach (with a "one-to-one" relationship), but had
trouble getting it to work right.
The main problem was creating a form which would allow me to add an order
(by selecting an enquiry NOT already a project, from a combo list).
I know this should be straightforward, but I just couldn't get it.

Any help appreciated.
Click to expand...

I would suggest keeping it all in one table, if it truly is one-to-one
information. Having fields that are null until some business event
occurs, especially if there aren't too many of them, is perfectly
acceptable. Splitting them into two tables usually isn't worth the
trouble.

To retrieve orders without enquiries, you can query by certain statuses,
or ProjectID not Null.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
 
Back
Top