Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

How to Count Unique using WHERE clause MS Access query

I am having trouble  figuring out how to get a query to return the count of unique account numbers in a particular table in my db.

The query first links two tables, 'time_code_ref_tbl' to 'SumEarnings_tbl' on field 'cycle_date'.  The purpose of this is to use a user defined 'time_code' as specified in a textbox on the main form in the db for the query criteria.

For example when '0209' is entered in the form's text box, I would like the query to return the count of unique account numbers for the two cycledates that correspond to 0209 as according to the 'time_code_ref_tbl'.  Here is some SQL I am statring with.

Any suggestions?

(It doesn't seem like putting in 'DISTINCT' does anything)

SELECT DISTINCT Count(SumEarnings2009.GroupAccount) AS CountOfGroupAccount
FROM SumEarnings2009 INNER JOIN TIME_CODE_REFERENCE_TABLE ON SumEarnings2009.CycleDate=TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE
WHERE (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=Forms![GEM to CMR ENTER DIFFERENCES FORM]!Time_Code_txt));
0
pgerman
Asked:
pgerman
  • 3
  • 2
1 Solution
 
gnovakhs2nCommented:
try like this:

select count(x.GroupAccount) as CountOfGroupAccount from (
SELECT DISTINCT SumEarnings2009.GroupAccount AS GroupAccount
FROM SumEarnings2009 INNER JOIN TIME_CODE_REFERENCE_TABLE ON SumEarnings2009.CycleDate=TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE
WHERE (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=Forms![GEM to CMR ENTER DIFFERENCES FORM]!Time_Code_txt)) as x
0
 
pgermanAuthor Commented:
I still can't get that to work, but I think I am just going to simplify things and break it up into two queries.  One that will return a unique list, the other to count from that query.

The end result will be the same to the user.

Thank you anyway for the quick response.

0
 
gnovakhs2nCommented:
thats exactly what this query does, probably in MS-Access Syntax you have to remove the "as" from "as x" at the end
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
pgermanAuthor Commented:
Its still giving me a 'Syntax error in the from clause'  
0
 
8080_DiverCommented:
I know this sounds too easy but, have you tried working in the non-SQL Design View of the Query Wizard?  
Create the query in the Query Wizard without trying to get the DISTINCT working (in other words, let it return all of the multpile instances).  Then right click to viw the properties of the query (or switch to the SQL view) and add the DISTINCT clause.
I understand that "real developers work with SQL and not GUI" but that's right up there with "real men don't cry" and "real men never, ever wear pink shirts". ;-)
0
 
gnovakhs2nCommented:
Oh, i see

a bracket problem ... try the snippet
select count(x.GroupAccount) as CountOfGroupAccount from 
(
SELECT DISTINCT SumEarnings2009.GroupAccount AS GroupAccount
FROM SumEarnings2009 INNER JOIN TIME_CODE_REFERENCE_TABLE ON SumEarnings2009.CycleDate=TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE
WHERE 
(((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=Forms![GEM to CMR ENTER DIFFERENCES FORM]!Time_Code_txt))
)  x 

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now