check boxes (1 Viewer)

[J. MULLAHEY]

I am just getting my feet wet on using access, as you probably can tell from my previous posts. I am probably trying to do a project that is very complicated, but I want to try to go about creating this as painlessly as possible. I have read, and read, and read and read. Forums, and data in books. Am I on the right track, that's all I want to know. Do I put a check box in my tables for open/close , paid/unpaid. Do my Sales Tax percentages, that can change as the government increases of decreases go in tax table or is that yet a different table? Please respond if you have any answers. I'm trying to accomplish this myself, because I am the main person tracking this info. We are a very very smart mfg rep firm, and our Peachtree system just doesn't do enough, and is just not flexible enough to meet our needs. Any input welcome. Jude

 

[Opengrave]

Putting check boxes in your tables for open/close or paid/unpaid is possible. As far as the tax thing goes it really depends on what other data you may be trying to track. This is all really part of the dark art of database design. If you can post some specifics about what you are tracking and what you want to do with the data some of the kind folks here may be able to give you some direction. To me it sounds like you need someone to help with database design and then you could take it from there.

 

[J. MULLAHEY]

Wow! I can't believe someone finally responded to my post. You're right I just need some direction. I will my baby steps and eventually conquer this thing....I know I will.........with a little help.

What I am trying to accomplish is to be able to track commissions on our salespeople. Most orders are straight commission to only one sales person (50% of profit) while others are more complicated. There can be splits between two or more sales people for engineering credit, destination credit and purchase order credit. There is no percise %rule. I just input the figure after I cost account the job. I'm not going to ask access to do that....just the tracking part. (That means tracking open orders/closed orders and open unpaid invoices/paid invoices) Some jobs will have more than one invoice.

I need to track salestax only for the $amt , Local, and percentage

I put the name of supplier in because if I'm going to go as far as to track the order, I might as well know what supplier was used on the job.

Peachtree won't flex to allow me the filters, and sorts I need. It doesn't allow me to put in commissions so on and so forth. That's why I'm trying to use access. I have an old dos system that tracks my information great, but it's a dino, and the new computers don't like the software needed to run it. Date problems etc. had to fake date to get it to still work.

Thanks for contacting me.....I really need too do this......Like I said....with some help I know I can. Thanks again, Jude

 

[David R]

Spend a couple hours figuring out relational database design

Here's a pretty good link off of the Microsoft Support Knowledge Base; there are others: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q100139

Basically the thing to remember is that Access understands 0, 1, or Many of something. If something can have many, it should be stored in another table so it has room to grow, so to speak.
If a calculation cannot be replicated later, it should be stored. If it can be reproduced later from existing data, don't store it but instead calculate it on a form or query.

It sounds like, if I understand correctly, you're concerned with tracking comissions for salespeople, on orders. You mention storing more data about the order but that's a larger issue; are you going to be duplicating data to do so (i.e. are you storing it elsewhere in your system?), or are you thinking of migrating over to Access entirely?

So an order can have more than one invoice possibly, and one or more commissions are tracked from the closed invoices, right?

If so, you'll need four tables so far (make sure each has its own Primary Key).
One to deal with Orders (also called Jobs?). This should store information that is the same for the entire order.
One to deal with Invoices. Include all the invoice-specific material, but make sure you include a field of the same basic type as your PK field from Orders. This allows you to create a one-to-many relationship that associates certain invoices with a specific order.
One to keep track of your Salespersons. This one includes their personnel data you might need for this function (like do they get different rates based on seniority, job function, etc?). Don't worry about linking this to the other tables yet.
That's the job of the fourth table. Because any One Invoice can have Many Salespersons associated with it, and One Salesperson can (we hope) have Many Invoices they've been involved with, you've got a Many-to-Many relationship. For this you need a linking table. All it does is store the association between a specific Salesperson and a specific Invoice, so your queries can figure it out later. Two fields are needed: PK from Salespersons, and PK from Invoices. You can Shift-click on both of them in design view of the table and make them a multi-part Primary Key for this table; this means you can never duplicate that combination of Salesperson with that Invoice again. If this isn't appropriate to your work then just put an Autonumber PK in like usual.

Hopefully that will give you a start. Make sure you read up on defining relationships, referential integrity, and using related tables in queries. Post back if you need more help, or check the archives.