I'm working in Access 2003 and need to write a query that will select the records from one of four tables that is closest to (past only, not future) a date specified in a dialogue box.
Basically, these four tables store payment installment information for different contracts. Some contracts only have one or two installments, others may have three or four. As a result, all contracts will have a record in Table 1, but not necessarily the other three tables. There is a unique code for each contract which is included in each table for which there is an installment for that contract.
I need to write a query that, when I enter a particular date, will return the installment information from any of the four tables based on the closest date from each contract to the particular date specified.
Can anyone help?