how do i perform a Count(Distinct) query for 2 different column va

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/2007/09/19/writing-a-count-distinct-query-in-access.aspx
0
Utf
11/18/2009 9:43:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
892 Views

Similar Articles

[PageSpeed] 18

Jay -

You may need to create separate queries for each count(distinct) you want, 
and one to calculate the total cost, then combine the separate queries in a 
final query using the ROLLUP_ID as the join field.  

-- 
Daryl S


"jay" wrote:

> 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/2007/09/19/writing-a-count-distinct-query-in-access.aspx
0
Utf
11/19/2009 4:36:10 PM
Reply:

Similar Artilces:

negative values causing column title problem
How do I get the Column titles x axis lables at he bottom of the chart field when have negative values It s friday and this is killing me. Select the axis Choose Format>Selected axis On the Patterns tab, for Tick Mark Labels, select Low TFrisch wrote: > How do I get the Column titles x axis lables at he bottom > of the chart field when have negative values > > It s friday and this is killing me. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html hey thanks - boy was thaqt annoying >-----Original Message----- >Select th...

counting data from a series
I have from a suvey in a 3 column table, where COL1=survey number, COL2=question number, and COL3=response, as such: COL1 COL2 COL3 a Q1 2 b Q1 3 c Q1 1 a Q2 3 b Q2 3 c Q2 1 a Q3 2 b Q3 3 c Q3 1 etc.. How can I calculate all the "3" responses for all Q2s? For example, based on the above sample table, I would expect the formula to return "2". =SUMPRODUCT(--(B1:B9="Q2"),--(C1:C9=3)) Hope this helps! In article ...

Two different totals in one cell
C D L O P Q R H, AL A, AL A A P SF H, AL 1.0, 1.0 1.0, 1.0 7.50 8.5 0.00 0.00 1.0, 2.0 As you can see, “A” (shown in columns D and L) and “AL” (shown in columns C and D) are in two separate columns; I would like to show their totals in two separate columns, i.e, O (totals of all “A”) and R (totals of “AL”) in the example above. Try these: =3DCOUNTIF(C1:L1,"*A*") =3DCOUNTIF(C1:L1,"*AL*") Hope this helps. Pete On May 4, 4:27=A0pm, something68 <somethin...@discussions.microsoft.com> wrote: > C =A0 =A0 =A0 D =A0 =A0 =A0 L =A0 =A0 =A0 O ...

Multiple copies of emails #2
I've read some of the other posts on this problem, but nothing I've tried seems to work. Here's the background. I'm using Outlook 2000 (SP-3). I have 3 different email accounts with comcast that I have forwarded to my Outlook. If some sends an email to any one of those account, I end up getting 3 copies of it in my inbox ... all to the same one of my email addresses. I know this isn't a problem with Comcast, because when I look at my messages on their server, there's only one copy of each. So I figure it must have something to do with the Rules ... but w...

DateAdd Working Day Query
Hi, I wonder if someone could help me please. I'm trying to write an expression (see code below) within a query where I calculate 15 working days from a a given date, in this case the 'Date a report was sent'. DateAdd(“ww”,15,[Date Report Sent]-DateAdd([Date Report Sent],1)*2-IIf (Weekday([Date Report Sent],1)=7,IIf(Weekday([Date Report Sent],1)=7,0,1),IIf (Weekday([Date Report Sent],1)=7,-1,0))) But when I add it to my query I keep getting this message: 'The expression you entered has a function containing the wrong number of arguments.' I'm assuming, maybe wrongl...

Add a goal line to a column chart
I have several column charts which I need to add different goal lines. Any suggestions? The goal has to be represented as a line and not as a column. Thanks for your help! http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brandy" <Brandy @discussions.microsoft.com> wrote in message news:EF99E151-13A4-461D-B9CA-DECE4E467904@microsoft.com... >I have several column charts which I need to add different goal lines. Any > sugg...

Update Query and record lock violations
I have a local table constructed from two remote sqltables. I add records successfully via an append query in Access 2003. I am attempting to modify date records that have changed in the remote table to the local table (they change occasionally in the sqltable) via update query. I have the query set to pull date and time from the sqltable (there is a common key to both tables that acts as the master key in the local table) and update the date and time in the local table where the date and time are not equal in both tables. It seems to work - it returns exactly the number of records ...

