Please, What is wrong with this Query?

C

CEV

Hi, I created a query with the help of this group. The query is no longer
working. It runs and comes up with some numbers, but I do not know where
these numbers are coming from because I believe they are incorrect. Here is
the query:

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions INNER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
GROUP BY tblPositions.Department, tblPositions.Inactive
HAVING (((Sum(Abs([Open])))=Yes) AND ((tblPositions.Inactive)=No));

The query is supposed to count how many "Open" check boxes are marked in the
tblPositions table. It totals the number up per department. It only counts
records where the "Inactive" check box is not checked.

The tblPosition table is only showing 30 positions marked as Open but the
Query comes up with a total count of 50. None of the 30 are marked as
Inactive. There are 9 records that are marked as Inactive but none of these
arte marked as Open.

I have no ideal why the query comes up with the number 50. Can you see
anything wrong with the Query above?

Thanks,

CEV
 
S

strive4peace

I think this may be a problem...

HAVING (((Sum(Abs([Open])))=Yes)


Instead of using HAVING, which is applied after the dynaset is
generated, try using WHERE, which is applied before the records are
gathered up

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions INNER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
WHERE ([Open] = true)
GROUP BY tblPositions.Department, tblPositions.Inactive
HAVING (tblPositions.Inactive)=No);

you could also move your other criteria to where...

you can also count boolean values like this:

Sum(IIF([Open],1,0)) AS CountOfYes,
Sum(IIF([Open],0,1)) AS CountOfNo


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
C

CEV

Thank You for your response. I tried that and it came up with the same
results. I came in early today so I figured I would give it another shot at
trying to figure out how it was coming up with these numbers. I finally got
it. The tblEmployeePositions table contains records for all positions an
employee held and also all the employees that a position has ever had.
Therefore if there was a position that had 4 employees over a period of
time, it was counting that position number for each record in that table.
After I removed that table from the query it then calculated the correct
numbers. I do not know why I even had that table in there. Thanks for the
help.

CEV

strive4peace said:
I think this may be a problem...

HAVING (((Sum(Abs([Open])))=Yes)


Instead of using HAVING, which is applied after the dynaset is generated,
try using WHERE, which is applied before the records are gathered up

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions INNER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
WHERE ([Open] = true)
GROUP BY tblPositions.Department, tblPositions.Inactive
HAVING (tblPositions.Inactive)=No);

you could also move your other criteria to where...

you can also count boolean values like this:

Sum(IIF([Open],1,0)) AS CountOfYes,
Sum(IIF([Open],0,1)) AS CountOfNo


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi, I created a query with the help of this group. The query is no longer
working. It runs and comes up with some numbers, but I do not know where
these numbers are coming from because I believe they are incorrect. Here
is the query:

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions INNER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
GROUP BY tblPositions.Department, tblPositions.Inactive
HAVING (((Sum(Abs([Open])))=Yes) AND ((tblPositions.Inactive)=No));

The query is supposed to count how many "Open" check boxes are marked in
the tblPositions table. It totals the number up per department. It only
counts records where the "Inactive" check box is not checked.

The tblPosition table is only showing 30 positions marked as Open but the
Query comes up with a total count of 50. None of the 30 are marked as
Inactive. There are 9 records that are marked as Inactive but none of
these arte marked as Open.

I have no ideal why the query comes up with the number 50. Can you see
anything wrong with the Query above?

Thanks,

CEV
Click to expand...
Click to expand...
 
C

Charles Wang[MSFT]

Hi,
This issue is also probab caused by the INNER JOIN relationship.
Try the following:
SELECT tblPositions.Department, Sum(Abs([tblPositions] .[Open])) AS
CountOfYes,
tblPositions.Inactive
FROM tblPositions LEFT OUTER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
WHERE ([tblPositions] .[Open] = true) AND (tblPositions.Inactive=No)
GROUP BY tblPositions.Department, tblPositions.Inactive;

If this issue persists, could you mail me ([email protected]) a test
database file for further research?

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
C

CEV

What I did was use the following and it worked:

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive

FROM tblPositions

WHERE (((tblPositions.Open)=Yes))

GROUP BY tblPositions.Department, tblPositions.Inactive

HAVING (((tblPositions.Inactive)=No));



