Multiplying fields to produce Discounted Price Rates by Customer

[TimberSales]

Hi

I am attempting to create a pricelist to give to my customers, however I have become lost in the complexity of the query I am trying to perform and could really use some help.

The database is a relatively simple one set up in Microsoft Access 2007 and consists of 4 tables. The query in question should only deal with two of them, the PriceList table and CustomerDiscounts table. Currently each product in the PriceList table is assigned to a product type (ProductCatagoryNo) and each customer has different discounts for each catagory of products which is represented in the CustomerDiscounts table as CustCode, ProductCatagoryNo, DiscPer (the discount percentage).

What I would like to have output is the ProductDecription and Price, where Price is the Sale Price (PriceList.Retail1) less the Customer's discount percentage (CustomerDiscounts.DiscPer) and have this done for each customer in my database, listed ascending by ProdNo but that field doesnt need to be shown.

Edit: It becomes slightly more complicated in the fact that Customer's do not have a discount associated with every catagory of products, so where there is no entry for a given product's catagory under the CustomerDiscounts table it should list the sale price as is (Retail1).

Edit2: Im not terribly familar with Access but I think that the report function might be able to give me the results im looking for?
Any help would be greatly appreciated!

[TheSmileyCoder]

@TimberSales
Any chance you could attach your database here? It would make it alot simpler to write out the query.

[TimberSales]

@TheSmileyOne
Hi TheSmileyOne,

Yes I should be able to post it... Just give me a bit to remove some of the more sensitive information.

Thanks for the assistance!

[TheSmileyCoder]

@TimberSales
Sounds good. I will be going for lunch anyways, so you have some time :P

[NeoPa]

What you should be looking to do here is to join the two tables using a LEFT JOIN (See SQL JOINs for more on this). Where the linked table on the right (that is joined to) has no entry all the fields for that table will contain a value of Null.

The discounted value can then be calculated as the [retail price] * (100 - Discount) / 100, where Discount is actually Nz([Discount], 0). This means that where there is no entry for [Discount] the deduction will be 0.

Welcome to Bytes!

[TimberSales]

Sorry for the slow response very busy day,

I have cut out 99% of the information from the DB such that it should be easy to understand and work with.

Thanks again for the help!


@Neopa Had a play around with your suggestion, managed to get a good way towards what I was after, will give it another try later tonight!

[NeoPa]

If you can't manage to solve it fully then let us know how far you do get and we'll see what we can do to help.

Good luck :)