Table Preference

[Boltman]

Hi Everyone.



I am creating an invoicing program in access 2012. Here is what I need your help with to continue. I want a master table with everything i sell. Lets say I sell widgets. I have 20 customers that buy these widgets, but each customer pays a different price for the widget. When creating an invoice and I make a repeat sale of the widget to a customer, I want to show the date and last sale price to that customer for the widget. I also want to be able to change the raw cost of the widget in the master table, and it will automatically update each customers price using the same % of markup.

My questions is should each customer have an individual sales history table, or should sales history for all customers be in one table sorted by customer number.

Thanks for your help everyone!!
Keith

[orange]

I'd like to see a jpg of your tables and relationships.

I'd suggest a single tblSalesHistory, since you can find individual customer sales info via a query.

[Boltman]

Hi Orange
I am just beginning to set the relationships, but thought I would let you see what I have done so far. I appreciate your (and any one else's) input, and if you see a glaring mistake on the jpeg please let me know. This is my first attempt to build a database. Thank You

[rpeare]

Does a customer have a uniform markup percentage across all parts? Or is the markup percentage variable by part number for the same customer?

If it's the former the easiest thing to do would be to keep their markup percentage on the customer table and apply that to every invoice (i.e. write the percentage to the sales table because the percentage may change over time and if you need to reprint old invoices you want the correct percentage to show).

There's no reason for each customer to have a separate PO/Detail table for this, you just need to make sure you're carrying your current markup percentage on the sales table.

If you have a variable markup rate by part for individual customers it's a little more complex but can basically be accomplished the same way.

[Boltman]

yes rpeare each customer has a unique markup on each item. this is one of the reasons i am attempting to put this together in access instead of buying an off the shelf software to do this. quickbooks, peachtree etc will not provide unique pricing to each customer on each item without assigning a unique part number to each customer on each item. i have a friend who just spent several thousand dollars for a large accounting package to do this. i am a one person operation so i decided to try this. thanks for your imput.

[rpeare]

You don't have to create a unique item number for each client, but you will have to set up a matrix table that includes a unique identifier, the customerID, the PartID and the markup percentage at the very least. From this point you can do a couple of things.

1. On each part the company buys you attach the percentage to the sales record so that you can reproduce an old invoice. You probably wouldn't have to do much for this to happen, every time you switched a percentage for a client and you went to your data entry form you could just have it inherit whatever was on your matrix table.

2. Create a range of dates that a price is active for a specific client. This way is probably the more 'correct' way because you can calculate everything without having to store the percentage on every record, but if you're new to programming it's a lot more work to set up.

In case 2 your matrix table would have to include the start date and end date.