Assistane getting the closest date from two tables

[renaccess1]

I have 2 tables


Table one with accounting transactions that include a key that can be joined with another table that has status at a particular date in time. I need to select the dATE IN TABLE 2 that is closest to the transaction date in table 1.

For example

Table 1
acct key datetran
ABCD 10/31/2016
ABCD 2/8/2016


Table 2

Acct key	ORIG_VAL	NEW_VAL	TBL_LAST_DT
ABCD		A	3/4/2013 14:25
ABCD	C	AT	12/30/2013 10:23
ABCD	AT	WW	12/30/2013 10:23
ABCD	WW		2/6/2017 13:47

Results I want to see

ACCTKey datetran orig_val New_val
ABCD 10/31/2016 AT WW
ABCD 2/8/2017 WW


Basically selecting the row on table 2 where the transaction date > the TBL_LAST_DT on table 2 and the transaction date on table 1 has to be less than the date alson on table 2 if they are multiple records

Can this be done in Access or do I need to create SQL to solve this issue

Thank you

Renetta

[ArviLaanemets]

Both records in Table 1 are closest to one and same record in Table 2, to one at 2/6/2017. All other entries in Table 2 are 2 - 3 years distant.

Or do you need last previous record instead? And when yes, then what about cases when there is same date in both tables?

[renaccess1]

Hello Sorry.. forgot the date and time stamp... here is goes again. I truly appreciat you... responding.

Table 1
Acct Key	Date Tran
ABCD	3/31/2013 11:49:56 AM
ABCD	2/8/2016 5:10:00 PM
ABCD	12/30/2013 10:23:09 PM
Table 2
Acct key	ORIG_VAL	NEW_VAL	TBL_LAST_DT
ABCD		A	3/4/13 2:25:19 PM
ABCD	C	AT	12/30/13 10:23:07 AM
ABCD	AT	WW	12/30/13 10:23:25 AM
ABCD	WW		2/6/17 1:47:07 PM
Result Set
Acct Key	Date Tran	ORIG_VAL	NEW_VAL
ABCD	3/31/2013 11:49:56 AM		A
ABCD	2/8/2016 5:10:00 PM	WW
ABCD	12/30/2013 10:23:09 PM	C	AT

[aytee111]

You will need a sub-query for this. See
http://allenbrowne.com/subquery-01.html

[renaccess1]

Thanks for that information but when creating the sub query when returning the result I get multiple repeated records since there are many records in table b less than table a. Do you have a sample what the query would look like?

[aytee111]

The link I provided gave you an example of what it should look like. Make yours look like that, just change the table and field names. Then post your SQL with the exact issue you are having.