Need query help please

[odin1701]

I have a DB which has several tables that I need to be able to combine into one for reporting. I have a sample DB that I have posted. Here is the basics:

Tables:
Part
Customer
Tier
Region
CustomerDiscount
TierDiscount
RegionDiscount

Part table holds information about parts including cost/selling price, etc.

Customer table holds customer info. Each customer can be assigned a tier and region.

Discounts are offered for specific customers, tiers and regions and are assigned on a per-part basis. So for example in Tier A, Part 1 has $1 discount or whatever. Part 1 can also have a Region 2 discount of $2. Customer 1 can also have a $3 discount on that specific part. So altogether Customer 1's total discount would be $6.

What I am trying to do is get a query that will be able to show this.

The query is somewhat based on the Customer and their Tier/Region codes. For each customer I want to show every part, and the relevant discounts. For example:

Customer/Part/TierDiscount/RegionDiscount/CustomerDiscount

Cust1/Part 1/$1/$2/$3
Cust1/Part 2/$2/$1/$2
Cust2/Part 1/$2/$1/$2
Cust2/Part 2/$1/$2/$4

Some discounts do not apply to some Customers, say they are in a region with no discounts for Part 1, then the region discount for Part 1 should come up as $0. Some parts have no discounts at all, but they should still show, along with the selling price.

Since this information is coming from basically 5 different tables, I'm having issues combining everything and getting it to show correctly.

 

[jzwp22]

First, create a query that joins the customer, customer discount and parts tables. Something like this

query name: qryCustomerDiscount
SELECT tblCustomer.CustomerCode, tblCustomer.CustomerName, tblCustomer.Tier, tblCustomer.Region, tblCustomerDiscount.PartCode, tblCustomerDiscount.Discount, tblPart.PartDesc, tblPart.PartCost, tblPart.SellPrice
FROM tblPart INNER JOIN (tblCustomer INNER JOIN tblCustomerDiscount ON tblCustomer.CustomerCode = tblCustomerDiscount.CustomerCode) ON tblPart.PartCode = tblCustomerDiscount.PartCode
ORDER BY tblCustomer.CustomerCode, tblCustomerDiscount.PartCode;


Using the above query you need to left join it to the tier discount table via both the part number and tier that way all part/tier combinations for the customer will have a corresponding tier discount even if the discount is null. You'll need an IIF(IsNull()) nested function to test whether there is a discount and if not, return a zero, otherwise return the discount amount. The query would look like this:

query name: qryCustomerAndTierDisc
SELECT qryCustomerDiscount.CustomerCode, tblTierDiscount.TierCode, tblTierDiscount.PartCode, IIf(IsNull(tblTierDiscount.Discount),0,tblTierDiscount.Discount) AS TierDisc
FROM qryCustomerDiscount LEFT JOIN tblTierDiscount ON (qryCustomerDiscount.Tier = tblTierDiscount.TierCode) AND (qryCustomerDiscount.PartCode = tblTierDiscount.PartCode);

You will have to do the same for the region as well.

query name: qryCustomerAndRegionDisc
SELECT qryCustomerDiscount.CustomerCode, qryCustomerDiscount.PartCode, qryCustomerDiscount.Region, IIf(IsNull(tblRegionDiscount.Discount),0,tblRegionDiscount.Discount) AS RegionDisc
FROM qryCustomerDiscount LEFT JOIN tblRegionDiscount ON (qryCustomerDiscount.PartCode = tblRegionDiscount.PartCode) AND (qryCustomerDiscount.Region = tblRegionDiscount.RegionCode)
ORDER BY qryCustomerDiscount.PartCode;

Now you need a query that brings everything together in one place:

query name: qryFinal

SELECT qryCustomerDiscount.CustomerCode, qryCustomerDiscount.CustomerName, qryCustomerDiscount.PartCode, qryCustomerDiscount.PartDesc, qryCustomerDiscount.PartCost, qryCustomerDiscount.SellPrice, qryCustomerDiscount.Discount AS CustDisc, qryCustomerAndTierDisc.TierDisc, qryCustomerAndRegionDisc.RegionDisc
FROM (qryCustomerDiscount INNER JOIN qryCustomerAndRegionDisc ON (qryCustomerDiscount.PartCode = qryCustomerAndRegionDisc.PartCode) AND (qryCustomerDiscount.Region = qryCustomerAndRegionDisc.Region) AND (qryCustomerDiscount.CustomerCode = qryCustomerAndRegionDisc.CustomerCode)) INNER JOIN qryCustomerAndTierDisc ON (qryCustomerDiscount.CustomerCode = qryCustomerAndTierDisc.CustomerCode) AND (qryCustomerDiscount.Tier = qryCustomerAndTierDisc.TierCode) AND (qryCustomerDiscount.PartCode = qryCustomerAndTierDisc.PartCode)
ORDER BY qryCustomerDiscount.CustomerName, qryCustomerDiscount.PartCode;


The queries above have been added to the attached DB.

 

[odin1701]

Ahh! Thank you very much for your help. I see where I was screwing it up.

 

[jzwp22]

You're welcome!

 

[odin1701]

Okay so I thought this was right, but it's not.

This works fine if a customer has all three discounts, but not if they just have one or two, or none.

For instance, I added a customer discount to a part, but it doesn't show up in the final query. It doesn't show up if I give a Tier or Region discount either. It will only show up if that customer has a Customer, Tier, AND a Region discount, but I need it to show any discounts, and if there are none, then they should all just be $0 - that can be done later by selecting all parts from the part table and only the matching rows from the query though, then setting the discounts to $0 if they aren't there.

Anyway, still working on this, looks like I need to add the part table to some of the intermediary queries.

 

[jzwp22]

Sorry about that, the final query should have all left joins. See attached.

 

[odin1701]

Yeah I had fixed that part, but I still needed all of the other parts listed which were not necessarily associated with a customer. I got it working though.

 

[jzwp22]

Glad to hear that you got it worked out.