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.