If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can I sort by field A but group by field B?
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 |
#2
|
|||
|
|||
Can I sort by field A but group by field B?
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/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Michael Nagan" mnagan AT omniprint.net wrote in message ... 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. |
#3
|
|||
|
|||
Can I sort by field A but group by field B?
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. "Allen Browne" wrote in message ... 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/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Michael Nagan" mnagan AT omniprint.net wrote in message ... 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. |
#4
|
|||
|
|||
Can I sort by field A but group by field B?
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Michael Nagan" mnagan AT omniprint.net wrote in message ... 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. "Allen Browne" wrote in message ... 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/Ap****ueBadWord.html "Michael Nagan" mnagan AT omniprint.net wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|