W
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:
Code: ( text )
1.
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
2.
3.
FROM Rebates_Supplier INNER JOIN [Rebates_CBS Transaction Data]
ON Rebates_Supplier.Name = [Rebates_CBS Transaction Data].Name
4.
5.
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
6.
7.
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"));
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:
Code: ( text )
1.
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
2.
3.
FROM Rebates_Supplier INNER JOIN [Rebates_CBS Transaction Data]
ON Rebates_Supplier.Name = [Rebates_CBS Transaction Data].Name
4.
5.
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
6.
7.
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"));