Hi, i'm not 100% sure whether the problem i'm having is structure related or whether it's because my SQL/queries isn't the best, basically i have a table which contains a PK which is an autonumber so records can be uniquely identified, a batchnumber field, a invoicenumber field, a batchdate field, and the rest of the fields contain details about the invoice ie invoice amount, etc
Below is an example of the relevant fields in the table:
PK batchnumber invoicenumber batchdate
0 2112 10123 10/04/2007
1 2112 10152 10/04/2007
2 2521 10123 14/04/2007
3 2130 10155 12/04/2007
4 2525 10123 11/04/2007
Basically what i need to be able to do is build a list of the most current record of all invoices as of a specified date, for example if the date specified was 11/04/2007 then the output would be
PK batchnumber invoicenumber batchdate
1 2112 10152 10/04/2007
3 2130 10155 12/04/2007
4 2525 10123 11/04/2007
Any suggestions would be greatly appreciated. Thanks