By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,045 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,045 IT Pros & Developers. It's quick & easy.

Custom sorting and grouping in a query

P: n/a
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!
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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!

Nov 13 '05 #2

P: n/a
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?
Nov 13 '05 #3

P: n/a
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.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.