Solved

How to Count Unique using WHERE clause MS Access query

Posted on 2009-04-06
6
470 Views
Last Modified: 2013-11-29
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
Comment
Question by:pgerman
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:gnovakhs2n
Comment Utility
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
 

Author Comment

by:pgerman
Comment Utility
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
 
LVL 2

Expert Comment

by:gnovakhs2n
Comment Utility
thats exactly what this query does, probably in MS-Access Syntax you have to remove the "as" from "as x" at the end
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:pgerman
Comment Utility
Its still giving me a 'Syntax error in the from clause'  
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
 
LVL 2

Accepted Solution

by:
gnovakhs2n earned 500 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now