Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-25-2014, 07:58 AM   #1
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Lookup at the previous record in the table

Hi there,
I’ve got stuck in preparation of this sales query. The primary sales table contains a mix of Canadian and the US detail invoice sales records. All sales records are in their native currencies The secondary lookup table contains daily US/CAN foreign exchange rates (FX). I need to multiply every $US sales record (marked with a “U”) with the FX rate in order to convert it to the Canadian currency. Unfortunately, this FX lookup table is missing over 50 daily rates in the last two years and as a result I cannot match them date-by-date with the sales table.
As a workaround, it is acceptable to use the previous FX rate in the table. The previous rate can be one or more days before the transaction occurred.
I don’t know how to point to that previous FX record, therefore any help is appreciated.

Attached is a sample database with the query that I’ve already built.
The query contains two sample US records that are missing the FX rate on Jan 6. The FX rate of $1.0639 that needs to be applied to it should come from Jan 3rd entry.

New Date sales matcode curtype trans newrate
1/3/2014 1225.6128 1173224 U R18761 1.0639
1/3/2014 344.7036 1173260 U R18173 1.0639
1/6/2014 2520 0022691 U R19841
1/6/2014 5400 0022692 U R19841

Thank you.
Attached Files
File Type: accdb sample.accdb (484.0 KB, 42 views)

bimmer5 is offline   Reply With Quote
Old 01-25-2014, 09:19 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup at the previous record in the table

Try this query which uses a subquery to find the latest date - just copy into the sql window of your query designer

Code:
SELECT DateValue([Salejou4]![date]) AS [New Date], Sum(IIf(IsNull([EXHIST4]![newrate]),[Saledet4]![qty]*[Saledet4]![qprice],[Saledet4]![qty]*[Saledet4]![qprice]*[EXHIST4]![newrate])) AS sales, Saledet4.matcode, Salejou4.curtype, Saledet4.trans, EXHIST4.newrate
FROM (Salejou4 INNER JOIN Saledet4 ON Salejou4.trans = Saledet4.trans) LEFT JOIN EXHIST4 ON Salejou4.curtype = EXHIST4.curtype
WHERE (((EXHIST4.date) Is Null Or (EXHIST4.date)=(SELECT Max(Date) from Exhist4 as tmp where [date]<=salejou4.[date] and curtype=salejou4.curtype)))
GROUP BY DateValue([Salejou4]![date]), Saledet4.matcode, Salejou4.curtype, Saledet4.trans, EXHIST4.newrate
Note that Date is a reserved word so you should prefix or suffix it with something otherwise you will experience unexpected and difficult to identify errors in the future.

You'll see I also removed the link between the two date fields in Salejou4 and Exhist4
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
bimmer5 (01-25-2014)
Old 01-25-2014, 10:11 AM   #3
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Re: Lookup at the previous record in the table

Appreciated a quick response, CJ_London !
Tested it on the sample database and it worked fine.
However, the query gets stuck at approximately 60% runtime in the real database.
I replaced the "date" wording with the "date2" in the source linked tables, as suggested.

Here is the new code:
Code:
SELECT DateValue([Salejou4]![date2]) AS NewDate, Sum(IIf(IsNull([EXHIST4]![newrate]),[Saledet4]![qty]*[Saledet4]![qprice],[Saledet4]![qty]*[Saledet4]![qprice]*[EXHIST4]![newrate])) AS sales, Saledet4.matcode, Salejou4.curtype, Saledet4.trans, EXHIST4.newrate
FROM (Salejou4 INNER JOIN Saledet4 ON Salejou4.trans = Saledet4.trans) LEFT JOIN EXHIST4 ON Salejou4.curtype = EXHIST4.curtype
WHERE (((EXHIST4.date2) Is Null Or (EXHIST4.date2)=(SELECT Max(Date2) from Exhist4 as tmp where [date2]<=salejou4.[date2] and curtype=salejou4.curtype)))
GROUP BY DateValue([Salejou4]![date2]), Saledet4.matcode, Salejou4.curtype, Saledet4.trans, EXHIST4.newrate;
I hope I didn't miss anything?


Last edited by bimmer5; 01-25-2014 at 10:13 AM. Reason: correction
bimmer5 is offline   Reply With Quote
Old 01-25-2014, 10:38 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,128
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Lookup at the previous record in the table

I'm not sure where you're getting your exchange rates but for reference, the Bank Of Canada offers a link to get the rates for the last 5 days
http://www.bankofcanada.ca/rates/exc...n-rates-5-day/
and the ability to get a rate for last 10 years.
http://www.bankofcanada.ca/rates/exc...0-year-lookup/

Good luck.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
bimmer5 (01-25-2014)
Old 01-25-2014, 01:47 PM   #5
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Re: Lookup at the previous record in the table

The Bank of Canada is our source, exactly. The issue is with our bean counters who occasionally skip a day or two posting the rates in our internal system. This report needs to match our internal reports the way they are.
bimmer5 is offline   Reply With Quote
Old 01-25-2014, 04:30 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup at the previous record in the table

