Query to extract the first and last date

[MitchB]

Hi there.. I've been struggling with this and I hope one of you experts can help me out. I'm trying to get a query that will return the first and last values based on date and order id

I have a source table that looks like this

ID | Order ID | Date & Time | Start | Current
---------------------------------------------------------
01 | 0x0000000001 | 10-10-02 06:00:00 | 80.00 | 80.00
02 | 0x0000000001 | 10-10-02 07:00:00 | 80.00 | 70.00
03 | 0x0000000001 | 10-10-02 08:00:00 | 80.00 | 60.00
04 | 0x0000000002 | 10-10-02 06:00:00 | 80.00 | 50.00
05 | 0x0000000003 | 10-10-02 06:00:00 | 80.00 | 40.00
06 | 0x0000000001 | 10-10-02 11:00:00 | 80.00 | 30.00

And I would like it the results to be

ID | Order ID | Date & Time | Start | Current
---------------------------------------------------------
01 | 0x0000000001 | 10-10-02 06:00:00 | 80.00 | 80.00
04 | 0x0000000002 | 10-10-02 06:00:00 | 80.00 | 50.00
05 | 0x0000000003 | 10-10-02 06:00:00 | 80.00 | 40.00
06 | 0x0000000001 | 10-10-02 11:00:00 | 80.00 | 30.00

Note, this include rows 4 and 5 that have only one record (would be fine to have duplicates for those too)

 

[Galaxiom]

You need to parse the non-standard date time format.

I would permanently convert it the standard format in the table so Access can work with it as a Date field.

Add another field to your table formatted as Date and update it using this expression.

Format(Mid([Date & Time],2,2) & "/" & Mid([Date & Time],5,2) & "/" & Mid([Date & Time],8,2) & " " & Right([Date & Time],8),"General Date")

The Format function is not strictly necessary as the field format in the table will take care of this. However it would be required if you chose not to add the field to the table and just used the expression in a query.

If the new field is called DateTime your query is:

SELECT * FROM [tablename] WHERE Int([DateTime]) = Int(Max([DateTime])) OR Int([DateTime]) = Int(Min([DateTime])) ORDER BY [DateTime], ID;

Dates are held as a number of days starting 1/1/1900 with time as a the fraction of a day. The Int() function drops the time. The query then compares the date against the Minimum and maximum dates in the table. Note Min and Max not First and Last.

Recommend you stop using ampersand in your field names. Makes VBA hard to read where ampersand is used to concatenate.