Custom sorting and grouping in a query

[Jimmy Tran]

Hello everyone,

I have a table with many columns. Two of them are VehicleNumber and
ShipDate. What I would like to do is sort all the data in ascending
date(ShipDate) but I also like to group the matching VehicleNumber
together at the same time(If they have same ShipDate; please note
grouping here means putting them in adjacent rows). VehicleNumber does
not need to get grouped together when they have different ShipDate. I
try simple queries but I keep getting "VehicleNumber not part of
aggregate function" and etc. Maybe my problem is because I am trying to
group VehicleNumber while my other fields have different values, as you
can see for example the Size column.

my sample table:
VehicleNumber ShipDate ... Size
123 9/9/2004 5
434 9/10/2004 5
123 9/9/2004 16
123 9/13/2004 4

my output query:
VehicleNumber ShipDate ... Size
123 9/9/2004 5
123 9/9/2004 16
434 9/10/2004 5
123 9/13/2004 4

Thank You a whole lot for helping me out.
Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Squirrel]

Hi Jimmy,

I think you have two choices here:

[1] A simple select statement with an 'order by' ShipDate, VehicleNumber,
Size.
This would return a sorted listing of every record.

[2] A 'group by' statement:
Select ShipDate, VehicleNumber from tablename Group By ShipDate,
Vehicle Number.
In this case you must omit the Size field.

Suggest you try both, look at the listings and see if either will meet your
needs.

HTH -Linda

"Jimmy Tran" <hu*******@yahoo.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Hello everyone,

I have a table with many columns. Two of them are VehicleNumber and
ShipDate. What I would like to do is sort all the data in ascending
date(ShipDate) but I also like to group the matching VehicleNumber
together at the same time(If they have same ShipDate; please note
grouping here means putting them in adjacent rows). VehicleNumber does
not need to get grouped together when they have different ShipDate. I
try simple queries but I keep getting "VehicleNumber not part of
aggregate function" and etc. Maybe my problem is because I am trying to
group VehicleNumber while my other fields have different values, as you
can see for example the Size column.

my sample table:
VehicleNumber ShipDate ... Size
123 9/9/2004 5
434 9/10/2004 5
123 9/9/2004 16
123 9/13/2004 4

my output query:
VehicleNumber ShipDate ... Size
123 9/9/2004 5
123 9/9/2004 16
434 9/10/2004 5
123 9/13/2004 4

Thank You a whole lot for helping me out.
Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jimmy]

What if I need to have all the other fields in the query and I only
want to group by Vehicle # and order by SHIP DATE. Is it possible to
do without getting the aggregrate error from Access?

[Pieter Linden]

hu*******@yahoo.com (Jimmy) wrote in message news:<fc**************************@posting.google. com>...

What if I need to have all the other fields in the query and I only
want to group by Vehicle # and order by SHIP DATE. Is it possible to
do without getting the aggregrate error from Access?

Create a select query, include your totals query and then throw in any
fields you want. And while you're at it, have a beer and a smoke.