Date Query

[vaughan198]

Hi there,
Every day I import an excel report so I have lots of records for each day that have the same report date. I need to create a query that finds the most recent report/date and pulls in all the recors for that date.

I need to do this for three different tables

Then export these three queries to different tabs in an excel workbook and send it as an email.

Any help, especially on the first point would be hugely appreciated.

Thanks,
Will

 

[jzwp22]

First, you want to create a query that finds the most recent date

query name: qryMostRecentReport
SELECT max(reportdatefield) as MostRecentReportDate
FROM tablename

Now, create a second query that joins the above query back to the table

SELECT fields
FROM tablename INNER JOIN qryMostRecentReport ON tablename.reportdatefield=qryMostRecentReport.MostRecentReportDate

 

[vaughan198]

Many thanks, that worked perfectly.
Now that I have the three queries is it possible to export them to three different tabs of the same (new) excel workbook?

 

[Pat Hartman]

Yes. Access uses the "tableName" to name the sheet.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Switchboard Items", "C:\temppat\TestExcelExport.XLS", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tblExpenseType", "C:\temppat\TestExcelExport.XLS", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tblIncomeType", "C:\temppat\TestExcelExport.XLS", True