Sorting two time fields in the same record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mick - 18 Jan 2008 18:54 GMT

Hi, I have two time fields within each record of the database. Is it possible
to interleave the times from each recordso they are sequential.

e.g.
Record1, Time1 = 12.00 Time2 = 13.00 and Record2 Time1 = 12.30 Time2= 14.00

I want the query to report in this order Record1Time1 then record2Time1
then Record1Time2 then record2time2

Thank you in advance Mick

Reply to this Message

Jeff Boyce - 18 Jan 2008 19:09 GMT

Mick

One approach to this might be to create a UNION query that returns the times
from source1, source2, source3, source4, then sort that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

> Hi, I have two time fields within each record of the database. Is it
> possible
[quoted text clipped - 8 lines]
>
> Thank you in advance Mick

Reply to this Message

Jerry Whittle - 18 Jan 2008 19:21 GMT

You could with a Union query something like below:

SELECT tblTimeInterleave.Time1 AS SortedTime,
tblTimeInterleave.Time1,
tblTimeInterleave.Time2
FROM tblTimeInterleave
UNION ALL
SELECT tblTimeInterleave.Time2 AS SortedTime,
tblTimeInterleave.Time1,
tblTimeInterleave.Time2
FROM tblTimeInterleave
ORDER BY SortedTime;

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hi, I have two time fields within each record of the database. Is it possible
> to interleave the times from each recordso they are sequential.
[quoted text clipped - 6 lines]
>
> Thank you in advance Mick

Reply to this Message

Mick - 20 Jan 2008 20:34 GMT

So far so good many thanks. I have a second question please. Each record also
has two date fields I also wish to interleave these two dates and the two
times as previous enquiry such that it produces a date and time order query
using both dates and times, thank you for your help,
Mick

> You could with a Union query something like below:
>
[quoted text clipped - 19 lines]
> >
> > Thank you in advance Mick

Reply to this Message

KARL DEWEY - 18 Jan 2008 19:25 GMT

Use a union query. Instead of primary key use a field that distinguishes
record1 from record2.
SELECT [Change Requests].Primary_Key, [Change Requests].[Date open] AS MyDate
FROM [Change Requests]
UNION
SELECT [Change Requests].Primary_Key, [Change Requests].[Date close] AS MyDate
FROM [Change Requests], [Change Requests] AS [Change Requests_1];

Signature

KARL DEWEY
Build a little - Test a little

Reply to this Message

Mick - 18 Jan 2008 19:56 GMT

Many thanks to Jeff, Jerry and Karl for your swift replies. I will have a go
and let your all know how it went. Many thanks kind reagrds Mick

Reply to this Message