Finding Correct Record w/ Specified Date from Audit Table

[Kheribus]

Hello,

Working on a project to generate invoices with a Mysql backend connected via ODBC (linked table) to an access front end.

I have an audit table that I will be using to generate automated invoices over a specified time period. The table will be something like...

name, attrib1, attrib2, attrib3, changetype, dateChanged with name/datetime being the composite key. Change type can be NEW, CHG, or DELT.

So..

tripName1, attrib1, attrib2, attrib3, NEW, 9/01/2015 1:11:11PM
tripName1, attrib1, attrib2, attrib3, CHG, 9/22/2015 4:15:58PM
tripName1, attrib1, attrib2, attrib3, CHG, 10/2/2015 2:12:28PM
tripName1, attrib1, attrib2, attrib3, CHG, 10/3/2015 3:11:11PM


The user will select a date range to invoice over, and then my program needs to find the record in the audit table that relates to each individual date in the invoice date range.

For instance: invoice date range = 10/1 - 10/15
iteration 1: invoice date = 10/1/2015
iteration 2: invoice date = 10/2/2015
iteration 3: invoice date = 10/3/2015...

For iteration 1 - I need to select a single row (preferably through query but i could use a recordset with post processing scripting) with a specific trip name that does not exceed 10/1/2015 0:0:0, and then do some calculations on that row, which will give me invoicing metrics during the invoicing window specified.

Question is: What's the best way to select the (single) record with TripName1 from the audit tablet that <= 10/1/2015, to ensure that I get only one row and it is the most recent record fulfilling the requirement <=10/1/2015?

Thanks,
K

 

[Kheribus]

So for the above table,

iteration 1 (10/1/2015) would select the row where date = 9/22.
iteration 2 (10/2/2015) would select the row where date = 10/2.
iteration 3 (10/3/2015) = 10/3
iteration 4 (10/4/2015) = 10/3
...

 

[jdraw]

Untested

SELECT * FROM YourTable
Where TripName = "TripName1" AND
InvoiceDate = (Select Max(InvoiceDate) from Yourtable
                       WHERE InvoiceDate<=  #10/1/2015#
                        AND TripName = "TripName1")

 

[Kheribus]

ah, max function. Thanks, I'll give it a shot!

 

[Kheribus]

What does the # do in this statement?

 

[jdraw]

In Access
strings are delimited with quotes
eg. "This is a string"

Dates are delimited with octothorpes/hash tags
eg. #10/21/2015# is a date

Numbers do NOT require delimiters
eg. 12345 is a number

 

[pbaldy]

FYI, moved out of SQL Server forum since it wasn't related to SQL Server.

 

[Kheribus]

I wish I was on SQL Server and had window functions