Total Row messing up count unique values (1 Viewer)

[steve1111]

Hello all,

I am sure this is a simple oversight but it is driving me crazy.

I have a query that counts the number of users following a project.

the table is: (with autonumber ID)
User-------Project
14-----------24
7 ----------24
2 ----------24
14 ----------24
if I create the query and select unique values I get what is expected:
14 - - - 24
7 - - - 24
2 - - - 24

But as soon as I add the total row and group by the Project and Count the User, it spits out 4 rather than the expected 3.

I have never had this issue before so I am not sure what is going on.

thanks

 

[plog]

But as soon as I add the total row and group by the Project and Count the User, it spits out 4 rather than the expected 3.

That doesn't exactly make sense. If you GROUP BY the Project and count the User field you should get 3 records, 2 of which will have counts of 1 and 1 with a count of 2 (User=14). So I don't exactly understand what the final result you want to achieve.

Using your sample data above, can you post what data you hope to ultimately end with? Don't explain it, show me what data you want.

 

[steve1111]

Hi Plog,

I would expect to see

CountOfUser
3

But I am getting 4. I have hidden the group by project and also tried using the Where for the users in the totals row. Without the totals row I get unique values, just not when aggregated.

 

[JHB]

Show the SQL-String.

 

[plog]

To get what you want, you need to build another query using the unique query you already have. Let's call your working unique query 'sub1'. Then to produce the results you want you would use this SQL:

SELECT COUNT(User) AS UniqueUsers FROM sub1

 

[steve1111]

Sounds good thanks for the help