Service Database with endless order types to enter/track/invoice/etc. (1 Viewer)

[cbrace09]

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!

 

[Mr. B]

cbrace09,

I not sure that I really follow the entire scope of what you are wanting to do.

One thing that did get my attention is that you have one or more AutoNumber fields in tables that you are not using as PK. While I did understand the reasoning that you gave for what you are doing, I really think that you should make the AutoNumber field to be the PK for the table. Then use the actual OrderNumber field just the way you are using. You can make it work the way you are going, but in my opinion, for what that's worth, it will be much simpler to link your tables through the use of numbers rather that an combination of alpha and numeric values. (just my opinion).

If you have created your relationships for your tables, why don't you create a screen shot of that, or just attach a copy of your database and then perhaps we can tell a little more about what you are really doing.

As for the multiple table approach, I am not convinced that you need multiple tables for basically the same information. Provide a little more detail on this one, please.

I am not sure that I have helped you at all, but just post back and eventually we will all benifit.

 

[HiTechCoach]

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.

I agree with Mr. B that this is not a good reason tot do this. IMHO, it is one of the the reasons you would NOT do it the way you have done it.

If set up properly, the primary key should normally never bee seen by the end user. You can set up your Invoice number to appear to the user as the primary keep by setting it as an index with no dups.

It would really help if you posted a sample database with your tables and some sample data (nothing confidential)

 

[Mr. B]

Boyd is absolutely on with his comments. All I need to say is Amen!!