Quote:
The issue is with our bean counters who occasionally skip a day or two posting the rates in our internal system
Sounds like you need to kick ass!

You need to expand on
Quote:
However, the query gets stuck at approximately 60% runtime in the real database.
It depends how many records you are talking about and how are you defining '60% runtime'?

One thing to check which will have a significant effect on performance is indexing on any field used in the criteria so

Salejou4.trans
Saledet4.trans
Exhist4.date2
salejou4.date2
Exhist4.curtype
Salejou4.curtype
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
bimmer5 (01-26-2014)
Old 01-26-2014, 03:56 AM   #7
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Re: Lookup at the previous record in the table

Code:
It depends how many records you are talking about and how are you defining '60% runtime'?
Not too many records:
Salejou4 table: 51,000
Saledet4 table: 510,000
Exhist4 table: 1,000

Normally, it takes less than a minute to run the same query without the new subquery.
When I said the 60%, I was referring to the query progress bar. Actually, I decided to let it go and after 3 hours the progress bar reached the end but I had to leave and stopped the process. Defining the indexes didn't change anything.

I suspect that other (Euro FX) records in the 'exhist4' table affect the process, since they have the same dates as the 'U' currency.

PHP Code:
Jan  22014    1.4655    1.4535    E 
Jan  3
2014    1.4535    1.444    E 
Jan  7
2014    1.444     1.4627    E 
Jan  8
2014    1.4627    1.4678    E 
Jan  9
2014    1.4678    1.4747    E 
If so, then sorry, I should have mentioned them at the first place, now I am realizing that we may need to take them in consideration by further modifying the subquery.
What do you think?

bimmer5 is offline   Reply With Quote
Old 01-26-2014, 04:22 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup at the previous record in the table

Surprised defining the indexes didn't improve things

Other currencies should not make a difference, however the left join between Salejou4 and Exhist4 could make a difference since they can produce nulls.

Try changing this join to an inner join and remove the IS NULL Or in the criteria.

This means it won't report any Salejou4 records where there isn't a price in Exhist4, but this may not matter for your purposes - you can always make it subject to a separate 'exceptions report'
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
bimmer5 (01-26-2014)
Old 01-26-2014, 05:43 AM   #9
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Re: Lookup at the previous record in the table

No luck yet.
Now access shows 'Running Query' on the bottom but no progress bar.

FYI, here is the latest statement, as per your suggestions:
Code:
SELECT DateValue([Salejou4]![date2]) AS NewDate, EXHIST4.newrate
FROM Salejou4 INNER JOIN EXHIST4 ON Salejou4.curtype = EXHIST4.curtype
WHERE (((EXHIST4.date2)=(SELECT Max(Date2) from Exhist4 as tmp where [date2]<=salejou4.[date2] and curtype=salejou4.curtype)))
GROUP BY DateValue([Salejou4]![date2]), EXHIST4.newrate;
I removed the 'saledet4' table from the query since it shouldn't be relevant in this case and it contains tons of records.
Can we have a different approach here, e.g. just by building a new, FX rate based query that will contain the date, the rate and the currency fields only? Meaning, not to include any other field from the 'salejou4' table. If feasible, the query should populate ALL of the missing dates (including weekend days) in the list and replicate the previous rate and the currency type?
If not possible without the 'salejou4' date field, then maybe we should create another 'salejou4' based query that will show up the 'date' field only once for each day and then link it with 'exhist4' ?
Just throwing different ideas...
What do you think?

Thanks.
Peter
bimmer5 is offline   Reply With Quote
Old 01-26-2014, 06:00 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup at the previous record in the table

Happy to go back to the beginning, but your new approach is not any better.

Please explain what it is you are trying to achieve - I can see you want to look up the new rate but what do you want to do with it? ie. provide an example of what you want to see as a finished result
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
bimmer5 (01-26-2014)
Old 01-26-2014, 07:17 AM   #11
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Re: Lookup at the previous record in the table

O.K., here is the requirement:

The final result is a simple sales report that should show up any field from either the ‘salejou4’ (invoice header table) or ‘saledet4’ (invoice details) table.
The ‘exhist4’ is just a helper table that we need to use in order to get the daily CAN/US or CAN/EURO currency conversion rates for items sold in the U.S. or in Europe. I don’t have to have any field from the ‘exhist4’ table in the final report.

Also, I need to have a calculated ‘Sales Amount’ field on the final report, based on the following:
- Sales Detail Amount: [saledet4.qty] x [saledet4.qprice] x [exhist4.rate] or in words:
o (individual item sold quantity) x (individual item price) x (daily currency conversion rate)

In future (not for this report), I may need to use the ‘salejour4.amount’ field (the Total summary invoice amount) that I will also need to multiply with the daily rates from the ‘exhist4’ table. The ‘amount’ field contains a mix of the CAN, US or EURO invoice totals in their native currencies. The ‘curtype’ field is populated for the U.S. (U) and the European (E) transactions.

