B4 I get stuck in

[Richio]

I am just about to start a new project (not that i have enough on the go at the moment), but before I get stuck in I was wondering if anyone could give me ideas on which way to go.

The project will be a quotation system - I will base it on the "Order entry" template in access - (albeit completely ripping the guts out of it and putting it back together in a much more user friendly format)

It will do what I want in its simplistic format - ie product / price / quantity / discount / totals etc but my complications are as follows (thinking out loud here - it helps me.....talking to myself)

1. I have alot of products (several thousand) I want to be able to load these into categories. So in the order section before I select the product, I select a category - only products in this category are then shown. (10 categories)

Will I need to set up different tables for each category? (which would make sense for updating price lists) - will this mean having many subforms on the main order or is there another way.

2. Each product can have up to 9 prices dependant on colour of the overall order. It would be nice to be able to enter all products with a colour choice for the whole order. So an order can be revisted / amended by changing a drop down box from say blue to red and it will recalculate all the prices for each product from a different band.

I am thing along the lines of product / price1 /price2 etc in the table and selecting the column dependant on the colour - not sure how at the moment though!

Once I get these two decisions made in the planning stage it should be plain sailing - any feedback / other ideas would be appreciated before I spend days going in the wrong direction however.

Richio

 

[mission2java_78]

You mentioned something about different pricing based on a color...Im thinking more on the lines of a many side relationship here.

ProductID Color Price
1 Red 10
2 Blue 11
1 Green 20
..........

A product may have many different combinations such as color and price..therefore I feel you need a table to store this information.

As for #1 you will need three tables here:

tblProducts
tblCategories
tblProductCategories.

tblProductCategories will hold 2 values..both the primary keys of the two other tables...That way each product will have a category and each category has a product associated with it. You can look at it in 2 ways here each product has a category / a category can go back to several different products. It all means the same.

HTH
Jon

 

[Richio]

Thanks for the advice - I can see where you are coming from on the category side....but a little lost still on the colour pricing.

Alternatively - I have been surfing the web looking for quotation software to save me a job! - seems to be alot of quite complex stuff around - does anyone know of a simple program or database solution for this?

Thanks

 

[mission2java_78]

A customized version to fit your company needs? Get real...thats only possible by you. Sure there are samples out there for quoting / proposal systems...but you wont find exactly what you want. I don't see any problems in what you are trying to do. Im working on a proposal / quoting system right now. Although I dont really need categories and so on which you have. Grab paper and pencil and sketch out your table structure...its real simple when you start drawing...stuff starts clicking in.

Jon