Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Report - Grouping/Sorting

Posted on 2011-09-12
14
Medium Priority
?
392 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:bucball2007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 58
ID: 36523447
<<Any suggestions?  :)>>

  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.
0
 

Author Comment

by:bucball2007
ID: 36523906
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
0
 
LVL 58
ID: 36524111
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.

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Accepted Solution

by:
bucball2007 earned 0 total points
ID: 36524664
Hi Jim ~

I want the report to be grouped by:  (1) month/year, (2) GL Code, (3) Employee ID, Detail, Detail Code.  So the end result would be:

January 2011
          GL Code 111-1111-111
                    EmpID     Det     DetCode     Amount
                        1            E           Reg         $3000
                        1            E           OT           $800
         GL Code  222-2222-222
                    EmpID     Det     DetCode     Amount
                        1            T       PA Tax         $400
         GL Code  333-3333-333
                    EmpID     Det     DetCode     Amount
                        1            D       401k            $400
February 2011
        GL Code 111-1111-111
                    EmpID     Det     DetCode     Amount
                        1            E           Reg         $3000
                        1            E           OT           $500
         GL Code  222-2222-222
                    EmpID     Det     DetCode     Amount
                        1            T       PA Tax         $400
         GL Code  333-3333-333
                    EmpID     Det     DetCode     Amount
                        1            D       401k            $400

If an employee has multiple paychecks within a month, the E - REG I want combined, not to show as 2+ lines.

Does this clarify things?

Thanks,

Sunnie
0
 
LVL 58
ID: 36524805
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.
0
 

Author Comment

by:bucball2007
ID: 36524963
Hey Jim ~

If I do that, then my report won't have the employee names listed, right?

Or am I missing something?  :)

Thanks,

Sunnie
0
 

Author Comment

by:bucball2007
ID: 36524973
Hey Jim ~

Hold on...I think I misread your comment.  Let me try this, and I'll keep you updated.  :)

Thanks,

Sunnie
0
 

Author Comment

by:bucball2007
ID: 36525028
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
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36525040
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.
0
 
LVL 58
ID: 36525109
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"

 Screen shot sorting and grouping
 Then look for the header/footer options off to the right:

Header/footer options
 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:

 Old sort/grouping options

  Why Microsoft changed something so simple already is beyond me (well I know why, but it wasn't for the user).

Jim.
0
 
LVL 58
ID: 36525116
<<Go ahead and close this question...I'm totally at a loss.>>

 Only you can close the question.

Jim.
0
 
LVL 58
ID: 36525136
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.
0
 

Author Closing Comment

by:bucball2007
ID: 36553538
Did not answer my question, but that's OK.  I'll keep researching.
0
 
LVL 58
ID: 36526351
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question