Thanks for peeking, I am looking for a quick kick in the right direction. I have been working on a DB in access 2007 for almost 3 weeks now, in my spare time.

I need to track all the usual stuff like customers, tasks, invoices, payments, etc. Where I am having a problem is with the several types of service orders.

I have 5 completely different services offered by my company and I want to track them all with a streamline, easy to use GUI. I have mostly played with the following design:

One Table named ORDERS - this table holds fields such as
OrderID (autonumber/not the PK)
the PKis actually the OrderNumber field - the reason for this is so I can generate a unique order number which will add lets say 1000 to the OrderID value but also will join a 3 digit code (the order type). So for example, an ideal order number would be ABC1001. The ABC part will vary between the 5 order types. These three digit codes will help sort the different types of orders when looking at all orders/invoices/payments(income) in a report.

Also in this table are the typical stuff like CustomerID, Order Date, Customer PO #, etc etc.

Another key reason I do it this way is so that my OrderNumber, which is incremental due to the OrderID autonumber, will never produce duplicate order number between the 5 order tables (below) Also, I will never end up with ABC1001 but also DEF1001.

anyway, the other 5 tables are actually considered the order details. Information such as addresses, employers, bank accounts, SSNs, etc are all part of vital information I provide to customers. Each table represents a different service, or report, I will need to generate in order to complete my customer's order.

I'm going to stop here because I'm not sure I'm making sense anymore.

Please kick me!

Thanks!

First thing that strikes me is that it sounds like you should change your table design a bit. Are the details of the 5 different type of orders vastly different? As for the different order types, create a separate table with the order types and store the Primary key as a foreign key in the Orders Table:

tblOrderType
OrderTypeID Autonumber, PK
OrderType Order Type

If possible, could you post what you have so far? I know I work better if I could actually see the setup...and remember that not everybody is running access 2007...so save it as a2000-2003 format for the most exposure.

hello,

well I have completey changed things around but I decided to go ahead and upload a copy anyway. I'm sure there are tons of things that could be vastly improved.

Remember, it's not done yet. I still want to add a task handling system, case assignment logs, invoicing functions, etc etc etc.

Also, I tried to save in 2000-2003 format but got an error msg....sorry.

Thanks again!!!!