How to build query

[WarrenG]

I have multiple vehicles that are scheduled to leave at different times. I
would like to have a query that returns for each record, each vehicle ( V1,
V2 and V3)in order of its scheduled departure time (ascending), then repeat
for subsequent dates. The vehicle ID and scheduled departure time are in the
same table. eg if V1 departs at 0900 and 1000 and V2 departs at 0600 0900
and 1200 and V3 departs at 1100, 1300 and 1500, then the result I am after
looks like the following
V1 V2 V3
0900 0600 1100
1000 0900 1300
1200 1500
This then repeats for each day.
I hope someone can help as I am completely stuck on this one!
Regards
Warren

 

[Imran J Khan]

Wouldn't repeating be done in a report? Maybe what you need is to create a
report, and in the report wizard, choose to sort data according to Date,
Vehicle, and departure times.

Imran J Khan

 

[WarrenG]

Yes it normally would but I am just making a query for my website man to
create a web page as the report and he hasn't been able to succeed yet and
tells me that if the data was as described in a query, then we could achieve
our goal.
Thanks
Warren

 

[Jeff Boyce]

Have you looked into using a cross-tab query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lance]

Can you list what fields are in each table?

 

[WarrenG]

I haven't used crosstab queries before, but after looking at one how do you
return multiple values for the same date? I have date as the row header, rego
ID as the column header, with Departure time as the result, but it is asking
me for this to be an aggregate function, where I need it to list all the
departure times in ascending order.
Regards
Warren

 

[WarrenG]

The fields in the table are ID, DFRDate, RegoID and DepartureTime.
Regards
Warren

 

[Jeff Boyce]

Warren

If you haven't used crosstab queries before, spend a little time with Access
HELP looking over what the various pieces are and how they're used. This
might answer your question.

Or you could try using the query wizard, selecting crosstab and following
the wizard instructions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[WarrenG]

Thanks Jeff, I have got it to work with a crosstab query now that I have
added an extra sort field to it.
Thank you very much for your help
Warren