I am still trying to figure out the SQL language as it is very hard to
understand. Am I correct in that you could do it two different ways and
still come up with the same result? Because our two queries are not the
same.



Thanks,



CEV
 
M

Michel Walsh

Hi,


The WHERE clause is evaluated BEFORE any aggregate, any grouping. The HAVING
clause is applied after aggregation (SUM, MIN, MAX, COUNT,... ) and groups
made up.

You ask:
It totals the number up per department. It only counts records where the
"Inactive" check box is not checked
Click to expand...


so, basically, you have to look if the check box Inactive before counting,
so, in the WHERE clause:


SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions INNER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber

WHERE ((tblPositions.Inactive)=No)

GROUP BY tblPositions.Department, tblPositions.Inactive



the test on:

((Sum(Abs([Open])))=Yes)


just remove the groups where there would be 0 as count of yes. If you really
want it, it goes in the HAVING clause (since the SUM has to be made, to be
evaluated, it CANNOT be in the WHERE clause).



Hoping it may help,
Vanderghast, Access MVP


CEV said:
Hi, I created a query with the help of this group. The query is no longer
working. It runs and comes up with some numbers, but I do not know where
these numbers are coming from because I believe they are incorrect. Here
is the query:

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive
FROM tblPositions INNER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
GROUP BY tblPositions.Department, tblPositions.Inactive
HAVING (((Sum(Abs([Open])))=Yes) AND ((tblPositions.Inactive)=No));

The query is supposed to count how many "Open" check boxes are marked in
the tblPositions table. It totals the number up per department. It only
counts records where the "Inactive" check box is not checked.

The tblPosition table is only showing 30 positions marked as Open but the
Query comes up with a total count of 50. None of the 30 are marked as
Inactive. There are 9 records that are marked as Inactive but none of
these arte marked as Open.

I have no ideal why the query comes up with the number 50. Can you see
anything wrong with the Query above?

Thanks,

CEV
Click to expand...
 
S

strive4peace

I would even recommend not showing Inactive (since you are filtering on
it, you don't need to show it) -- you can move its criteria to WHERE also.

WHERE is criteria for the source recordsets of your query

HAVING is applied after the records you want are created -- and then, it
is too late to filter on something that might not show on the results.

BASIC SQL SYNTAX

SELECT fieldlist
FROM tablename
IN anotherdatabase.mdb
WHERE conditions
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

The order of the keywords in the SQL is the order that things happen...

furthermore, it is better to use inequalities for comparison than equalities

so you could skip HAVING and simply do this:

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes
FROM tblPositions
WHERE ((tblPositions.Open <> No) AND (tblPositions.Inactive <> Yes))
GROUP BY tblPositions.Department

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


What I did was use the following and it worked:

SELECT tblPositions.Department, Sum(Abs([Open])) AS CountOfYes,
tblPositions.Inactive

FROM tblPositions

WHERE (((tblPositions.Open)=Yes))

GROUP BY tblPositions.Department, tblPositions.Inactive

HAVING (((tblPositions.Inactive)=No));



I am still trying to figure out the SQL language as it is very hard to
understand. Am I correct in that you could do it two different ways and
still come up with the same result? Because our two queries are not the
same.



Thanks,



CEV


Charles Wang said:
Hi,
This issue is also probab caused by the INNER JOIN relationship.
Try the following:
SELECT tblPositions.Department, Sum(Abs([tblPositions] .[Open])) AS
CountOfYes,
tblPositions.Inactive
FROM tblPositions LEFT OUTER JOIN tblEmployeePositions ON
tblPositions.PositionNumber = tblEmployeePositions.PositionNumber
WHERE ([tblPositions] .[Open] = true) AND (tblPositions.Inactive=No)
GROUP BY tblPositions.Department, tblPositions.Inactive;

If this issue persists, could you mail me ([email protected]) a test
database file for further research?

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
======================================================
Click to expand...
Click to expand...
 

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

Similar Threads

Showing most recent record for each employee in a query 2
Show just one record based on date 6
Creating Turnover Report based on Query 7
Add Fields to a query that are not included in an aggregate functi 3
What is wrong with this query? 3
SUM in a UNION query 2
Update Query Question 1
How to convert this to an Access query? 8

Top