Results 1 to 3 of 3
  1. #1
    jay_brubin is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2009
    Posts
    2

    how do i perform a Count(Distinct) query for 2 different column values using GROUP BY

    how do i perform a Count(Distinct) query for 2 different column values using GROUP BY

    I have this "source" table like this....



    |-------------------------------------|
    |ROLLUP_ID | GAP_ID | REQ_ID | COST |
    |-------------------------------------|
    |001 | 001 | 002 | 12.00 |
    |001 | 002 | 002 | 15.00 |
    |005 | 005 | 009 | 9.00 |
    |-------------------------------------|

    I need a group by query with distinct for ms access 2007...

    to make the following...aggregate query

    |------------------------------------------------------|
    |ROLLUP_ID | GAP_COUNT | REQ_COUNT | TOTAL_COST |
    |------------------------------------------------------|
    |001 | 2 | 1 | 27.00 |
    |005 | 1 | 1 | 9.00 |
    |------------------------------------------------------|

    Any help would be so grateful...

    I looked at this suggestion but I am not sure its applicable:

    http://blogs.msdn.com/access/archive...in-access.aspx

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    move Gap_ID one position to the left and make it Groupby - it doesn't give you the value 1 but rather the value it is grouping on....in your example this column's value (the way you want) is always 1 - thus it becomes an abstraction....if you really need a column always showing the number 1 you can make that happen I suppose....

  3. #3
    jay_brubin is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2009
    Posts
    2
    Quote Originally Posted by NTC View Post
    move Gap_ID one position to the left and make it Groupby - it doesn't give you the value 1 but rather the value it is grouping on....in your example this column's value (the way you want) is always 1 - thus it becomes an abstraction....if you really need a column always showing the number 1 you can make that happen I suppose....
    Mabye its the bad formating of my source and result table...but I want to sum and count the unique requirments and gap for a rollup....so moving the following does not help me:

    SELECT GapRollupCostAnalysis.GapID,
    SUM(GapRollupCostAnalysis.RollupCost)
    FROM GapRollupCostAnalysis
    GROUP BY GapRollupCostAnalysis.GapID
    ;

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

Similar Threads

  1. Replies: 3
    Last Post: 10-16-2009, 09:27 AM
  2. Lookup values in one column from another record
    By cjayjones in forum Queries
    Replies: 16
    Last Post: 08-05-2009, 02:27 PM
  3. Distinct Values
    By Acramer8 in forum Reports
    Replies: 1
    Last Post: 06-15-2009, 08:37 AM
  4. Replies: 0
    Last Post: 08-08-2008, 08:34 AM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 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