Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to Count Unique using WHERE clause MS Access query

Posted on 2009-04-06
6
Medium Priority
?
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:gnovakhs2n
ID: 24078204
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
ID: 24078370
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
ID: 24078399
thats exactly what this query does, probably in MS-Access Syntax you have to remove the "as" from "as x" at the end
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

Expert Comment

by:8080_Diver
ID: 24078773
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 2000 total points
ID: 24078783
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

636 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