Results 1 to 3 of 3
  1. #1
    hoachen is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    17

    First Group, Second count, and third sum

    First Group, second count and third sum in Query
    Ms Access 2007, windows 7

    I am trying simplify or eliminate some of the steps to get what I need, but it seems did not really work out and desperately need your expertise!



    Based on the below table, I do a make table on each GroupName and i then take each individual groupName table to match any InvName to count and sum of what I need. I have about 50 different GroupName, this mean that I have to do it individually to get what I need become a little to inconvenience and unpractical.

    I hope you --an expert can help me to simplify this. Thanks a lot for reading my post!

    Here is the table and and the output that i want.


    GroupID GroupName GroupMaster InvName Price
    3445 Min4567 LinkMin4567 Inv090 64
    3445 Min4567 LinkMin4567 Inv876 64
    3445 Min4567 LinkMin4567 Inv556 512
    3445 Min4567 LinkMin4567 Inv652 512
    3445 Min4567_00 LinkMin4567 Inv11 35
    3445 Min4567_789 LinkMin4567 Inv21 48
    8709 Leo9809 LinkLeo9809 Inv222 30
    8709 Leo9809 LinkLeo9809 Inv56 30
    8709 Leo9809_b LinkLeo9809 Inv556 512
    8709 Leo9809_b LinkLeo9809 Inv652 512
    1256 Hpe1112 LinHpe009 Inv652 512
    1256 Hpe1112 LinHpe009 Inv454 98
    1256 Hpe1112 LinHpe009 Inv556 512
    1256 Hpe1112 LinHpe009 Inv578 97
    1256 Hpe1112 LinHpe009 Inv222 30

    1. Group all the GroupName and count how many InvName under that group
    Example : GroupName "Min4567", a total of 4 InvName

    2. After grouping, look for any InvName that are same as Min4567 and then count and sum how many InvName are same. *the price are the same when they have same InvName
    Example: GroupName "Min4567", a total of 4 InvName, Inv556 are on Leo9809_b and Hpe1112 so this example should display as below

    Output
    GroupID GroupName GroupMaster TotalInv Nameby GroupName Total Price InvName Share InvName Total Price Share InvName Other GroupName1 Other GroupName2 Other GroupName3
    3445 Min4567 LinkMin4567 4 1152 (sum of 64+64+512+512) 2 1024 Leo9809_b Hpe1112
    3445 Min4567_00 LinkMin4567 1 35 0 0
    3445 Min4567_789 LinkMin4567 1 48 0 0
    8709 Leo9809 LinkLeo9809 2 60 1 30
    8709 Leo9809_b LinkLeo9809 2 1024 1 512 Hpe1112 Min4567 Leo9809
    1256 Hpe1112 LinHpe009 5 1249 3 1054 Leo9809_b

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The first two Total columns are easy to produce from GROUP BY (aggregate) query. The rest are not easy to produce by any query. I expect will require some elaborate VBA code looping through recordset and writing data to temp table.
    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
    hoachen is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    17
    Quote Originally Posted by June7 View Post
    The first two Total columns are easy to produce from GROUP BY (aggregate) query. The rest are not easy to produce by any query. I expect will require some elaborate VBA code looping through recordset and writing data to temp table.
    Thanks for your replied. I did made it a table to count and then sum them. I am still working on how to make it do show on other groupName1,2,3...

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

Similar Threads

  1. Replies: 3
    Last Post: 04-19-2012, 01:22 PM
  2. nested query: avg, count, group by
    By gap in forum Queries
    Replies: 2
    Last Post: 07-05-2011, 07:48 AM
  3. Replies: 0
    Last Post: 04-25-2011, 07:58 PM
  4. GROUP and COUNT?
    By TheMoodyFiles in forum Queries
    Replies: 2
    Last Post: 02-10-2011, 05:50 PM
  5. SQL Count and Group By
    By Tyork in forum Programming
    Replies: 11
    Last Post: 01-24-2011, 09:06 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