The ideal way to have a pricelist for separate clients.

[marcfl]

Hi Everybody!

I've created a database for the company I work for and there's only one more thing left for me to do! (Which you can imagine I'm both happy and sad about.)

I suppose the main problem I have is figuring out what the best way is to have a unique pricelist for some clients but not for others. At the moment I have a form that is used to create order forms for our clients in which there is a subform where the products that are required are selected.

I was contemplating creating a Combobox where a client could be selected and when that is done the Record Source of the Subform would change and that would load the relevant pricelist. However I'm sure there must be an easier way to do this as I'm not exactly good when it comes to writing code with VBA.

Any suggestions?

Cheers,

Marc.

 

[fneily]

Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

It would be interesting to see your table structures if you would post them.

So you have a single client that can have many items and an item can be bought by many clients. A many-to-many relationship. The price of an item has a base value. Any change from that is a markup or deduction. That difference goes into the junction table.
tblClient
ClientID Primary Key
FirstName
etc.

tblItem
ItemID Primary Key
Description
Price
other item fields

tblTransaction (junction table)
TranID Primary Key
ClientID Foreign Key
ItemID Foreign Key
Markup/Discount (may be a percentage, constant, whatever)
DateBought
other COMMON fields

So when a Client is selected, the actual cost would be a calculated field.

 

[marcfl]

Well that's not exactly how it works. Below is a link to the relationships of my db.. Bear in mind that this is the first one I've made so no laughing!!

http://i379.photobucket.com/albums/oo238/marclf/dbRel.jpg

Basically, we have many clients that purchase a varying number of products, some clients have their own pricelist whereas others use our default pricelist (which contains all of our products, the others only contain 20 or so products).

 

[fneily]

If this is really your first attempt at a relational database, then let me offer you my kudos. Well done in normalization. Most people just never get it.
Now some suggestions: Take UnitPrice out of the tblOrderDetails. It's already in tblProduct.
You have Comments1, Comments2, Comments3. Violates first normal form - duplicate column headings (drop the number). I would create a comments table.
You have fields called Name. They should be broken down into FirstName, LastName. If you ever do a mail merge or something in the future, you'll have to parse Name. Just split it up front.

Now for your problem: You already have a Products table so we don't want to retype Product names. You already have a Customer table so we don't want to retype customer names.

You could create another table:
tblSpecialPrices
SPID Primary Key
CustID
ProductID
Price

So this stores the customers and their specific products with their special price.

I would then have a checkbox in the main tblCustomer that would indicate if they have a special price list.

Now when they choose a client, you could have code (like using the Dlookup) to check to see if that client has a checkmark. If no, then the source for the pricelist would be a query ran against the regular product table. If yes, then it would run a query off tblSpecialPrices connected to the Product table to get the name.

Just adding that new table should help keeping everything normalized.

 

[marcfl]

Cheers for the help fneily I really appreciate it! I'm going to try to make those changes now will probably take me all day if not longer. Sorry i didnt reply sooner but I was off sick yesterday so I didnt get the chance.