Multiple Price Columns

[kmwhitt]

I am using Access 2003. I am trying to create a pricing utility for a cabinetry line. I have 158 products that all have 8 different price groups depending upon the door style the user selects. I am at a loss as how to structure the tables. Once the user selects the door style and looks up each individual product, the subform should only retrieve the price column for this particular door style. Should I have 8 different tables for the prices that are sublinked to each door style or is there an easier way? In the future it may become necessary to add more products and/or door styles and it would be quite a chore to edit 8 different tables. Any suggestions? Thanks, Kevin

 

[pbaldy]

I see one table, 3 fields: Product, Door Style, Price.

 

[Niroth]

You can do it as pbaldy suggest, or what I have done is to combine the product and door style into one unique productID, so if you have 158 product lines, and 8 door styles, that would mean 1264 products. You can configure your productID such that it contains the information from your product lines and their door styles, for example p1d1, p1d2, p1d3 etc. so that you can easily set your query to search for each product, when product number 1 is called, the query criteria would be productID=like "p1" & "*" etc. When they select on the door stlye, let the saleTable store this uniqe ID. And you can then link the saleTable and the productTable to call up any information for reports etc. Everytime you add a new product line, let access add the productID for each product with their different door styles for you. This method involves more work but I found it's easier for me to call up the information later on because I have a unique ID for each product even though they have two characteristics.

 

[Rabbie]

Another thing to bear in mind is that you may need to change your prices in the future so you need to record the actual price charged for a particular item that will not change when you update your pricelist.

 

[kmwhitt]

pbaldy -

I'm not sure I follow you. Just three fields - product, doorstyle and price? There are 158 products, 8 doorstyles and 1,264 prices. Are you suggesting a unique product for each doorstyle within the same table as Niroth mentioned that contains 1,264 records? I would think this to be difficult to update when price increases occur. Let me run another idea by you and see what you think. What if I have a doorstyle table with 8 records and 8 different tables (one to represent each doorstyle) that I could use in a subform? These 8 additional tables would have the product name and 1 price column unique to that particular doorstyle. Down the road when it becomes necessary to update pricing, I could query all of the different price columns into one form which would make the update easier.

Rabbie -

Thanks for the headsup on price increases. How am I able to make the content of the older quotes static?

 

[Rabbie]

-----
cut -----

Rabbie -

Thanks for the headsup on price increases. How am I able to make the content of the older quotes static?

What I do is to copy the price from the pricelist into the quotedetail record so it remains part of the quote data independent of any changes to the pricelist.

 

[gemma-the-husky]

if you have 158 products, and 8 door styles then you DO really have 1264 different products, and therefore 1264 different prices

UNLESS you have a more complex situation where several doors are simlar and are always priced the same - in which case you may only have 30 different doors as far as pricing goes. - say then you could have 30 types of doors, and 8 doorstyles, therefore only 240 prices.

But you may find this too restrictive, as it would limit your ability to price a door individually

-----------
do you just do doors, or do you do door furniture as well (locks handles hinges etc) - thats a whole new ball game - far more complex, cause you generally need a way of handling product sets

 

[pbaldy]

Just three fields - product, doorstyle and price? There are 158 products, 8 doorstyles and 1,264 prices. Are you suggesting a unique product for each doorstyle within the same table as Niroth mentioned that contains 1,264 records? I would think this to be difficult to update when price increases occur. Let me run another idea by you and see what you think. What if I have a doorstyle table with 8 records and 8 different tables (one to represent each doorstyle) that I could use in a subform? These 8 additional tables would have the product name and 1 price column unique to that particular doorstyle. Down the road when it becomes necessary to update pricing, I could query all of the different price columns into one form which would make the update easier.

That's what I'm suggesting, yes (I would also have tables for products and door styles). I would NOT have 8 price tables. What happens when somebody dreams up a 9th style? Your whole design has to change. You can update the table I mentioned just as easily by restricting to a specified door style.

 

[kmwhitt]

pbaldy -

Thanks again for the advice. Just to clarify you recommend 1 table for products, 1 table for doorstyles and 1 table that combines the other two with prices? When editing is necessary I could filter by doorstyle? Would you please show me how you would build the relationships? Would the different doorstyles be subsets of the products? Or would you actually use duplicate products and have 1,264 records? Sorry for all the questions.

Thanks!

Kevin

 

[pbaldy]

Based on your first description, you have 1264 potentially different prices, do you not (158 products each with 8 different prices)? If so, yes, 1264 records. This pricing table would have a one-to-many relationship on product ID with the product table, and a one-to-many relationship on style ID with the style table. Some people would have an autonumber as the primary key on this table, others would have make the product and style fields a compound key.