Nearest Earlier Table2.Date Based Table1.Date

[Micron]

I have searched many forums and have spent about 12 hours on this to no avail. First, let me say that I have inherited this database and may not have the time to start over with it. It is an mdb created with version 2007.

For each date in ODBC tbl1I need to get information from ODBC tbl2 where the tbl2 date is the closest OLDER date when compared to a date supplied by each record in tbl1. I have tried using a subquery (sql nested in sql), TOP 1, Max, Min, Last, First, in every configuration I could think of, but no luck. In application software, I can see the information within tbl2 that I want, but can't match it. I can get one record, but it is never the right one. Tbl1 is actually a query originally. I used it to make a table so that execution was faster, so I am not adverse to populating a table to get what I need if that's the only way to do it. However, this would require that I re-write code that pushes the recordset to Excel Here's the setup:

tbl1.STOCK_CD (query) is the only join I can make with tbl2.STOCK_CD. These fields contain the same data.
tbl1.MinOfFirstXACTION_DATE is a calculated field based on another query results. This date comes from a work order table, so no correlation.
tbl2.XACTION_DATE is inventory transaction and will likely never match the other date.
So, for (MinOfFirstXACTION_DATE) versus (tbl2.XACTION_DATE):
(07/21/2010) I need the record for (05/28/2010) - nearest that is earlier
(05/17/2011) -> (03/30/2011)
(11/23/2012) -> (11/21/2011) etc.

Nothing I have tried works. Hope someone can steer me. Thanks in advance.

[orange]

Need some sample data to try out a few things.
Can you create a demo database with some data for your tbl1 and tbl2?
Zip the database and post it here and I'll give it a try.

To post go to the Go Advanced button below the box you type in then manage attachments.

Where in Ontario?

[Micron]

Thanks, be glad to when I get back to office 2morrow morning. I have no access from home.
Hamilton.

[CJ_London]

A subquery should work - it may be you have not got it quite right. Aliasing the subquery is often forgotten

You may need to correct names but this should work from what you have provided - just copy and paste into the query builder sql window

