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