Query With Variable Field

[stapleford]

Hi All,

I have created a query to price items for me for specific customers. So far i am able to choose a specific customer, products and the base price associated with those products but now i am stuck with the discount rate that is specific to each customer.

I have a combo box on my form to choose what customer you want, but in my master table i have to list the customers across the top in each of their own columns. Each customers has a different discount rate for each product.

So my problem is I do not know how to make a query that returns the discount rate because the field has to be a variable depending on what customer you choose. Basically i would like a field in my query that is linked to my combobox.

Item # Description Base Price Cust 1 Cust 2 Cust 3
item1 test1 $ 100.00 0.98 0.97 0.97
item2 test2 $ 150.00 0.99 0.96 0.93

Is there anyway to build this into my query in the design view or is this more of a job for code?

Thanks in adavance,
Andrew

 

[TexanInParis]

Andrew-

Please explain what you mean by "master table." You should NEVER store calculated values in a table, much less use a repeating group such as suggested by your Cust 1, Cust 2, etc. columns.

If the discount rate is in the Customers table (or is it by product or product class?), then it would seem to be a simple matter to calculate the price to be paid if you also have the "list" price of the product. What is the structure of your tables, and what is the business problem you're trying to solve?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[stapleford]

Hi John,

Essentially the idea is to streamline our pricing process, currently the task is carried out by a 100mb excel sheet that is incredibly slow.

The master table basically just holds the data, there is no calculated values in this table. I have the item number, the item base price and then the idea was to have each customer in their own column. The reasoning behind this is because potentially each customer could have a different discount rate for each product (100 customers x 17,000 items). In doing it this way I can easily locate a product and change its discount rate to make the price fall in to line of what the customer is willing to pay.

The problem with this structure is that in order to make the query work, I need the field in the query that would pull the customers discount rate to be a variable because the column that will need to be selected is dependant on which customer is required.

This may seem kind of like a round about way to doing the task but I am by no means an access expert.

Thanks,
Andrew

 

[TexanInParis]

Andrew-

You're having problems because your structure is all wrong. The tables should look like this:

tblCustomers: CustomerID, CustName, CustAddress, etc.

tblProducts: ProductID, ProdName, ProdVendor, ProdDescription, ProdSRP, etc.

tblCustProdPrices: CustomerID, ProductID, CustProdPrice

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[stapleford]

I understand the structure you have proposed but i don't understand how this would work for more than one customer.

Are you suggesting that i have a record for each productID and CustomerID?

Example under tblCustProdPrices :

Customer1 - Product1 - Price
Customer2 - Product1 - Price
etc.


If I understad you right I would have 100 records for each productID and a total of 170,000 records?

Thanks,
Andrew

 

[TexanInParis]

If EVERY customer has a unique price that's not the SRP (Suggested Retail Price), then yes. If only some customers get special prices, then you would need rows in that table only for those customer / product combinations. You could set it up with a Left Join to the customer prices table, and if you get no row, then use the SRP.

 

[spikepl]

You mean you agree a specific price one by one for each one of the 1700 items with each customer? Not 10 % discount on one group of items and 20% on another or something like that?

 

[TexanInParis]

Andrew-

That depends on your business rules. If the special prices are decided individually by customer and product, then you need an entry for every product and customer who gets a special price. If the price is calculated as a discount percentage, then there might be several ways to deal with it. If a customer gets an across-the-board percentage discount, then the percentage can be part of the customer record. If different percentages are offered on different product categories, then perhaps you need a table like:

CustomerID
CategoryID
PercentDiscount

What you want to avoid at all costs is a repeating group such as you suggested in your original post.

 

[stapleford]

Spikepl for the most part there is a common percentage used for item classes but I still need the ability to change one product if need be to make it more competitive in the market.

I will try the way you have suggested John, but i am just worried about the maintenance side of things if i had to do 100 records per item.

I know it may not be correct structure but, is there infact a way to make a variable column in a query?

Thanks,
Andrew

 

[TexanInParis]

Andrew-

Keep in mind that you can keep the default price for all customers in the Products table. You would only have to enter in the Customer Products Prices table the exceptions for individual customers and products. As I noted earlier, you can use an Outer Join to pick up any relevant matching records, and if you get a Null value, then default to the price saved with the product.

 

[spikepl]

Do what John advised.

You do NOT create separate containers for data of the same kind, because then you need to search not only for data but for the container. This is why you keep asking about "query with variable field". That is a huge normalization error. (Google "database normalization" and do a tutorial on table/datastructure design). There is no normalization "police" The price for normalization errors is unamaintainable bloated code and database, and huge problems doing anything with the data (with same kind of data all over the tables).

Access can easily handle a million rows, so don't worry. And Access is NOT Excel. Forget about how the data is laid out in Excel. You can show it in the same way in Access, but you should not store it like that in a relational database.