SELECT *
FROM tbl1 INNER JOIN tbl2 ON tbl1.STOCK_CD= tbl2.STOCK_CD
WHERE tbl2.XACTION_DATE(SELECT MIN(actionDate) FROM tbl2 AS T WHERE T.STOCK_CD=tbl1.STOCK_CD AND T.XACTION_DATE<tbl1.MinOfFirstXACTION_DATE

Note this will provide the closest older date, if you want a date which also matches, change the < (in red) to <=

suggest you correct for names and try it. If it doesn't work, post back here your amended SQL so we have consistency on names with the reason it is not right (wrong result, syntax error, etc) and we can move forward from there

[orange]

I set up a small table and found this seems to work (if I understand the requirement)

SELECT tbl1.stock_cd
    ,tbl1.xactionDate
    ,tbl2.xactiondate AS nearestButLowerDate
FROM tbl1
INNER JOIN tbl2 ON tbl1.STOCK_CD = tbl2.STOCK_CD
WHERE tbl2.XACTIONDATE <= (
        SELECT TOP 1 xactionDate
        FROM tbl2 AS Q
        WHERE Q.STOCK_CD = tbl1.STOCK_CD
            AND Q.XACTIONDATE <= tbl1.XACTIONDATE
        )
ORDER BY tbl1.stock_cd

[Micron]

NextOlderDate.mdb
To summarize, for each tblTest.MinOfFirstOfXACTION_DATE I need to insert into (tblTest.NearestInvXactionDate, tblTest.QtyOnHand) values (nearest older XACTION_DATE, IN_STOCK_QTY) from tbl2 where tblTest.STOCK_CD = tbl2.STOCK_CD
This is the sql for each version offered (thanks very much BTW):
orange version:
SELECT tblTest.stock_cd, [tblTest].[MinOfFirstOfXACTION_DATE], tbl2.xaction_date AS nearestButLowerDate FROM tblTest INNER JOIN tbl2 ON tblTest.STOCK_CD = tbl2.STOCK_CD
WHERE ((([tbl2].[XACTION_DATE])<=(SELECT Top 1 xaction_Date FROM tbl2 AS Q WHERE
Q.STOCK_CD = tblTest.STOCK_CD AND Q.XACTION_DATE <= [tblTest].[MinOfFirstOfXACTION_DATE])))
ORDER BY tbTest1.stock_cd;
Result - prompts me for STOCK_CD, seems to returns the correct # of recs, but only 1 date (same) for each row.

Ajax version:
SELECT tblTest.stock_cd, [tblTest].[MinOfFirstOfXACTION_DATE]
FROM tblTest INNER JOIN tbl2 ON tblTest.STOCK_CD=tbl2.STOCK_CD
WHERE tbl2.XACTION_DATE (SELECT MIN(XACTION_DATE) FROM tbl2 AS T WHERE T.STOCK_CD=tblTest.STOCK_CD AND T.XACTION_DATE<tblTest.MinOfFirstOfXACTION_DATE);
Result: returns 66 rows (looks like a Cartesian product of 6 rows x 11 rows) but does not prompt me for STOCK_CD. Prompt will be needed if I have to resort to a code loop (see below).

I tried running both versions using the ODBC table instead of local tbl2, and after 10 minutes I killed them, seeing no sign of nearing completion. SO, if I can get the correct single record using a local table, the orange version may work if I call it in code and pass a different date for each execution and insert the fields for one record into a local table, then move to the next local record, all in a loop. YIKES! Unless anyone comes up with something that works. However, if anyone comes up with a sql string that works, I will gladly use it as long as the execution does not take too long. Otherwise, users will have to open the production program and look up the nearest value manually.
Thanks.

[rpeare]

If you are looking to find the most recent date on TBLTEST given the XACTION_DATE in tbl2 use this:

SELECT tbl2.XACTION_DATE, tbl2.STOCK_CD, tbl2.IN_STOCK_QTY, Max(IIf([MinOfFirstOfXACTION_DATE]<[xaction_date],[minoffirstofxaction_date],Null)) AS MostRecentTblTest
FROM tbl2 LEFT JOIN tblTest ON tbl2.STOCK_CD = tblTest.STOCK_CD
GROUP BY tbl2.XACTION_DATE, tbl2.STOCK_CD, tbl2.IN_STOCK_QTY;

[CJ_London]

I can see I missed an = sign, I also got confused with 'nearest' so revised here

SELECT tblTest.stock_cd, [tblTest].[MinOfFirstOfXACTION_DATE]
 FROM tblTest INNER JOIN tbl2 ON tblTest.STOCK_CD=tbl2.STOCK_CD
 WHERE tbl2.XACTION_DATE = (SELECT MAX(XACTION_DATE) FROM tbl2 AS T WHERE T.STOCK_CD=tblTest.STOCK_CD AND T.XACTION_DATE<tblTest.MinOfFirstOfXACTION_DATE);

[Micron]

Ajax, I have to give you credit for solving this 99.9%! The only thing missing is the date that I was looking for:
SELECT tblTest.stock_cd, tblTest.MinOfFirstOfXACTION_DATE, tbl2.XACTION_DATE
FROM tblTest INNER JOIN tbl2 ON tblTest.STOCK_CD = tbl2.STOCK_CD
WHERE (((tbl2.XACTION_DATE)=(SELECT MAX(XACTION_DATE) FROM tbl2 AS T WHERE T.STOCK_CD=tblTest.STOCK_CD AND T.XACTION_DATE<tblTest.MinOfFirstOfXACTION_DATE))) ;

This is lightning fast on local tables. Bad news is, that if I substitute the ODBC table for tbl2, I could probably go on vacation while it runs. I did other stuff for 45 minutes and came back to it with no signs of progress (there are over 7 million records in that table). I copied the ODBC table name from the property sheet and used find/replace to over write "tbl2", so I am certain the sql was correct. I will tell the boss that to make this work, I will have to dump the initial query into a local table, then dump the transactions for the stock code into another table and work your magic on it from there.
Thanks to everyone for the help!!

[CJ_London]

This is lightning fast on local tables

it may be to show an initial display but with 7 million records will take some time to process all of them. Part of the problem is I suspect indexing - tbltest looks like it is sourced from a query or is a query. I would check the relevant fields in both tables are all indexed - (stock_cd, XAction_Date and minOfFirstXAction). I deal a lot with big data and a query similar to this one which took several hours to run was reduced to a couple of minutes when properly indexed

You could also look at running it as a passthrough query, or create as a view in SQL Server - although again, to bring that number of records across will still take some time.

As a test - open your table then go to the last record - how long does that take? The first records will display quickly, but it will take some time to get to the end.

It may also may be more efficient to combine both queries

Also, suggest look at end use - I suspect it is highly unlikely that you want a 7 million line report - but if you were to select one stock_cd or a number based on stocktype or similar, you will find it runs significantly faster (again, with proper indexing)

[Micron]

I'll consider that, thanks. tblTest is the output of a make table query and only has 6 rows for the test, so it is likely the ODBC table is the problem. I checked, and none of the fields I need are indexed and I can't do anything about that. I know there are that many records because I did what you suggested and it takes a minute or two to get to the end. Too long. For the test, I only need 6 dates that are less but nearest to the tblTest records. As for pass-through or creating a view, I need to push the results to Excel, so I think I will dump the data into local tables. Those queries only take seconds.