Tables Questions

[Tmom]

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

 

[jdegeorge]

Have you tried using the database wizard? There are many company type samples there from which you may choose a wide variety of tables and fields.

If you don't have the wizard, I believe you can download it from Microsoft. It is very helpful when you don't want to start from scratch.

Hope this helps.

Jim "Get it right the first time, that's the main thing..."

 

[Tmom]

Jim: I have in fact tried the way of the wizard, and there just isn't anything in there for what I am trying to accomplish. There is order tracking, but not a combination of what I need to do. Northwind is the closest to what I need without as much detail. I am so new at this that I'm struggling on the steps I should take to get from point a to point b. I don't want to put it all together only to find I left some important aspect out in the beginning. We a very very small manufacturer rep firm. We had a antiquated DOS system that worked fabulous, but the darn computers that would accomodate the software are dying rapidly. We have two left, but they keep glitching my zip drive. Do I need to put a check box in my tables or do they go in forms or queries. I need a check box to determin open from closed and paid form unpaid. I'm going to take baby steps in putting this whole thing together, and perhaps with some help from the forums, I will accomplish what I am trying to accomplish. Thanks for your reply Jim I do appreciated all the help I can get. I'm a babe in the woods (so as to speak!) Jude

 

[jdegeorge]

Jude:

How about going off the board to another platform? What about looking into Intuit's Quickbooks application? If you get the professional addition, it'll handle vendors, clients, employees, billing, invoicing, etc. And, it'll let you select the type of business you're using and set up the appropriate chart of accounts.

Just an idea...

Jim "Get it right the first time, that's the main thing..."

 

[Tmom]

Jim: Once again I appreciate the comments you made. But quickbooks pro, and peachtree are basically the same. I set up peachtree at our business. They do ask what kind of business you are when setting it up, but the software is not flexible to the point that you can munipulate fields with sorts and filters. I can't obtain the information that is needed to track our salesmen commissions, I can't track sales tax paid in specific sales tax areas. I can't track jobs that are split between two or more sales people. Trust me, I wouldn't be trying to use access if I didn't think it was the answer. I guess eventually I'll figure it out, but I was just looking for a little input on whether or not I was heading in the right direction with the attempt I'm trying to make. Thank you for your input. Jude

 

[TomCologne]

Hey Jude, don't be...

just couldn't resist. I already had a mighty overdose of db issues, but still a few thoughts after a very quick look at this thread:
The first three tables (from top) have fields like Ship1,Ship2, 3 SalesIDs in tblOrderDetail, which in my opinion is very suspicious. Do the SalesIds really belong in ONE row? Like: Do three salesmen work that customer over, if yes, one at a time or simultaneously?

Shipped Co 'UPS?FedEx?
Address1 'Shipper's or Customer's?
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 'Related to tblOrders.ShipCity ?!?
Ship State1
Ship Co2 'Again, is there a good reason for ShipCo2?
Ship City2 'You may have one, but what about the ones Ship State2 'with 3,4 and more Adresses?
Tax Code
Sales Id 'All by himself?
Query Code

Assuming you want keep track of orders, customer with billing address plus multiple shipping addresses, I would use tblCustomer, ID, Name, BillingStreet etc.. and a separate tblShipAddresses, ID, Name, ShipStreet etc..,CustomerID

Maybe

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q164172

will help you finding the right direction.

We're all in binary hell,

you're not alone, Tom

 

[Tmom]

TomCologne
Explanation:

Customer Info Table:
Customer ID (Primary Key)
Company Name
Bill to City
State
Ship Co1
Ship City1 (Yes related to tblOrders.ShipCity
Ship State1
Ship Co2 (Customer may have more than one ship to co/local
Ship City2 (What about more than two ship locals?)suggestion
Ship State2
Tax Code
Sales Id (There is a main sales person for this customer)
Query Code


Explanation Three way split of commission due on job.
Orders Detail Table
Ryan# (Primary Key)
Sales1 ID (The main salesperson on order)
Sales1 Comm Earned (ditto above)
Sales1 Comm Pd (ditto above)
Sales2 ID (second sales person involved (Eng) (split comm)
Sales2 Comm (ditto above) (they get paid when job complete)
Sales3 ID (third sales person(destination)(split commission)
Sales3 Comm (ditto above)
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

I don't need a complete detail of the order just a summary of the overall invoice/order. I have to be able to track open and closed orders and open and closed invoices. I have to be able to track commissions paid against a job, and commissions still due on jobs. I have to be able to track sales into various tax codes because the % can differ on local. And San Francisco for instance has a special tax form to be filled out just for doing business in their city.
What I'm trying to do doesn't have to be extremely complicated............but it is just enough to boggle my mind. I need access to accomplish my needs, and I just want to make sure I follow the right steps in designing my database. After I get my tables figured out (what is and isn't needed) where do I go from there. Thank you so much for the response. Jude

 

[Tmom]

Tom: I want you to know that I went to the website that you suggested and the lights came on. I want to thank you and Ally For pointing me in a direction that made things alot clearer than any of the books I've read. In that article it's all in laymen's terms. Thanks to Ally for telling me about this sight, and Thanks to you for your direction. I do appreciate it! I will post my new cleaned up database after I clean-up my tables. Perhaps you'll comment again. Have a good day! Jude

 

[TomCologne]

Hi Jude:

sorry, but I couldn't get back to you sooner since I was trying to revive my social life on the weekend. The upcoming weeks are not likely to leave me much time to address your specific problems, so I'm glad that you already seem to be on your way. I will keep this thread marked. Once this is posted I am going to compile a short list of links which were helpful to me, send me your @, (click on my handle for mine), in case you feel like reading more.

You're not alone,

TomCologne