Can I sort by field A but group by field B?

[Michael Nagan]

My data looks something like this:



Date Job ID Expense

1/1/07 1 $100

1/2/07 1 $50

1/3/07 2 $75

1/4/07 1 $500

1/5/07 3 $200



I'd like to be able to sort by date and break/group on job ID. If I were to
sum expense, I'd like to end up with:



Job ID Expense

1 $150

2 $75

1 $500

3 $200



I'm looking for a simple way to accomplish this, hopefully in a query.



Thanks.



Michael

(e-mail address removed)

 

[Allen Browne]

Your example outputs Job 1 twice because there are 2 dates. It therefore
appears that you want to group by the combination of date plus job.

1. Create a query using this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row th the grid.

3. Drag [Date] into the grid.
Accept Group By in the Total row under this field.
Choose Ascending in the Sort row.
Uncheck the Show box.

4. Drag [Job ID] into the grid.
Accept Group By in the Total row.

5. Drag [Expense] into the grid.
Choose Sum in the Total row.

BTW, if you really have a field named Date, it is going to cause you grief,
as it is a reserved word in JET SQL and also in VBA code. Access will
confuse it with the system date (and other things.) Consider renaming it as
JobDate or something else.

If you want a (lengthy) list of the words to avoid as field names, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[Michael Nagan]

Thanks for your help, but I don't think this quite works for me, since I
don't want a new group for every different date. If I'm understanding you
correctly, the ouput of your approach (using the data set I included) would
be identical to the original data set. I was hoping to combine the first
two items (despite different dates) because the sequence doesn't change job
number until the third item.

Your example outputs Job 1 twice because there are 2 dates. It therefore
appears that you want to group by the combination of date plus job.

1. Create a query using this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row th the grid.

3. Drag [Date] into the grid.
Accept Group By in the Total row under this field.
Choose Ascending in the Sort row.
Uncheck the Show box.

4. Drag [Job ID] into the grid.
Accept Group By in the Total row.

5. Drag [Expense] into the grid.
Choose Sum in the Total row.

BTW, if you really have a field named Date, it is going to cause you
grief, as it is a reserved word in JET SQL and also in VBA code. Access
will confuse it with the system date (and other things.) Consider renaming
it as JobDate or something else.

If you want a (lengthy) list of the words to avoid as field names, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My data looks something like this:

Date Job ID Expense
1/1/07 1 $100
1/2/07 1 $50
1/3/07 2 $75
1/4/07 1 $500
1/5/07 3 $200

I'd like to be able to sort by date and break/group on job ID. If I were
to sum expense, I'd like to end up with:

Job ID Expense
1 $150
2 $75
1 $500
3 $200

I'm looking for a simple way to accomplish this, hopefully in a query.

 

[Allen Browne]

So what date do you want?
The most recent?
If so, use Max under the date field at step 3.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your help, but I don't think this quite works for me, since I
don't want a new group for every different date. If I'm understanding you
correctly, the ouput of your approach (using the data set I included)
would be identical to the original data set. I was hoping to combine the
first two items (despite different dates) because the sequence doesn't
change job number until the third item.

Your example outputs Job 1 twice because there are 2 dates. It therefore
appears that you want to group by the combination of date plus job.

1. Create a query using this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row th the grid.

3. Drag [Date] into the grid.
Accept Group By in the Total row under this field.
Choose Ascending in the Sort row.
Uncheck the Show box.

4. Drag [Job ID] into the grid.
Accept Group By in the Total row.

5. Drag [Expense] into the grid.
Choose Sum in the Total row.

BTW, if you really have a field named Date, it is going to cause you
grief, as it is a reserved word in JET SQL and also in VBA code. Access
will confuse it with the system date (and other things.) Consider
renaming it as JobDate or something else.

If you want a (lengthy) list of the words to avoid as field names, see:
http://allenbrowne.com/AppIssueBadWord.html

My data looks something like this:

Date Job ID Expense
1/1/07 1 $100
1/2/07 1 $50
1/3/07 2 $75
1/4/07 1 $500
1/5/07 3 $200

I'd like to be able to sort by date and break/group on job ID. If I
were to sum expense, I'd like to end up with:

Job ID Expense
1 $150
2 $75
1 $500
3 $200

I'm looking for a simple way to accomplish this, hopefully in a query.