Challenge:
If I have the ‘exhist4’ table properly populated with the exchange rates every day, then I wouldn’t have any issues. Since there are a number of gaps (missing daily rates) in this table, I cannot match one-to-one each ‘salejou4.date’ field with the ‘exhist4.date’ field. As a workaround, I was told to use the previous rate posted into the ‘exhist4’ table. Ideally, I should have a new query based on the ‘exhist4’ table that will contain the missing entries. If I have such a query, life would be wonderful again and I could re-use it for any future sales report.

Regardless, the final basic sales report should have a least the following fields:
- salejou4.date (Invoice Date)
- saledet4.matcode (Product Number)
- saledet4.qty (Quantity Sold)
- Sales Detail Amount (the calculated field from above)

I hope the above clarifies any confusion?
Let me know if you still need further clarification.
Thank you for your help in this matter.

Last edited by bimmer5; 01-26-2014 at 07:45 AM. Reason: correction
bimmer5 is offline   Reply With Quote
Old 01-26-2014, 10:09 AM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup at the previous record in the table

OK try this SQL - just copy and paste as before

Code:
 
SELECT T.invnum, T.date2, Saledet4.matcode, Saledet4.qty, Saledet4.qprice, T.newrate, [qty]*[qprice]*[newrate] AS SDA
FROM (SELECT SALEJOU4.invnum, SALEJOU4.date2, EXHIST4.newrate
FROM SALEJOU4 INNER JOIN EXHIST4 ON SALEJOU4.curtype = EXHIST4.curtype
WHERE (((EXHIST4.date2)=(Select Max(date2) FROM [EXHIST4] as tmp where date2<=salejou4.date2))))  AS T INNER JOIN Saledet4 ON T.invnum = Saledet4.invnum
Note I have not grouped since in the sample data you do not have any duplicate matcodes for the same day - however you can add this in if required.

Quote:
I should have a new query based on the ‘exhist4’ table that will contain the missing entries
This can be done- you'll need to create a table with every date and currency in your reporting period (so 1 year, 2 currencies=365*2 records) then use the code highlighted in red above, substituting SALEJOU4 with the name of your table
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
bimmer5 (01-26-2014)
Old 01-26-2014, 01:05 PM   #13
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Re: Lookup at the previous record in the table

Alright, I've got a mixed results this time:
- Using the new code from above, the query would display in about 1 minute
- Scrolling through the query is sooo slooow...I can scroll one page per minute, no way to scroll to the bottom or to see the total number of rows, it runs constantly in background.
I tried indexing the 'invnum' field in both 'salejour4' and 'saledet4' files but it didn't make any difference at all.

Then I created a new 'FX' table, inserted the date range suggested and the 'curtype' column, copied the code in red, had to exclude the 'invnum' field, renamed 'salejour4' entries with 'FX'... the result is that Access shows 'Running Query' forever, so I had to cancel it.

Oh, well...
bimmer5 is offline   Reply With Quote
Old 01-26-2014, 02:31 PM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Lookup at the previous record in the table

OK, try modifying the code to

Code:
 
SELECT T.invnum, T.date2, Saledet4.matcode, Saledet4.qty, Saledet4.qprice, T.newrate, [qty]*[qprice]*[newrate] AS SDA
FROM (SELECT SALEJOU4.invnum, SALEJOU4.date2, EXHIST4.newrate
FROM SALEJOU4 INNER JOIN EXHIST4 ON SALEJOU4.curtype = EXHIST4.curtype
WHERE (((EXHIST4.date2)=(Select Max(date2) FROM [EXHIST4] as tmp where date2<=salejou4.date2 AND curtype=salejou4.curtype))))  AS T INNER JOIN Saledet4 ON T.invnum = Saledet4.invnum
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
bimmer5 (01-26-2014)
Old 01-26-2014, 03:21 PM   #15
bimmer5
Newly Registered User
 
Join Date: Apr 2008
Location: Toronto, Canada
Posts: 67
Thanks: 15
Thanked 0 Times in 0 Posts
bimmer5 is on a distinguished road
Re: Lookup at the previous record in the table

Nope. No luck my friend.
The same performance issue as with the previous one. I tried removing the 'invnum' indexes but no difference.
Sorry.

bimmer5 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup List of Values depending on a previous lookup list ds3010 Queries 6 11-07-2011 08:35 AM
lookup last record in table ray147 Modules & VBA 3 12-22-2005 04:34 AM
comparing a record with previous record in the same table sametch Queries 7 07-29-2003 11:42 PM
Lookup on a table based on previous field on a form Bob M General 7 09-12-2002 12:25 PM
[SOLVED] How to lookup value in the previous record? maxkmv Modules & VBA 1 03-13-2002 02:03 PM




All times are GMT -8. The time now is 07:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World