Can't figure this query out.

[odin1701]

Been working too much, can't figure this out. I have a part table that has info on parts. I need to combine this with 3 other tables that have various discounts.

Each part can have a discount for a Region, Tier or Customer, or all three. There are multiple Regions, Tiers, and Customers. I'm wanting to generate a price sheet that takes a product's cost and subtracts relevant discounts (if there are any) based on the customer and calculates margins.

There is an effective date because I need some historical data.

Here's the relevant info:

Part Table:
PartID
Cost

RegionDiscount Table:
RegionID
PartID
Discount
Effective Date

TierDiscount Table:
TierID
PartID
Discount
Effective Date

CustomerDiscount Table:
CustomerID
PartID
Discount
Effective Date

What I am trying to do is combine these into a query which has the following:

PartID
Cost
Region Discount - If there is an applicable effective date
Tier Discount - If there is an applicable effective date
Customer Discount - If there is an applicable effective date
Final Price = Cost - (Sum of Discounts)

Note that it is possible for a part to have no discounts, so those must show up as well. Any ideas?

 

[plog]

You use the term 'applicable effective date'--what does that mean exactly? What defines applicable? I assume its going to be compared to another date--is that the date the query is run or is there another date field in your data somewhere that makes it applicable? Are you looking for the most recent Effective Date in each table?

With that said, in general what you are going to need is to start a query with your Part Table and then LEFT JOIN all the other tables to it. Then for each discount field you want you determine if there is applicable data in each LEFT JOIN table and either show it or a 0 for its respective discount field.

 

[odin1701]

You use the term 'applicable effective date'--what does that mean exactly? What defines applicable? I assume its going to be compared to another date--is that the date the query is run or is there another date field in your data somewhere that makes it applicable? Are you looking for the most recent Effective Date in each table?

With that said, in general what you are going to need is to start a query with your Part Table and then LEFT JOIN all the other tables to it. Then for each discount field you want you determine if there is applicable data in each LEFT JOIN table and either show it or a 0 for its respective discount field.

Yeah so say you are generating a price list for September. If there is a September date in the discounts, that makes that discount effective - takes the most recent discount entered. If no September date, then no discount for the month, just 0.

I think I got it - I didn't replace the missing values with 0, just left them blank and did an inline if to make the blank values 0 if they were null so I could get the cost after discounts.