473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Access assistance, if you can. Query related.

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
15 1978
Rabbit
12,516 Expert Mod 8TB
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
Widge
56
Crikey, not used subqueries before. I'll see if I can find any guides.
Feb 6 '08 #3
Widge
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
Widge
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
12,516 Expert Mod 8TB
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
Widge
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
12,516 Expert Mod 8TB
Of course you have the same percent all the way down, every transaction has the same date.
Feb 8 '08 #8
Widge
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
Widge
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
12,516 Expert Mod 8TB
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
Widge
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
12,516 Expert Mod 8TB
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
1,384 Expert 1GB
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
Widge
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
Widge
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

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

Similar topics

3
by: deko | last post by:
From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite... SELECT qryTxToQ1.Q1Total, qryTxToQ2.Q2Total,...
2
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
2
by: SenseForAll | last post by:
First please note I am a novice at VBA and not even that experienced with DAO/ADO and MS-SQL. Any assistance is appreciated. That said... I have an application written in Access w/ VBA. I need to...
0
by: Rolan | last post by:
I know what I want to do regarding the average cost for inventory, but need some assistance to sort out some of the details to finalize an inventory table and query. Essentially, the information is...
5
by: Bob Alston | last post by:
I am looking for any Microsoft Access based software that could be used for a United Way agency that provides basic needs assistance - food, clothing, financial (rent, utilities, Rx, gasoline,...
2
by: Jan Szymczuk | last post by:
Thanks again guys, for your recent assistance. Now as you were so kind to assist the first time (the solution was so simple and elegant) I was wondering if you could assist with another couple of...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
3
by: gaurav92K | last post by:
sir i am working in a company . there are many pc. i want to use remote assistance. i configure all group policy which are related remote assistance.and i enable service through remote in system...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.