Tried to come up with SQL query in MS Access, but null values and an aggregate function have me stumped. Any help appreciated.
Query to show records from TABLE1 where the EditDate (which may be null) is greater than the maximum LastImportDate from TABLE2.
TABLE1
Field Name - Data Type ReportID - Number EditDate - Date/Time
TABLE2
Field Name - Data Type LastImportDate - Date/Time
Thank you.
SELECT *
FROM table1
WHERE editDate > (
SELECT max(lastImportDate)
FROM table2
)
not sure how exactly that translates to an access query, but that is the idea.
Additionally, if you could break out the max date as a separate variable, that will get you a bit better performance - something like:
DECLARE @maxDate DATETIME
SET @maxDate = (
SELECT max(lastImportDate)
FROM table2
)
SELECT *
FROM table1
WHERE editDate > @maxDate
Lastly, if you wanted to dates that have a null editDate, you can have the query interpret nulls as some arbitrary date like isNull(editDate, '1900-01-01') - this will make null editDates get interpreted as 1900 Jan 1
COALESCEorISNULLwork in Access's SQL? And if theEditDateisNULL, do you want to ignore it? You could probably use something like... where ISNULL(TABLE1.EditDate, '1970-01-01') > TABLE2.LastImportDate...ISNULLin MS Access. Something like this:... where IIF(ISNULL(TABLE1.EditDate), '1970-01-01', TABLE1.EditDate) > TABLE2.LastImportDate...