Results 1 to 12 of 12
  1. #1
    Levonas is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    20

    Queries, Counts, Sums, Aggregates, Pain


    My headaches come in the form of sums. I am trying to build an overview of military duty sections to see what duty section has what amount of people in it based off of rank.
    I have queries that divide up and show who is E1 to E4 assigned to Duty Section 1, and another for E5 to E6 assigned to Duty Section 1. I have that set up for all 8 duty sections.
    My table has these fields; Last, First, Rank, School, Indoc, Phone, Address, Section Assigned, NMT, PAC, Chapel, Restricted
    I have been racking my brain here and need help in the best way to have something to show how many E1 - E4 are in section 1, how many E5 and E6 are in section 1, repeated for all the sections and displayed in one snapshot so I can decide which section needs people without going to each report to see who has what number of personnel.
    Any suggestions on this? I was trying to do a query to add up how many unique records were in each section then show it on a report for easy viewing.
    I just cannot figure out how to do this because I get all sorts of mismatch blah blah errors when trying to use sum and count. I can't figure out how to sum a aggregate count field in a query. I'm just at wit's end trying to solve this and no amount of searching has yielded an answer yet.
    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Do you want all E1s to E4s counted as one group and then all E5s to E6s counted as another group? E6 is the highest?

    Calculate a field in query that will assign a group ID to each record.

    GrpID: IIf([Rank]<"E5", 1, 2)

    Consider creating a report and using its Grouping & Sorting features with aggregate calcs in header/footer sections. This will allow display of detail records as well as summary data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Levonas is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    20
    In a way yes. E6 is the highest. I have about 1500+ people I am tracking, adding, assigning, and deleting. When I assign I use an overview to see every section's total E1-E4 and their total E5-E6.
    I want display all info on one report showing how many E1-E4 are in duty section one. How many in two. All the way up to Eight. As well as doing it for all E5-E6, all on the same report so I get the overview of who has what and who needs what.
    I have reports that show who is in there and counts them in the header separately. I just need one report to show the breakdown.
    I might use the calc fields to assign a 1 in eight separate fields pending what section they are in. Then sum those separately in a query, then do a report off that.
    I don't know, maybe that is the hard way?
    BTW, all these ranks have different rating attached to them like SN, SA, CTR1, LS2. The whole E1, E2, etc is not what is entered as their rank so that complicates things.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    8 fields calculated - that's an option but maybe not the easiest.

    I suggest a report with multi-tier grouping and counts at whatever group levels you want.

    [Section] group
    [GrpID] group
    individuals' raw data in detail section

    How do you want to deal with the ratings? How does that impact GrpID designation?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Levonas is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    20
    Okay.
    The rating issue. They may be E5 or E2, but they are identified by different rates and not pay grade. So I can have a CTN3 being an E4, or an LS1 being an E6. Is their a way to filter anything that has a "3" in it or "SR", or "SA", or "SN" as the E1-E4 group, and anything with a "2" or "1" as the E5-E6 group with the multi-tier grouping? I am trying avoid the annoyance of going back and assigning E1 to all SRs, and so on, and then adding in that input to the admin form. It might have to happen, but if not, great.

  6. #6
    Levonas is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    20
    Thanks by the way. This is definitely helping me get close to finalizing this.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How many ratings are there? Can do a calc with any field you want for assignment to category as you desire. At some point, if there are enough values to deal with or the assignment rules complicated enough, might need a table that sets these assignments.

    So, maybe:

    GrpID: IIf([Rating] LIKE "*1*" Or [Rating] LIKE "*2*", 1, 2)

    At this point, I am a little fuzzy on what you want for report structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Levonas is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    20
    There are a lot of ratings in the Navy. That is why I was looking at a variable to look for anything with a "3" for third class aka E4, or a SR (E1), SA (E2), SN (E3). I could easily keep E5 and E6 null and just filter to those null for the query.
    For the structure, I want to have one column that will have a label of "DS 1 E1-E4" in row one, "DS2 E1-E4" on the second row, etc. To the right of each label will be the result of the query showing how many E1-E4 are in that section.
    Then I will make another column on the same report that will do the same thing saying "DS 1 E5-E6" etc with the resulting total for that section. Of course repeat for all eight sections.
    Just so on one single report I can view the break down of numbers by those rank groups.
    I am seriously considering pushing the Rate / Rank input instead of just Rate. At least the rank is cut and dry, which is the E1, E2, etc. Not ideal but it seems it might be the easy way in the long run, unless there is code to specifically pick out the "3" from any number of rates LS3, OS3, AO3, CTT3, CTR3, CTN3, CTM3, AE3, HT3, MM3, the list goes on and on lol. The "3" is the issue really, because they are lumped with the SR, SA, and SNs. Else this would be easy haha. I could just look for those alone and the rest I could tell the code that if they are not SR, SA, SN to give me those totals per section.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As I said, define the rules and build expression(s) based on rules or build a table.

    Yes, expression can look for "3" and assign value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are a lot of ratings in the Navy.
    I have been thinking about this.... I think the suggestion of using a table (look up table) by June is a good idea.

    I threw together a dB...... I probably got it wrong, so my apologies. Hopefully this will help...

  11. #11
    Levonas is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    20
    Well I wish I had the easy way haha, but I ended up creating an input for the rank separate from the rate with a drop down to force the whoever does the input to be only E1-E6.
    I then made a column for each section and wrote an IIF function: IIf([SECTION ASSIGNED]=1 And [RANK]<"E5",1,IIf([SECTION ASSIGNED]=1 And [RANK]>="E5",2,Null)) So that way I have an easy way to filter them.
    The next challenge I faced was trying to avoid 16 separate queries to count each of the 8 sections' columns to see how many were assigned a "1" or a "2". I have failed to figure out a way to do that, so I have a query for each to count the "1" and "2" for each of the 8 columns, yes giving me 16 separate queries. Dear lord I wish there was an easier way. I am going to have a bazillion queries lol.
    I swear if I didn't have so many other things to do here, I'd have this program down pat but I am juggling this collateral duty with a plethora of other duties.
    I really do appreciate your all's input and helping me out. I am learning from each post and trying to reverse engineer your sample database to understand how someone else might approach this.

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well I wish I had the easy way haha, but I ended up creating an input for the rank separate from the rate with a drop down to force the whoever does the input to be only E1-E6.
    I would have a combo box in a form with the row source a value list. The only options for the field would be from the drop down list.


    I then made a column for each section and wrote an IIF function: IIf([SECTION ASSIGNED]=1 And [RANK]<"E5",1,IIf([SECTION ASSIGNED]=1 And [RANK]>="E5",2,Null)) So that way I have an easy way to filter them.
    I hope that this is a "column" in a query and not in a table.

    .... each of the 8 sections' columns......
    This sounds like the table is not normalized. Would you expound on this comment a little more? What is the table structure?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-05-2014, 11:13 PM
  2. Multiple counts/sums needed
    By Audreysfirstdaughter in forum Queries
    Replies: 6
    Last Post: 05-25-2011, 02:14 PM
  3. Replies: 2
    Last Post: 05-05-2011, 12:41 PM
  4. Sums of Multiple Queries
    By flsticks in forum Queries
    Replies: 5
    Last Post: 09-16-2010, 09:32 AM
  5. Navigation Pane pain
    By Felmer Dingle in forum Access
    Replies: 0
    Last Post: 06-17-2010, 11:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums