Find the recent available data based on the dates

[CJ_London]

Have you tried the other suggestions?

In addition, if you are now making a table you could try using a bit of vba to insert each company in turn. I think you will find the sum of the individual companies will be less than doing the whole thing in one go.

Looking at it another way - Just looking at your data, how often do you have missing dates in tblPrices? It may be more efficient to populate the missing dates so you can join the two tables on company and date (perhaps with an additional field to indicate this is a 'calculated' record)

What sort of backtesting would you plan to do on such a recordset once it is created, thinking about that might point the way to reduce the recordset required.

 

[CJ_London]

This query will insert the missing dates

INSERT INTO tblPrices ( PriceDate, Company, Price )
SELECT tblDates.PriceDate, tblDates.Company, tblPrices_1.Price
FROM (tblDates LEFT JOIN tblPrices ON (tblDates.PriceDate = tblPrices.PriceDate) AND (tblDates.Company = tblPrices.Company)) INNER JOIN tblPrices AS tblPrices_1 ON tblDates.Company = tblPrices_1.Company
WHERE (((tblPrices.Company) Is Null) AND ((tblPrices_1.PriceDate)=(SELECT Max(PriceDate) FROM tblPrices as Tmp WHERE PriceDate<=tblDates.PriceDate AND Company=tblDates.Company)));

Don't know how long it will take to run

 

[Arvidsson]

If I combine the tblDates with the tblPrice (RIGHT JOIN), and set as criteria Price Is Null, I get 20,602 datapoints.

Out of 222,461, there are 20,602 prices missing.

A VBA solution would be nice, too.

Maybe a vba code which works with the combined (Dates & available prices) table. But unfortunately I do not know how such a code must be set up.

 

[CJ_London]

with 20000 records the insert query should not take long to run

 

[Arvidsson]

Thank you very much CJ_London!

If I test the code, a info box appears: "Enter parameter: tblPrices_1.PriceDate".

Do you have an idea why?

EDIT: Now it works. Maybe a typing error by me.
It works on my test tables. I will test it now on the big.

Thank you!

 

[CJ_London]

My version has tblPrices and I forgot to knock off the s when posting the code it should be tblPrice_1.PriceDate

 

[Arvidsson]

It is depressing

I tested your code on the big database (company IDs instead of company name). It is still running since 8:00 and the progress bar is completely blank.

If I match the tblPrice and the tblDates I get this table:

PosNr  PriceDate          Company  Price
1       01.01.2014       Firma A       5                                      
2       02.01.2014       Firma A       7                                   
3       03.01.2014       Firma A       9            
4       04.01.2014       Firma A       8 
5       05.01.2014       Firma A       
6       06.01.2014       Firma A       6            
7       02.01.2014       Firma XY     11                                      
8       03.01.2014       Firma XY      9                                  
9       04.01.2014       Firma XY      7           
10      05.01.2014       Firma XY      8          
11      06.01.2014       Firma XY      10

Maybe a VBA code can check whether the price field is empty, an than it fills the last price into. Perhaps, would this be faster?

 

[CJ_London]

I wonder if there is something else, I am surprised it is taking so long.

Just as a visual check, this is what your query should look like to insert the missing dates

VBA code still runs the same query, the benefit is you can break it down into smaller amounts and smaller datasets generally run quicker. But if 20000 records are taking this long then I'm not sure there would be any real benefit.

 

[vbaInet]

Not had any response from the poster yet!

By the way, if all you want to do is update those records that have no prices shouldn't you be filtering it down further? Get the update done then get all the records.

 

[CJ_London]

Whilst looking for something else I found this link about BeginTrans and CommitTrans

http://www.blueclaw-db.com/access_consultant_rapid.htm

Have a look at point 10 - it claims

This method can speed up Access database queries many fold by allowing the transactions to be cached rather than written to disk after each transaction