Dynamics 2.0 Locks up
All of a sudden yesterday my dynamics POS 2.0 started locking up when it was minimized or idol for a few minutes of non use. Has anyone else had this problem? I am running Vista. Thanks If you haven't already, you may want to post this question in the retail management forum. -- Charles Allen, MVP "shadowrunner11" wrote: > All of a sudden yesterday my dynamics POS 2.0 started locking up when it was > minimized or idol for a few minutes of non use. Has anyone else had this > problem? I am running Vista. > > Thanks ...

RMS SO 2.0 Manager
On one of my RMS SO 2.0 Manager's computer I'm having a huge delay (about 5 minutes) before the Item list shows up (Database - Items). This only happens on this one machine that's "directly" connected to the RMS server (no VPN etc), while it's trying to bring up the Items List the SOMANAGER.EXE process CPU Utilization stays at 99% and there is no network utilization/activity. When I bring up an (all) Item List/Value report it doesn't take very long at all. Has anybody else encountered this problem and knows of a potential solution? I've already tried r...

Installing office 2000 disk 2 with publisher on upgraded windows x
I can not get disk 2 to install. It preloads then as files start to copy, it stalls then ends with error message. this is office2000 professional 2 cd set. everything else works exept publisher and its components. Please advise Thanks What is the error message? -- JoAnn Paules MVP Microsoft [Publisher] "cugolfn" <cugolfn@discussions.microsoft.com> wrote in message news:F57B8716-65FA-4619-93A4-AA24D44ABC33@microsoft.com... >I can not get disk 2 to install. It preloads then as files start to copy, >it stalls then ends with error message. this is office2000 profess...

Named Ranges on different sheets
Does anyone know how to assign a named range to two seperate ranges on two seperate sheets? Assigned Names is a workbook object and not a sheet object. What I sometimes do is to strip off the sheet name form the named object so I can use the object on more than one sheet. You are trying to use the same name on two different sheet which can't be done. What can be done is to use a cell data to identify where a table starts. What some people do is to put on the worksheet something like "Yearly Expense". Then use FIND to locate the cell where "Yearly Expen...

Unhiding Protected Rows and Columns? is it possible?
I have a workbook with a number of worksheets that all interlink. I have protected a number of cells in one of the sheets. the problem arrises in that the unprotected cells cannot be hidden or indeed unhidden when the sheet is protected. Is there a way I can protect cells whilst still keeping the formatting ability to widen / hide rows etc. thanks -- guilf ------------------------------------------------------------------------ guilf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25608 View this thread: http://www.excelforum.com/showthread.php?threadid=39...

Working hours #2
Hi, I have helpdesk inquiries logged in at various times of the day. Sinc our working hours are from 08:00 to 17:00, I would like to the clock t start the next working day if the inquiry arrives after working hours. Can anyone suggest what formula I should use? Example of cell A1 content: 13/06/2005 14:05 Thanks Han -- Hani Muhtad ----------------------------------------------------------------------- Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2679 View this thread: http://www.excelforum.com/showthread.php?threadid=46722 Hi Hani Try =IF...

Copy Public Folders #2
I need to copy a ton of public folders and their sub folders to a single public folder on my exchange 2003 server. I once saw a program that would accomplish this for me. Does anyone know how to do this? Thanks PFmigrate, http://www.msexchange.org/tutorials/PFMIGRATE.html If you need to keep the same permissions for each folder you will also need PVADMIN, http://hellomate.typepad.com/exchange/2003/10/the_pfdavadmin_.html -- John Oliver, Jr MCSE, MCT, CCNA Exchange MVP 2006 Microsoft Certified Partner "Curious Joe" <joebob.johnson@gmail.com> wrote in message news:11...

CRM 3
HI all, i was unable using CRM 1.2 report in CRM 3. so, where i can find the guide for using custom crystal report in CRM 3? what software i should install. thank you, regards, Ng HI, all, it is possible add the crystal report crm 1.2 to CRM 3 report page? bec when i try using CRM3 Add new Report. i see the following msg Error Uploading Report An error occurred while trying to add the report to Microsoft CRM. Try adding the report again. If this problem continues, contact your system administrator. if can add, the crystal report crm 1.2 it is require re-mapping the fields? bec i...

Gridlines on Column Chart
Hello- I am trying to create a stacked column chart with gridlines that only appear for every 2 columns. How can I do this in Excel 2007? All I can find is how to insert a major gridline between every column. Thanks, Sean Hi, Format the X axis, on the Axis Options section set 'Interval between tickmarks' to 2. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "SeanF74" <SeanF74@discussions.microsoft.com> wrote in message news:E1BE2939-AB52-4FB1-8A0F-ACA9EFDB4CAD@microsoft.com... > Hello- > > I am trying to create a stacked ...

