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]

You're going to need to use a subquery to return only one record from the rebates table per record in your invoice table.

[Widge]

Crikey, not used subqueries before. I'll see if I can find any guides.

[Widge]

Oh what am I talking about I've used a subquery before. Ok, I have a slight advancement on this problem. I'm using this query in VBA as part of a ADODB.Recordset . What I currently do is set a string full of my SQL from the query, including some sections where there is user input from a form, and have it run. How would I use a subquery in this instance?

Instead of referring to my tables, can I refer to a query instead?

[Widge]

Actually I'm still a bit baffled at how to structure the query to pick out the applicable date across all suppliers. I'm basically asking it to pick off all records BEFORE the entered date. Now I pick up all the supplier rebate rates previous to that date, including any historical ones if there are any. I can't figure out how to strip out these historical ones.

[Rabbit]

Well, a quick and dirty example of this would be:

SELECT (SELECT TOP 1 RebatePct FROM tblRebate WHERE x.PurchaseDate > RebateDate ORDER BY RebateDate DESC) AS RebatePercent, PurchaseDate, Vendor
FROM tblPurchases AS x;
 

Of course this only uses a date as the determining factor for the rebate amount. Your criteria may be more complex and all of that can be built into the query.

[Widge]

Well, a quick and dirty example of this would be:

SELECT (SELECT TOP 1 RebatePct FROM tblRebate WHERE x.PurchaseDate > RebateDate ORDER BY RebateDate DESC) AS RebatePercent, PurchaseDate, Vendor
FROM tblPurchases AS x;
 

Of course this only uses a date as the determining factor for the rebate amount. Your criteria may be more complex and all of that can be built into the query.

Had a go.... gave me the same percentage all the way down for each supplier.

Was this:

 
SELECT Rebates_Supplier.Name AS Supp, (SELECT TOP 1 Rebates_Supplier.TotalRebate From Rebates_Supplier WHERE [Rebates_CBS Transaction Data].[Rebate Date] > Rebates_Supplier.DateApplied ORDER  BY Rebates_Supplier.DateApplied Desc) , [Rebates_CBS Transaction Data].[Rebate Date]
FROM Rebates_Supplier INNER JOIN [Rebates_CBS Transaction Data] ON Rebates_Supplier.Name = [Rebates_CBS Transaction Data].Name
GROUP BY Rebates_Supplier.Name, Rebates_Supplier.TotalRebate, [Rebates_CBS Transaction Data].[Rebate Date]
HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])="2007-08"));
 
 

just doing a tester to see if it would work. I could knock up a test DB with my data (I'll have to edit out names and stuff) to demonstrate?

Cheers for being on hand btw!

[Rabbit]

Of course you have the same percent all the way down, every transaction has the same date.

[Widge]

Right, this should help visualise it:

http://www.sendspace.c om/file/janqm7

Thats an example. Usually the database is full of months and months of transaction data, plus the rebates table is full of suppliers.

What I've put in is an example of how I want the history of rebate rates to flow. SuppB has had a good few different rates over time.

I want to be able to run the query and have SuppB only display a result for the relevant date that its rebate rate is applicable for.

For the 2007-08 period, Supp B should be calculated on the 3% rate, my query is creating a line for every single historical rate. I can't find a way to make it pick off the correct one. I need to make sure that each time the query pulls off across all suppliers, the correct period. Such as Supp A only has one line of entry which is valid from 2006, that must be used across all Supp A's. If I ran a calc at 2008-01 then Supp B would have a different rate, but Supp A would still stay the same because its still valid from 2006 with no change.

What happens is this data will then populate a table on a form (which can be copied and pasted)... then I have a piece of code that uses the results of this to go and autopopulate rebate invoices. In its current form, it would create 3 Supp B invoices when run for 2007-08, with only one actually being correct.

[Widge]

A better and less complex version of my database for example:

For download here is my database. Inside are two table and query set,
one set which is running of my current spec where I don't use any
historical records.... runs fine. One where it is running using
historical entries for SuppB (as an example), as you can see when
running the query, 3 lines for SuppB show. I need the relevant one to
display only for Supp B.

Also I have to make sure the likes of Supp A are not affected.

Thanks for any help!

Database:

http://www.sendspace.c om/file/nxjko3