database not working properly...HELP

[Guest]

I set up a database with four primary tables: customer, orders, order
detail, shipping and shipping detail. There is an additional table called
'monthly open orders'. Originally the tables were designed with primary
keys: customer id, sales order number and invoice number. When I started
inputting the data I realized that the sales order number and invoice number
could not be primary keys because several items were on each order and the
sales order number needed to be repeated. Also, the invoice number was
repeated as well. Sooo...I took the primary key designation off those fields.

But now, the data isn't flowing properly. Sometimes I have more orders on
the order table than I do in the order detail table and vice versa.

To try and solve this problem I decided to use an order id number and make
that the primary key in the orders, order detail, shipping and shipping
details tables.

Two ways to go on this....first, if I just deleted all data and started over
would the order id number, set as a primary key, keep this problem from
recurring and would the downstream tables be properly populated?

or

Second, if I assign order id numbers to the records in the orders table,
would the order id numbers automatically flow thru to the right records? I
have a gut feeling that they won't but hope I'm wrong.

I have until Dec 1 to have this up and running smoothly and I really need as
much feedback as quickly as possible. If I need to delete the data and start
over, I need to do this Monday or Tuesday to allow enough time.

HELP!

 

[Tim Ferguson]

I set up a database with four primary tables:
customer,
orders,
order detail,
shipping and
shipping detail.

Actually I make that five.

Originally the tables were designed
with primary keys: customer id, sales order number and invoice number.

You need to be a lot more specific about how your keys are set up...
There certainly seems to be something missing here.

- A CustomerID I understand.

- I don't know what SalesOrderNumber is -- is this the identifier for
each Order?

- I don't know what an InvoiceNumber is either. I assumed this would
identify each Order but perhaps not.

= You don't say what are the identifiers for the OrderDetails table
(presumably SalesOrderNumber + LineNumber?), Shipping table (presumably
SalesOrderNumber + ShipmentNumber?) or ShippingDetail (no idea).

When I started inputting the data I realized that the sales order
number and invoice number could not be primary keys because several
items were on each order and the sales order number needed to be
repeated.

Huh? If you have the same SalesOrderNumber on different Orders, how do
you know whom to bill or where to send the stuff? Surely you have to have
some kind of reliable identifier -- that's what the PK should be.

I think it might be helpful for you to review the NorthWind database that
comes with Access -- it shows a pretty classical layout for this kind of
business.

Hope that helps


Tim F