Hi All
This is my first post, im seeking some help in designing an Access database which i will be using for PC Repairs and other services eg spreadsheet development. Below is the tables which i currently have.

ORDERS - order_id, ord_price, ord_quote, ord_req_by, service_req, parts_req, ord_comments, service_status, payment_status, amount_tendered, amount_outstanding. PK is order_id

CUSTOMER - client_id, cust_firstnane, cust_lastname, cust_ph, cust_email PK is client_id

SUPPLIERS - supplier_id, supplier_name, supplier_ph, supplier_address. PK is supplier_id

I would like to add the field Invoice, but it does not fit into any of the tables above. One invoice may have many orders, one customer may have may invoices, im thinking of creating a new table named INVOICE.

Something like

INVOICE invoice_id, order_id the problem with this is that if invoice_id is the PK it will not work as invoice_id will repeat in this table (one invoice may have may order_id's). Can i make order_id the PK? or is there a better way of designing this database?

Thanks in advance
Mike

Two tables, one for Invoices and the other for InvoceOrders.

-PatP

Thanks Pat
Which fields would go in which table? Is it just a concat PK in invoiceorders?
Thanks

Think of the tables a three distinct "widgets", where you collect information about things. The stuff that applies only to invoices goes into the invoices table. The stuff that applies only to orders goes into the orders table. The stuff that only applies to the association of an order with an invoice (like when it was done, who did it, the PK values from the invoice and the order, etc) go into the invoice orders table.

I'd suggest a quick perusal of Boyce for more details.

-PatP

Your orders table might also benefit by splitting it into an order header table and an order details table. There are data associated with the order as a whole (customer reference, various order dates, etc.) since a given order may (and often would) have multiple items (Virus cleaning, hardware replacment/add, etc.) you would probably want to include an order details table (child of the order header table) to store item specific info. Otherwisae, the service_reqd and parts_reqd fields would contain multiple pieces of information, which is a normalization no-no.

An Access database for tracking spreadsheet development?
My head jus a'sploded.

but its not very likely its a real world requirement after all.....