Querying earliest and latest times

[BillyDo]

Hi all

Wonder if anyone can help with this one.

I have a database for managing event bookings. Here is a simplified version of the table structure

TBL:Events
EventId (PK)
Reference
VenueID (Linked to TBL:Venue)
Date
StartTime
FinishTime

TBL:Venue
VenueId
VenueNAme

I want to be able to query the Events table using the Reference field so that for each record it shows the earliest StartTime and the latest FinishTime.

Is this possible?

Many thanks

 

[MStef]

If you want "for each record", in that case you don't need a query.
You have got the earliest start time in the field "Start Time", also
the latest finish time int he field "Finish Time", (for each record).

 

[BillyDo]

If you want "for each record", in that case you don't need a query.
You have got the earliest start time in the field "Start Time", also
the latest finish time int he field "Finish Time", (for each record).

Hi, thanks for your reply.

I should have explained that in the reference field there is a 3 digit number. There are duplicates of the reference field though- so the data looks a bit like this

Reference Start Finish
100 10am 2pm
100 11am 3pm
100 1pm 3pm
101 10am 2pm
101 11am 3pm

So for each value in the reference field, I want the earliest and latest times...

Hope that makes more sense...

 

[MStef]

OK, it means "for each Reference".
Look at "DemoEarliestLatestTA2000.mdb" (attachment, zip).
Look at Table1, queries. Run Query3EL.

 

[MStef]

You can do it via one Query.
Look at a better Demo.
Rune "Query11".

 

[BillyDo]

You can do it via one Query.
Look at a better Demo.
Rune "Query11".

Excellent- just what I needed.

Thanks a million for your help

 

[BillyDo]

You can do it via one Query.
Look at a better Demo.
Rune "Query11".

Thanks again

Do you know an expression I can now use in the query to calculate the difference in hours between the earliest and the latest times?

 

[MStef]

Look at a new Demo (attachment, zip).
Look at Query33. Run Query33.
In Query11 and query33 put format SHORT TIME for all time column.