Lookup at the previous record in the table

[bimmer5]

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.

 

[CJ_London]

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

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
[COLOR=red]WHERE (((EXHIST4.date) Is Null Or (EXHIST4.date)=(SELECT Max(Date) from Exhist4 as tmp where [date]<=salejou4.[date] and curtype=salejou4.curtype)))
[/COLOR]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

 

[bimmer5]

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:

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?

 

[jdraw]

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/exchange/noon-rates-5-day/
and the ability to get a rate for last 10 years.
http://www.bankofcanada.ca/rates/exchange/10-year-lookup/

Good luck.

 

[bimmer5]

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.

 

[CJ_London]

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

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

 

[bimmer5]

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.

Jan  2, 2014	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?

 

[CJ_London]

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'

 

[bimmer5]

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:

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

 

[CJ_London]

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

 

[bimmer5]

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.

 

[CJ_London]

OK try this SQL - just copy and paste as before

SELECT T.invnum, T.date2, Saledet4.matcode, Saledet4.qty, Saledet4.qprice, T.newrate, [qty]*[qprice]*[newrate] AS SDA
FROM ([COLOR=red]SELECT SALEJOU4.invnum, SALEJOU4.date2, EXHIST4.newrate[/COLOR]
[COLOR=red]FROM SALEJOU4 INNER JOIN EXHIST4 ON SALEJOU4.curtype = EXHIST4.curtype[/COLOR]
[COLOR=red]WHERE (((EXHIST4.date2)=(Select Max(date2) FROM [EXHIST4] as tmp where date2<=salejou4.date2)))[/COLOR])  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.

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

 

[bimmer5]

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...

 

[CJ_London]

OK, try modifying the code to

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 [COLOR=red]AND curtype=salejou4.curtype[/COLOR]))))  AS T INNER JOIN Saledet4 ON T.invnum = Saledet4.invnum

 

[bimmer5]

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

 

[CJ_London]

and you have indexed both date fields?

I have also just noticed both your date fields are text type and not date type in the sample your sent

Try changing them to the correct type in table design. Date type is a special form of double and numbers process a lot quicker than text.

You are trying to process upwards of 1/2 million records - what time period is this for? It is way to many to display meaningfully in a form or report so you may want to consider what you are using the info for - for example if it is to populate a summary table and you use an append query, it will append much more quickly than it takes to populate a select recordset to display - so try converting your query to an append (or make table) query

 

[bimmer5]

Hello CJ,
You are right, the 500K records is waaay too many to display on a report and of course I never do that. I attach the product table, group them by product categories and summarize the records by sales amounts, weight, cost, etc...
The way the tables are linked now by Invoice Number, I cannot group them as I used to when I linked them by the transaction number; MS Access returns the aggregate error if I uncheck the 'invnum' field.

CJ, as much as I appreciate your help, I don't want you to go crazy about it. I am going on a trip tomorrow but if you wish we can continue later in the week or next weekend. Also, I can give you a remote access, so you can see it for yourself.

Of course, it is totally up to you, your call. I already truly appreciate all the effort you put in it so far.

Cheers,
Peter

 

[CJ_London]

Enjoy your trip Let me know if there is a performance improvement by correcting the date type

I think this is the key

I

attach the product table, group them by product categories and summarize the records by sales amounts, weight, cost, etc...

the fact you are trying to view a query with everything in it is irrelevant because you are never going to actually do it 'for real'.

There are a number of strategies but what may be a better approach is to append your EXHIST4 table with the missing dates. Can review when you return

 

[bimmer5]

Thank you, CJ.
FYI:
Changing the date data type didn't help, sorry.
The data set represents 2 years of sales detail invoice records, needed for a year to year comparison, since January 2012.
Once grouped into the product categories , the final report is 40 lines, one page only. I need every detail invoice line, there is no other way to get the final result.
Sometime I analyze records in pivot tables in last 4 years - no performance issue.
I think the issue has to do with the Euro records in the exhist4 table; they are conflicting with the US records because they have the same date entries. Unfortunately, I haven't loaded them in the sample file. You can add a few records manually with the same dates as the US records and try for yourself, if you wish.
Or maybe there is some sort of a loop related to the first record in the same table, trying to add an infinite number of records?
Thanks.