Dups

G

Guest

With a new query created from 4 other queries, I just want a count with no
duplicates.

Example: 4 queries all have a field called ProviderNo. the first query has
24 the second has 3 and so on...

All I want to do is count the Distinct ProviderNO.

I made a new query with all 4 queries and added DISTINCT to the SQL but that
did not work..

Help
 
M

[MVP] S.Clark

It seems odd that you're using 4 queries to do this, so maybe if you explain
more about the nature of the table structures and the queries, we can give a
more definate answer.

Typically, using GROUP BY will assist with gathering unique values.

e.g. Select Count(Providerid), ProviderCategory From tblStuff GROUP BY
ProviderCategory
 
G

Guest

The GROUP BY is fine with only one query, but I need all 4 queries..
Example: Query 1 has 44, Query 2 has 11, when I put them on the same Query I
get a total of 484... (44x11)..

Here is my situation:
I have 4 question (4 queries) that I asked Providers, some providers failed
1, 2,3 or all 4 questions.... I need to know how many providers failed
(could be failed 1, 2,3 or all 4 don't matter)...

It's easy to count how many questions were failed, but I need to know haw
many Providers failed..

So, I assumed I should put the 4 queries on one query and somehow merge and
identify the DISTINCT Providers...

Suggestions Please..
 
J

John Spencer (MVP)

You are looking for a UNION query. And then you can use that as the source of a
totals query. UNION queries cannot be created in the query grid, but must use
the SQL (text) window.
 
G

Guest

Your answer is more confusing than my question:
You say use a UNION Query, then a TOTALS Query, but use SQL - or use several
simple queries.
Did someone just invent TOTALS queries because none of my books mention them.
All I needed was help with putting a few queries onto one, and eliminating
the dups.
 
M

Michel Walsh

Hi,

SQL defines Total queries with the use of an aggregate, with or without
GROUP BY clause. An aggregate can be COUNT(*), COUNT(fieldName),
SUM(fieldName), MIN(fieldName), MAX(fieldName) , ... ,
DISTINCTCOUNT(fieldName) , ... A field name can be replaced by an
expression involving one or many fields or constants. Not all aggregate are
supported by all engines. JET does not support DISTINCTCOUNT, so you have to
mimic it like:


SELECT COUNT(*) , whatever
FROM (SELECT DISTINCT fieldName, whatever FROM myTable GROUP BY whatever)
GROUP BY whatever

which is equivalent to

SELECT DISTINCTCOUNT(fieldName), whatever
FROM myTable
GROUP BY whatever

(note that exact MS SQL Server syntax would be : SELECT COUNT(DISTINCT
fieldname), whatever FROM myTable GROUP BY whatever).

(note: reserved keyword are allcap, other words are user/designed names)

If you have no GROUP BY, the whole table is assumed to make *one* group:


SELECT COUNT(*)
FROM (SELECT DISTINCT fieldName FROM myTable)


is equivalent to

SELECT DISTINCTCOUNT(fieldName)
FROM mytable




A UNION query removes the duplicate, unless you use UNION ALL


SELECT fieldName, whatever FROM myTable
UNION
SELECT fieldName, whatever FROM myTable


is thus equivalent (as result, not as execution time) to

SELECT DISTINCT fieldName, whatever FROM myTable



A Total query is more versatile than a DISTINCT since it can differentiate
what can vary (the groups) and which field are to be considered as
containing duplicated data:


SELECT Monthly, LAST(employee)
FROM myTable
GROUP BY Monthly


which returns just one (more or less randomly selected) record *for each*
Monthly value (so, 12 records in all, if Monthly has possible integer values
from 1 to 12), but


SELECT LAST(Monthly), LAST(employee)
FROM myTable


returns just one record (more or less randomly).



Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer (MVP)

Sorry, I should have just said that we need more information on the structure of
your data. I tried instead to give you a range of solutions based on the range
of possible structures your data might have.

Totals Query is also known as an aggregate query or sometimes as a Group by
query. In Access, when you use the query grid to do an aggregate query the line
in the query grid that decides what aggregate action to perform is labeled: "Total".

I see that you have gotten another answer in this thread. I hope that works for
you. If not, post back and perhaps someone can see what you need to do and can
clearly give you instructions on how to reach your goal.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top