Grouping and sorting fields in a query

[pm]

Hi I have a query that gives me location(store number), terms(3 month, 6
month etc) and Volume. I'd like to sort it by Location and terms. Please
help!!
Here's an example:

Location Term Volumne
2 3 mo 1,000
6 mo 1,000
12 mo 1,000
Total 3,000
5 3 mo
6 mo

 

[pm]

A better example:

Location.>>>>Term>>>>Volume
2....................3mo...........1,000
.......................6mo...........1,000
......................12mo...........1,000
Total................................3,000
5....................3mo............2,000
.......................6mo............2,500
etc.

 

[Maarkr]

you didn't say if you're using this data in a report or what... for a report,
use the report wizard and group first by location and next by terms... you
may need to make adjustments for the order in your terms field.

 

[pm]

Thanks Maakr - - - how can i sort the terms in the query?

Terms are as follow: I3 = 3mo, I6 = 6mo, 1Y = 12mo, 18 = 18 mo, 2Y = 24,
and 3R = 36.

 

[John Spencer (MVP)]

Easiest way would be to make a table with two fields
TermCode - I3, 1Y etc
TermOrder - 3, 12, etc with a number of months or just an order number

Then add the table to your query linking TermCode to Term and including the
field TermOrder so you can sort by the TermOrder field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[pm]

Thanks John - That works.

Easiest way would be to make a table with two fields
TermCode - I3, 1Y etc
TermOrder - 3, 12, etc with a number of months or just an order number

Then add the table to your query linking TermCode to Term and including the
field TermOrder so you can sort by the TermOrder field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County