Report #2
Hi we have a different company and for each company we are using different report dictionary is it possible to use a single report dictionary all companies Regards Srinivasan Yes. -- Lyle U Srinivasan wrote: > > Hi we have a different company and for each company we are using > different report dictionary is it possible to use a single report > dictionary all companies > > Regards > Srinivasan OK, that was a bit too brief. Remember that security access to modified reports is specific to each company. Grant access to the modfied reports for each company where...

the picture is too large and will be truncated #2
Hi, When i try to select an entire row and push the copy button. I'm getting the following error: "the picture is too large and will be truncated" There are no pictures in the selection!!(?) I work with Excel 2002 on Win xp prof. Somebody has an idea thanxs in advance greets, Van Steenbergen Jan See Jon Peltier's site about this error http://www.geocities.com/jonpeltier/Excel/XL_PPT.html This is the only KB obout this error This is for 2000 but check it out http://support.microsoft.com/default.aspx?scid=kb;en-us;318265&Product=xlw2K XL2000: Error Message: The P...

insert number in column #2
I have a column with threedigits for telephone extensions. I want to insert a digit before the three throughout the column as the extension has been increased to 4 digits. Any easy way.../ Thanks. AFD One way: In a blank column (say column H), enter H1: =1 & A1 or, if you want them as numbers rather than text: H1: =1000+A1 copy down as far as necessary. Select the column and copy it. Select the original column and choose Edit/Paste Special/Values. In article <Om6vrJpEIHA.3548@TK2MSFTNGP06.phx.gbl>, "afdmello" <afdmello@hotmail.com> wrote: &...

Trouble scanning 3 of 9 #2
I have a client who is using 1"x1" labels to label some internal use products or products that come through the door without UPCs. We have set them up to use the 3 of 9 barcode format when there is no UPC available. The trouble is, the barcode is so condensed that the scanners at the POS can't read them. The line scanners in the receiving department read the barcodes without issue. Has anybody else had this problem? What can be done to remedy this? I have considered writing a simple program to automatically generate an internal use barcode as the UPC-A type scans without issue....

Variable Column Lookup
I have three columns to the left column K each has a header with the year number. 2008, 2009 and 2010. Underneath each of the three columns I have numerical data. On the same header row I have a drop down list in K1. So when I pick 2009, I want the calculations in K2, K3 downward to use the corresponding data in column that has the matching year. 2008 2009 2010 2009 (drop down) 2 3 6 3*10 If I choose the drop down of 2008 then the formula should be 2*10 If I choose the drop down of 2010 then the formula should be 6*10 The actual formula is much more complic...

Stacked Column Charting
Has anyone tried the following? From the data set below, I would like to have the data to the right of the Hrs. avail data be a stacked bar for each day. Along with this the Hrs. avail data would be a bar next to the remaing row of data. I tried placing the Hrs. avail on its own axis but that did not seem to work. In short I want to show the number of hours availabe in a day and actual hours used by the verious other data. Regards, Hal Day Hrs. avail EM's PM's Cal's Projects Other Idle 1 14 4 2.5 2 4 0 1.5 2 14 5 1 1 3 0 4 3 14 0 2 2 6 0 4 4 7 2 0 0 3 0 2 5 14 2 0 0 10 0...

excel 2004 column sorting
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I've read up a bit on others' sorting issues, but haven't come across exactly the same problem as mine. I used to be able to sort the info in one column and the corresponding info in the other columns would sort along with whichever column I chose at the first. Now, my corresponding columns don't sort along with my first sort, resulting in inaccurate info. Also, the workbook I am working with contains macros; the workbook contained macros when I first began using using it, i.e. when sorting was no prob....

Crystal 9.0 and CRM 1.2
Does anyone know if 1.2 supports Crstal Reports 9.0? Yes 1.2 supports crystal 9.0 <anonymous@discussions.microsoft.com> wrote in message news:03e101c3bf57$beeb0880$a101280a@phx.gbl... > Does anyone know if 1.2 supports Crstal Reports 9.0? ...

Help with counting user entered text values
Hi, Version: Excel 2000 Situation: In Column G, I will have testers entering in their initals. I would like to display the tester's initals in column H of another worksheet with the number of times that set of intals appeared in column G on column I of the other worksheet. The catch is that I do not have a list of the testers initals ahead of time, so I'll need to pull them from column H. Thanks for any and all help in advance, David Hi David, H10: DAL i10: =COUNTIF(G:G,H10) or if you don't want to see 0 if H10 is empty I10: =IF(H10="","",COU...