Help with couting records as 1 with query

[NimrodHunteR]

Hello all,

I am trying to get a query to count a group or same product as 1 record to give me total of customers using this product

example. i have a product coded "an" which has 154 customer using this product but when i use the count total for this product code in access i get "an" totaling 454. its almost as if it is counting some records multiple time almost a like a sum.

here is my query

SELECT RFGCostClass.Product, RFGCostClass.[Service Class], Count(SQLUser_LN_ACCT.col_cd) AS CountOfcol_cd
FROM (RFGCostClass INNER JOIN SQLUser_LN_ACCT ON RFGCostClass.Product = SQLUser_LN_ACCT.col_cd) INNER JOIN SQLUser_MEMBER_ACCT ON SQLUser_LN_ACCT.acn = SQLUser_MEMBER_ACCT.acn
GROUP BY RFGCostClass.Product, RFGCostClass.[Service Class], SQLUser_MEMBER_ACCT.acn_status
HAVING (((RFGCostClass.[Service Class]) Is Not Null) AND ((SQLUser_MEMBER_ACCT.acn_status)="Open"));

If you need anymore info to help me please let me know.

 

[Trevor G]

Welcome to the forum,

Have you looked at Setting this up in a Report and if you use the Report Wizard and also then Groupings Access will then give you some options which should resolve this for you.

 

[AConrad00]

Set up two queries

First Query, SELECT
Customer and Product Code - In properties, set Unique Values to Yes

Second Query, SELECT but click TOTALS
Product Code and Customer - Group by Product Code and then Set Customer to Count

I find it easier to split up stuff like this instead of trying hard to get it done with a single query, but i'm a newbie

 

[vbaInet]

example. i have a product coded "an" which has 154 customer using this product but when i use the count total for this product code in access i get "an" totaling 454. its almost as if it is counting some records multiple time almost a like a sum.

here is my query

SELECT RFGCostClass.Product, RFGCostClass.[Service Class], Count(SQLUser_LN_ACCT.col_cd) AS CountOfcol_cd
FROM (RFGCostClass INNER JOIN SQLUser_LN_ACCT ON RFGCostClass.Product = SQLUser_LN_ACCT.col_cd) INNER JOIN SQLUser_MEMBER_ACCT ON SQLUser_LN_ACCT.acn = SQLUser_MEMBER_ACCT.acn
GROUP BY RFGCostClass.Product, RFGCostClass.[Service Class], SQLUser_MEMBER_ACCT.acn_status
HAVING (((RFGCostClass.[Service Class]) Is Not Null) AND ((SQLUser_MEMBER_ACCT.acn_status)="Open"));

So it looks like you already know what the problem is. The multiple joins you have in your query is causing duplicate records to appear hence the unexpected results.

I would look at using a report like Trevor suggested. Or you can go with Conrad's suggestion of using the Distinct Values property but this should be last resort.