Query Count Conditions

[Chris Monner via AccessMonster.com]

I have a rather odd query to do. Well, it's odd for me.

I need to do a query based on the following conditions.

Only display records and Count those records - Awards where the award name
is 4 or 5
--This works Fine.

Now I need to expand on the query. I need to include the following
condition Only display the awards where the name is 4 or 5, but if they
have an award 1 do not display them. Basically a candiadate gets an award
category "1" after 5 category "4" or "5" awards. So I wanted a query (Not
a report) if possible that will give me all candidates that have 5 or more
category 4 or 5 awards and not yet possess a category 1 award. So it will
list the name of the person and a count of how many awards they have. And
that's it. Clear? I hope so.

Help

 

[MGFoster]

I have a rather odd query to do. Well, it's odd for me.

I need to do a query based on the following conditions.

Only display records and Count those records - Awards where the award name
is 4 or 5
--This works Fine.

Now I need to expand on the query. I need to include the following
condition Only display the awards where the name is 4 or 5, but if they
have an award 1 do not display them. Basically a candiadate gets an award
category "1" after 5 category "4" or "5" awards. So I wanted a query (Not
a report) if possible that will give me all candidates that have 5 or more
category 4 or 5 awards and not yet possess a category 1 award. So it will
list the name of the person and a count of how many awards they have. And
that's it. Clear? I hope so.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure, but try this:

SELECT candidate, award_category, Count(*) As CountOfAwards
FROM table_name As t
WHERE NOT EXISTS (SELECT * FROM table_name
WHERE candidate = t.candidate
AND award_category = 1)
AND award_category In (4,5)
GROUP BY candidate, award_category
HAVING Count(*) >= 5

Substitute your table/column names where appropriate.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQh+D/4echKqOuFEgEQJS4ACgk/6gLmd9n7cByA++jIeyjh0lUOEAn1BT
eEyi89a2Tad8h5GjzCzyAE8R
=/2TF
-----END PGP SIGNATURE-----

 

[Chris Monner via AccessMonster.com]

I obtain a syntax error when I enter this. I am not too familiar with the
SQL builder so it could be how I put it together. Is there a way to do
this through Design Mode

 

[MGFoster]

I obtain a syntax error when I enter this. I am not too familiar with the
SQL builder so it could be how I put it together. Is there a way to do
this through Design Mode

What's the error?

Show us the structure of the table(s) (table & column names & data
types) you are using and the SQL of the query.

 

[Chris Monner via AccessMonster.com]

BEFORE:

SELECT Recognition.EmployeeID, Count([Recognition Details].[Award Name]) AS
[CountOfAward Name]
FROM Recognition INNER JOIN [Recognition Details] ON
Recognition.RecognitionID = [Recognition Details].RecognitionID
WHERE ((([Recognition Details].[Award Name])="4" Or ([Recognition Details].
[Award Name])="5"))
GROUP BY Recognition.EmployeeID
ORDER BY Recognition.EmployeeID;

AFTER

My After I know is completely wrong as I have no idea if I am supposed to
put candidate or if this applies to an existing table and I am not sure
what the "table_name As t" is supposed to signify and what I am supposed to
substitute. Real big newbie here to code like this. I hope my before SQL
code gives enough information for you. Thanks for your help in advance.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

table_name as t meant: "table_name" was were you were supposed to
substitute the name of the table you were using in the query. "As t" is
an alias of the table name - makes the query easier to read.

The below is just a guess, since you didn't tell me the structure of
your tables.

SELECT R.EmployeeID, D.[Award Name], Count(*) As CountOfAwards
FROM Recognition As R INNER JOIN [Recognition Details] AS D
ON R.RecognitionID = D.RecongnitionID
WHERE NOT EXISTS (SELECT * FROM [Recognition Details] As D1
INNER JOIN Recognition AS R1
ON D1.RecognitionID = R1.RecognitionID
WHERE R1.EmployeeID = R.EmployeeID
AND D1.[Award Name] = "1")
AND D.[Award Name] In ("4","5")
GROUP BY R.EmployeeID, D.[Award Name]
HAVING Count(*) > 4

Why do you have numbers as the values for a column named "Award Name"?
Shouldn't the column name be "Award Number"? If using numbers you
should store them as numeric data types, not strings (the quotes around
the numbers tells me that Access "thinks" the [Award Name] column is a
string data type).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiS9zoechKqOuFEgEQJMnACgu94JzbkBOwFRyT6VDEjEm3hz6koAoIS3
3mJm1G9AUAwb3PsBM+U8RQKl
=cGay
-----END PGP SIGNATURE-----