By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Access assistance, if you can. Query related.

P: 56
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3.  
  4. FROM Rebates_Supplier INNER JOIN [Rebates_CBS Transaction Data] ON Rebates_Supplier.Name = [Rebates_CBS Transaction Data].Name
  5.  
  6. 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
  7.  
  8. 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"));
  9.  
  10.  
Feb 4 '08 #1
Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You're going to need to use a subquery to return only one record from the rebates table per record in your invoice table.
Feb 5 '08 #2

P: 56
Crikey, not used subqueries before. I'll see if I can find any guides.
Feb 6 '08 #3

P: 56
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?
Feb 6 '08 #4

P: 56
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.
Feb 6 '08 #5

Rabbit
Expert Mod 10K+
P: 12,366
Well, a quick and dirty example of this would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT (SELECT TOP 1 RebatePct FROM tblRebate WHERE x.PurchaseDate > RebateDate ORDER BY RebateDate DESC) AS RebatePercent, PurchaseDate, Vendor
  2. FROM tblPurchases AS x;
  3.  
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.
Feb 6 '08 #6

P: 56
Well, a quick and dirty example of this would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT (SELECT TOP 1 RebatePct FROM tblRebate WHERE x.PurchaseDate > RebateDate ORDER BY RebateDate DESC) AS RebatePercent, PurchaseDate, Vendor
  2. FROM tblPurchases AS x;
  3.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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]
  3. FROM Rebates_Supplier INNER JOIN [Rebates_CBS Transaction Data] ON Rebates_Supplier.Name = [Rebates_CBS Transaction Data].Name
  4. GROUP BY Rebates_Supplier.Name, Rebates_Supplier.TotalRebate, [Rebates_CBS Transaction Data].[Rebate Date]
  5. HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])="2007-08"));
  6.  
  7.  
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!
Feb 8 '08 #7

Rabbit
Expert Mod 10K+
P: 12,366
Of course you have the same percent all the way down, every transaction has the same date.
Feb 8 '08 #8

P: 56
Right, this should help visualise it:

http://www.sendspace.com/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.
Feb 11 '08 #9

P: 56
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.com/file/nxjko3
Feb 11 '08 #10

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 13 '08 #11

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Rebates_Supplier AS A INNER JOIN [Rebates_CBS Transaction Data] ON A.Name=[Rebates_CBS Transaction Data].Name
  3. WHERE (((A.DateAppliedFrom)=(SELECT MAX(DateAppliedFrom)   FROM Rebates_Supplier AS B   WHERE A.Name = B.Name AND B.DateAppliedFrom <= #8-1-2007#)))
  4. GROUP BY A.Name, [Rebates_CBS Transaction Data].[Rebate Date], A.CBSRebateAmt, A.PfHRebateAmt, A.DateAppliedFrom
  5. HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])="2007-08"));
Feb 14 '08 #12

Rabbit
Expert Mod 10K+
P: 12,366
That's good, but it's very specific. It only works for August 2007. If that's all you need then that's fine.
Feb 14 '08 #13

Scott Price
Expert 100+
P: 1,384
I've been given this as a solution! It works!

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Rebates_Supplier AS A INNER JOIN [Rebates_CBS Transaction Data] ON A.Name=[Rebates_CBS Transaction Data].Name
  3. WHERE (((A.DateAppliedFrom)=(SELECT MAX(DateAppliedFrom)   FROM Rebates_Supplier AS B   WHERE A.Name = B.Name AND B.DateAppliedFrom <= #8-1-2007#)))
  4. GROUP BY A.Name, [Rebates_CBS Transaction Data].[Rebate Date], A.CBSRebateAmt, A.PfHRebateAmt, A.DateAppliedFrom
  5. 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
Feb 14 '08 #14

P: 56
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!
________________
Mar 7 '08 #15

P: 56
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.
Mar 7 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.