Access assistance, if you can. Query related.

[Widge]

Hi,

I wondered if you could help me with an issue I'm having. Currently I have a rebate calculation that is running off two tables:

1) A list of suppliers and the rebate %ages relevant to them
2) A ongoing list of invoice details, including a month/year period and an invoice total

At the moment, I can happily run the rebate calculation for the current month. What I would like to do is begin to track changes in supplier rebate rates over time. This means applying a month/year period for the date which the rebate is applicable against each line entered on the rebate table.

The theory is that when I run my query, it goes in, finds the correct rebate rate to apply from a specifed period and then calculates based on those rates. This means I can track changes, but also run the calc historically if necessary (which is the end purpose of my calc).

The problem I'm having is, no matter what I enter in my query, it seems determined to give results for each and every line that exists in the supplier rebate table, regardless of the date that is against that line.

For example, I can have data that is like:

Supplier A - 2% - 2007-03
Supplier A - 3% - 2007-08
Supplier A - 5% - 2008-01

on the rebate table. If I run a calc with a run date of 2007-10, I want it to go and pull off all the October 2007 rebates, and then use the rebate rate of 3% against it. At the moment it gives me results for 2%, 3% and 5%, a record for each.

If this doesn't make sense, I could expand further!

My existing query is this if you find it useful:

 
SELECT Rebates_Supplier.ClientNumber, Rebates_Supplier.Name AS Supp, Rebates_Supplier.Group, Sum([Rebates_CBS Transaction Data]![Base Amount]*-1) AS [Gross Amount], Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*Rebates_Supplier!CBSRebateAmt/100) AS CBS, Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*Rebates_Supplier!PfHRebateAmt/100) AS PfH, Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*Rebates_Supplier!TotalRebate/100) AS Total, [Rebates_CBS Transaction Data].[Rebate Date], Rebates_Supplier.Category, Rebates_Supplier.Status, Rebates_Supplier.CBSRebateAmt, Rebates_Supplier.PfHRebateAmt, Rebates_Supplier.Frequency
 
FROM Rebates_Supplier INNER JOIN [Rebates_CBS Transaction Data] ON Rebates_Supplier.Name = [Rebates_CBS Transaction Data].Name
 
GROUP BY Rebates_Supplier.ClientNumber, Rebates_Supplier.Name, Rebates_Supplier.Group, [Rebates_CBS Transaction Data].[Rebate Date], Rebates_Supplier.Category, Rebates_Supplier.Status, Rebates_Supplier.CBSRebateAmt, Rebates_Supplier.PfHRebateAmt, Rebates_Supplier.Frequency, Rebates_Supplier.Category
 
HAVING (((Rebates_Supplier.ClientNumber)>0) AND (([Rebates_CBS Transaction Data].[Rebate Date])="2007-08") AND ((Rebates_Supplier.Category)="C") AND ((Rebates_Supplier.Status)="Active") AND ((Rebates_Supplier.Frequency)="Monthly"));
 
 

[Rabbit]

There's a few problems.

1. Your rebate date field isn't of date data type.
2. You didn't use a subquery to select the correct record.
3. In the SQL statement a few posts prior, you never accounted for the supplier in the subquery.

[Widge]

I've been given this as a solution! It works!

SELECT A.Name AS Supp, Sum([Rebates_CBS Transaction Data]![Base Amount]*-1) AS [Gross Invoice Amount], Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*A!CBSRebateAmt/100) AS CBSRebate, Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*A!PfHRebateAmt/100) AS PfHRebate, Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*A!TotalRebate/100) AS Total, [Rebates_CBS Transaction Data].[Rebate Date], A.CBSRebateAmt, A.PfHRebateAmt, A.DateAppliedFrom
FROM Rebates_Supplier AS A INNER JOIN [Rebates_CBS Transaction Data] ON A.Name=[Rebates_CBS Transaction Data].Name
WHERE (((A.DateAppliedFrom)=(SELECT MAX(DateAppliedFrom)   FROM Rebates_Supplier AS B   WHERE A.Name = B.Name AND B.DateAppliedFrom <= #8-1-2007#)))
GROUP BY A.Name, [Rebates_CBS Transaction Data].[Rebate Date], A.CBSRebateAmt, A.PfHRebateAmt, A.DateAppliedFrom
HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])="2007-08"));

[Rabbit]

That's good, but it's very specific. It only works for August 2007. If that's all you need then that's fine.

[Scott Price]

I've been given this as a solution! It works!

SELECT A.Name AS Supp, Sum([Rebates_CBS Transaction Data]![Base Amount]*-1) AS [Gross Invoice Amount], Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*A!CBSRebateAmt/100) AS CBSRebate, Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*A!PfHRebateAmt/100) AS PfHRebate, Sum(([Rebates_CBS Transaction Data]![Base Amount]*-1)*A!TotalRebate/100) AS Total, [Rebates_CBS Transaction Data].[Rebate Date], A.CBSRebateAmt, A.PfHRebateAmt, A.DateAppliedFrom
FROM Rebates_Supplier AS A INNER JOIN [Rebates_CBS Transaction Data] ON A.Name=[Rebates_CBS Transaction Data].Name
WHERE (((A.DateAppliedFrom)=(SELECT MAX(DateAppliedFrom)   FROM Rebates_Supplier AS B   WHERE A.Name = B.Name AND B.DateAppliedFrom <= #8-1-2007#)))
GROUP BY A.Name, [Rebates_CBS Transaction Data].[Rebate Date], A.CBSRebateAmt, A.PfHRebateAmt, A.DateAppliedFrom
HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])="2007-08"));

Hello Widge,

As a full member now with the number of posts that you currently have, we expect that you begin to use the [code] tags without being prompted! They are simple to use. Simply select the code text and click on the # button on the top of the message window. You can manually edit the first tag to reflect the language of the code: [code=vb] or [code=sql] for Visual Basic or SQL respectively.

Thanks!

Regards,
Scott MODERATOR

[Widge]

Hello Widge,

As a full member now with the number of posts that you currently have, we expect that you begin to use the [code] tags without being prompted! They are simple to use. Simply select the code text and click on the # button on the top of the message window. You can manually edit the first tag to reflect the language of the code: [code=vb] or [code=sql] for Visual Basic or SQL respectively.

Thanks!

Regards,
Scott MODERATOR

Ooops, sorry Scott!
_______________ _

[Widge]

That's good, but it's very specific. It only works for August 2007. If that's all you need then that's fine.

Yeah, that was a specific example, I subbed out that for a variable in my final piece of code.