Table Question (1 Viewer)

[J. MULLAHEY]

I have been trying to create a database that will be simular to Northwind (access sample co.) only in that it is an accounting database of sorts. My main goal is to be able to track jobs (open and closed), invoices (paid and open), Commissions due sales people, along with commissions paid to sales people. Sales Tax $$ Due to Various tax codes. At the end of each month I would like to purge out the paid invoices into a paid invoice file, and closed orders into a closed order file. Tracking The sales comm/orders is really my primary goal, as Peach Tree our accounting system isn't structured to track our commissions the way we disburse them. So far I have created the following tables, and not sure where to go next. May need more fields, but not sure. Tables look like this: I don't need as much information as Northwind or a full fledged accounting database, but just enough to make my life misserable. I have been using a very old custom database but the old computers are failing fast, and I have to get this new database in place before the last dog kicks the bucket. I feel I'm on the right track, but just don't want to waste alot of time barking up the wrong tree. Any suggestions would be very much appreciated.

Orders Table:
Ryan Number (Primary Key)
CustomerID
SalesID
Customer P.O.
Ship Date
Ship Via
Frt $ Amt
Shipped Co
Address1
Address2
Ship City
Ship State
Ship Zip Code

Customer Info Table:
Customer ID (Primary Key)
Company Name
Bill to City
State
Ship Co1
Ship City1
Ship State1
Ship Co2
Ship City2
Ship State2
Tax Code
Sales Id
Query Code

Orders Detail Table
Ryan# (Primary Key)
Maid Sales ID
Sales1 Comm Earned
Sales1 Comm Pd
Sales2 ID
Sales2 Comm
Sales3 ID
Sales3 Comm
Supplier ID
Qty
Unit Price

Salesmen Table
Sales ID (Primary Key)
First name
Last Name
Notes

Suppliers Table
Supplier ID
Supplier Name

State Table
Abbreviated State
Description

Tax Codes Table
Tax Code
Description
Tax Rate

 

[Pat Hartman]

A good start but you need some changes.

1. Names should NOT include spaces or special characters. So [Customer P.O.] should be CustomerPO, [Frt $ Amt] should be FrtAmt, etc.
2. If customers have only two addresses - billTo and ShipTo, you can keep them in the customer table. ShipTo should NOT be duplicated in the Order table. If the ShipTo can vary by order, it should NOT be kept in the Customer table but should be in the Order table. You can default the ShipTo address in the Order table to the BillTo address from the Customer table to minimize typing.
3. I don't know what Sales1.., Sales2.., and Sales3.. are but they seem to be a repeating group. In other words, sales has a one-to-many relationship with Order. So, your OrderDetails should have an autonumber primary key and the RyanNumber would be a foreign key so you can join the Order and OrderDetails tables. Each OrderDetails row would contain information on ONLY ONE sale. Therefore, OrderDetails may have many rows per order as it does in Northwind. If SalesId actuallly comes from another application, the key to the OrderDetails would be RyanNumber and SalesId rather than an autonumber.
4. If the customer and order information already exist in another system and you can link to that system via ODBC, do NOT duplicate the data in your Access application. Include only tables that you need for the functionality that you are trying to build. You don't want to create maintenance problems caused by the data becoming out of sync.

 

[J. MULLAHEY]

Thank you Pat............I will clean up my database and do another post with the ammended tables. I understand what I need to do and I feel I can get it a little closer to what it should be. I read an article that someone in tek-tip forum suggested I read. It was a microsoft article, and for some reason after reading it the lights came on. And now with your comments I think I can advance to stage two. I'm sure I'll be posting to make sure I'm staying on track. I really find these forums the answer to figuring this whole thing out. I'm so glad I ran across this website. Thanks again for your comments. Jude