bucball2007
asked on
Access Report - Grouping/Sorting
Good Morning, Everyone ~
I've been fighting with a report I need to create in Access, and I was hoping to get some assistance.
I have a table of payroll information:
Employee ID
Check Number
Detail (whether it was earning/deductions/taxes)
Detail Code (more detail on what type of deduction
Check Month
Check Year
GL Code
GL Code Name
What I need to do is create this report grouped first by month, then by GL Code, then by Employee ID + Detail + Detail Code.
Any suggestions? :)
Thanks,
Sunnie
I've been fighting with a report I need to create in Access, and I was hoping to get some assistance.
I have a table of payroll information:
Employee ID
Check Number
Detail (whether it was earning/deductions/taxes)
Detail Code (more detail on what type of deduction
Check Month
Check Year
GL Code
GL Code Name
What I need to do is create this report grouped first by month, then by GL Code, then by Employee ID + Detail + Detail Code.
Any suggestions? :)
Thanks,
Sunnie
ASKER
Hi Jim ~
Well...my issue is I've never done grouping in an Access report before, so I'm asking for advice. I went through the report wizard, but when I get to the third grouping, I cannot combine three fields for grouping.
For example - Employee ID 1 would have the following associated with each record:
Check Date Employee ID Detail Detail Code Amount
1/15 1 E Reg $1500
1/15 1 E OT $500
1/15 1 T PA $200
1/15 1 D 401k $200
1/31 1 E Reg $1500
1/31 1 E OT $300
1/31 1 T PA $200
1/31 1 D 401k $200
2/1 1 E Reg $1500
2/1 1 E OT $100
2/1 1 T PA $200
2/1 1 D 401k $200
I need to put all of the months together by GL Code, which is the easy part. But the 3rd group I need to join Employee ID, Detail, and Detail Code. I'm having trouble figuring out how to do that.
I hope that gives you more info.
Thanks,
Sunnie
Well...my issue is I've never done grouping in an Access report before, so I'm asking for advice. I went through the report wizard, but when I get to the third grouping, I cannot combine three fields for grouping.
For example - Employee ID 1 would have the following associated with each record:
Check Date Employee ID Detail Detail Code Amount
1/15 1 E Reg $1500
1/15 1 E OT $500
1/15 1 T PA $200
1/15 1 D 401k $200
1/31 1 E Reg $1500
1/31 1 E OT $300
1/31 1 T PA $200
1/31 1 D 401k $200
2/1 1 E Reg $1500
2/1 1 E OT $100
2/1 1 T PA $200
2/1 1 D 401k $200
I need to put all of the months together by GL Code, which is the easy part. But the 3rd group I need to join Employee ID, Detail, and Detail Code. I'm having trouble figuring out how to do that.
I hope that gives you more info.
Thanks,
Sunnie
Sunnie ,
<<Well...my issue is I've never done grouping in an Access report before, so I'm asking for advice. I went through the report wizard, but when I get to the third grouping, I cannot combine three fields for grouping. >>
When you say you want to group on three fields combined, in effect your saying this:
Month
GL Code
Employee ID
Detail
Detail Code
So really, you just need to group on each of the fields individually in sequence.
<<For example - Employee ID 1 would have the following associated with each record:
Check Date Employee ID Detail Detail Code Amount
1/15 1 E Reg $1500
1/15 1 E OT $500
1/15 1 T PA $200
>>
So as a double check, let's step back for a second; is the above what the records look like? If so, what should the output look like? and if this is output, what do the records look like?
Jim.
<<Well...my issue is I've never done grouping in an Access report before, so I'm asking for advice. I went through the report wizard, but when I get to the third grouping, I cannot combine three fields for grouping. >>
When you say you want to group on three fields combined, in effect your saying this:
Month
GL Code
Employee ID
Detail
Detail Code
So really, you just need to group on each of the fields individually in sequence.
<<For example - Employee ID 1 would have the following associated with each record:
Check Date Employee ID Detail Detail Code Amount
1/15 1 E Reg $1500
1/15 1 E OT $500
1/15 1 T PA $200
>>
So as a double check, let's step back for a second; is the above what the records look like? If so, what should the output look like? and if this is output, what do the records look like?
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sunnie
<<Does this clarify things?>>
Group on all the fields individually. Make the detail section hidden (visible property = false). Place controls for displaying of the Emp ID, Detail, and Detail code in the Emp, Detail, and Detail code footers along with a text box for totaling the amount.
The lines that appear in your report will only be from Group footers.
Jim.
<<Does this clarify things?>>
Group on all the fields individually. Make the detail section hidden (visible property = false). Place controls for displaying of the Emp ID, Detail, and Detail code in the Emp, Detail, and Detail code footers along with a text box for totaling the amount.
The lines that appear in your report will only be from Group footers.
Jim.
ASKER
Hey Jim ~
If I do that, then my report won't have the employee names listed, right?
Or am I missing something? :)
Thanks,
Sunnie
If I do that, then my report won't have the employee names listed, right?
Or am I missing something? :)
Thanks,
Sunnie
ASKER
Hey Jim ~
Hold on...I think I misread your comment. Let me try this, and I'll keep you updated. :)
Thanks,
Sunnie
Hold on...I think I misread your comment. Let me try this, and I'll keep you updated. :)
Thanks,
Sunnie
ASKER
Hey Jim ~
I don't see the group code footers...I see the group headers. So I'm not sure I understand all of this.
Go ahead and close this question...I'm totally at a loss.
Thanks,
Sunnie
I don't see the group code footers...I see the group headers. So I'm not sure I understand all of this.
Go ahead and close this question...I'm totally at a loss.
Thanks,
Sunnie
You can make a concatenated field combining the three fields (using &, not +), and use that field as your 3rd sort level. It is generally a good idea to do calcs in a record source query, rather than directly in the report.
Sunnie,
Your almost there! For each group level, you can specify if you want a header, footer, both, or none. That's done in the sorting and grouping option. First click "more"
Then look for the header/footer options off to the right:
And if you say that's confusing, your right. I really dislike the 2007/2010 interface. It's not obvious at all. Contrast that to A2000/2003:
Why Microsoft changed something so simple already is beyond me (well I know why, but it wasn't for the user).
Jim.
Your almost there! For each group level, you can specify if you want a header, footer, both, or none. That's done in the sorting and grouping option. First click "more"
Then look for the header/footer options off to the right:
And if you say that's confusing, your right. I really dislike the 2007/2010 interface. It's not obvious at all. Contrast that to A2000/2003:
Why Microsoft changed something so simple already is beyond me (well I know why, but it wasn't for the user).
Jim.
<<Go ahead and close this question...I'm totally at a loss.>>
Only you can close the question.
Jim.
Only you can close the question.
Jim.
Helen,
<<You can make a concatenated field combining the three fields (using &, not +), and use that field as your 3rd sort level. It is generally a good idea to do calcs in a record source query, rather than directly in the report. >>
While you can do that, it's not a great idea because you need to format it in a way that it will sort the same (the EmpID field is a numeric).
Jim.
<<You can make a concatenated field combining the three fields (using &, not +), and use that field as your 3rd sort level. It is generally a good idea to do calcs in a record source query, rather than directly in the report. >>
While you can do that, it's not a great idea because you need to format it in a way that it will sort the same (the EmpID field is a numeric).
Jim.
ASKER
Did not answer my question, but that's OK. I'll keep researching.
What do you mean I didn't answer the question? You gave up. If you can, post a db with just the reprt and the table(s) involved and I'll set it up.
Although it seems confusing at first (not having a detail section), it's very straightforward.
Jim.
Although it seems confusing at first (not having a detail section), it's very straightforward.
Jim.
It's not clear what your having an issue with. Sorting and grouping must be done in the report using the sorting/grouping options.
Because of that, were most often go wrong is in not having all the data available to the report to start, but try doing it in the report.
For example, the month; add a column to the reports underlying record source of:
Format([Check Month],"MM")
Then group on that field in the report.
If I've missed the mark, let me know exactly what it is your having a problem with.
Jim.