"Where" column limits records regardless of row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've noticed that when I create a totals query and have "Where" in the totals
cell, Access will use "AND" to combine the criteria in the "Where" column
with the criteria in other columns - even when they are in different rows!
Everything I've read seems to say that if criteria are on different rows, the
criteria will be combined with an "Or" instead.

Could someone please explain what is going on? This is driving me insane!

Thanks!
 
Do any of these columns involve nulls?

Or are they from different tables with outer joins, so that a Where on the
related table limits the query to records where there is some value in the
related table?

If neither of those issues apply, post the SQL statement (SQL View on View
menu, from query design window).
 
Thanks for your help.

I don't believe either of those situations apply. The two tables have an
inner join and none of the four fields used have any null values.

Here is the SQL statement:

SELECT Sum([2004 Cost By Class].[Invoice Amount]) AS [SumOfInvoice Amount],
[2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT DATABASE].OM

FROM [qry SAME STORES IN IPT DATABASE] INNER JOIN [2004 Cost By Class] ON
[qry SAME STORES IN IPT DATABASE].[SITE ID] = [2004 Cost By Class].[Store
Number]

WHERE ((([2004 Cost By Class].[Problem Description]) Like "hand*"))
GROUP BY [2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT
DATABASE].OM
HAVING ((([qry SAME STORES IN IPT DATABASE].OM)=710)) OR ((([qry SAME STORES
IN IPT DATABASE].OM)=711));
 
Hmm. A bit hard to tell, as this query is stacked on top of another query.

You will notice that the "OR" is in the HAVING clause, not the WHERE clause.
The HAVING clause is applied after the data is aggregated.
Try dragging the OM field into the grid a 2nd time.
Uncheck the "Show" box under this 2nd instance.
Remove the criteria from the 1st instance, and place under the 2nd.

I suggest you use criteria of:
710 OR 711
since that avoids the problem where you also need that AND'd with the
criteria for the [Problem Description] field.

Does that work?

In any case the issues we discussed previously mean that these records will
not be returned:
- any record that has nothing in the [Problem Description] field;
- any record that has nothing in the OM field.
- any record that has nothing in the [Site ID] field.
- any record that has nothing in the [Store Number] field.
- any record where the [Store Number] does not match a [Site ID] field
returned by the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jon5001 said:
Thanks for your help.

I don't believe either of those situations apply. The two tables have an
inner join and none of the four fields used have any null values.

Here is the SQL statement:

SELECT Sum([2004 Cost By Class].[Invoice Amount]) AS [SumOfInvoice
Amount],
[2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT DATABASE].OM

FROM [qry SAME STORES IN IPT DATABASE] INNER JOIN [2004 Cost By Class] ON
[qry SAME STORES IN IPT DATABASE].[SITE ID] = [2004 Cost By Class].[Store
Number]

WHERE ((([2004 Cost By Class].[Problem Description]) Like "hand*"))
GROUP BY [2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT
DATABASE].OM
HAVING ((([qry SAME STORES IN IPT DATABASE].OM)=710)) OR ((([qry SAME
STORES
IN IPT DATABASE].OM)=711));

Allen Browne said:
Do any of these columns involve nulls?

Or are they from different tables with outer joins, so that a Where on
the
related table limits the query to records where there is some value in
the
related table?

If neither of those issues apply, post the SQL statement (SQL View on
View
menu, from query design window).
Click to expand...
Click to expand...
 
I did as you suggested and it gave me the same result as before.

I think I may understand what was causing my previous confusion. Please let
me know if this is correct: Any criteria placed in a column with GROUP BY (or
any of the aggregrate functions) will fall under the HAVING clause in SQL,
whereas criteria in a column with WHERE will fall under the WHERE clause in
SQL. Hence, when working in the design grid, it doesn't matter which row
criteria under a GROUP BY column is in relation to criteria under a WHERE
column is and vice versa. However, If criteria are both under the same type
of clause (where or group by) they will be AND'd and OR'd as explained in
introductory Access texts.

Is that correct?

Allen Browne said:
Hmm. A bit hard to tell, as this query is stacked on top of another query.

You will notice that the "OR" is in the HAVING clause, not the WHERE clause.
The HAVING clause is applied after the data is aggregated.
Try dragging the OM field into the grid a 2nd time.
Uncheck the "Show" box under this 2nd instance.
Remove the criteria from the 1st instance, and place under the 2nd.

I suggest you use criteria of:
710 OR 711
since that avoids the problem where you also need that AND'd with the
criteria for the [Problem Description] field.

Does that work?

In any case the issues we discussed previously mean that these records will
not be returned:
- any record that has nothing in the [Problem Description] field;
- any record that has nothing in the OM field.
- any record that has nothing in the [Site ID] field.
- any record that has nothing in the [Store Number] field.
- any record where the [Store Number] does not match a [Site ID] field
returned by the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jon5001 said:
Thanks for your help.

I don't believe either of those situations apply. The two tables have an
inner join and none of the four fields used have any null values.

Here is the SQL statement:

SELECT Sum([2004 Cost By Class].[Invoice Amount]) AS [SumOfInvoice
Amount],
[2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT DATABASE].OM

FROM [qry SAME STORES IN IPT DATABASE] INNER JOIN [2004 Cost By Class] ON
[qry SAME STORES IN IPT DATABASE].[SITE ID] = [2004 Cost By Class].[Store
Number]

WHERE ((([2004 Cost By Class].[Problem Description]) Like "hand*"))
GROUP BY [2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT
DATABASE].OM
HAVING ((([qry SAME STORES IN IPT DATABASE].OM)=710)) OR ((([qry SAME
STORES
IN IPT DATABASE].OM)=711));

Allen Browne said:
Do any of these columns involve nulls?

Or are they from different tables with outer joins, so that a Where on
the
related table limits the query to records where there is some value in
the
related table?

If neither of those issues apply, post the SQL statement (SQL View on
View
menu, from query design window).


I've noticed that when I create a totals query and have "Where" in the
totals
cell, Access will use "AND" to combine the criteria in the "Where"
column
with the criteria in other columns - even when they are in different
rows!
Everything I've read seems to say that if criteria are on different
rows,
the
criteria will be combined with an "Or" instead.

Could someone please explain what is going on? This is driving me
insane!

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Yes, I think you have that correct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jon5001 said:
I did as you suggested and it gave me the same result as before.

I think I may understand what was causing my previous confusion. Please
let
me know if this is correct: Any criteria placed in a column with GROUP BY
(or
any of the aggregrate functions) will fall under the HAVING clause in SQL,
whereas criteria in a column with WHERE will fall under the WHERE clause
in
SQL. Hence, when working in the design grid, it doesn't matter which row
criteria under a GROUP BY column is in relation to criteria under a WHERE
column is and vice versa. However, If criteria are both under the same
type
of clause (where or group by) they will be AND'd and OR'd as explained in
introductory Access texts.

Is that correct?

Allen Browne said:
Hmm. A bit hard to tell, as this query is stacked on top of another
query.

You will notice that the "OR" is in the HAVING clause, not the WHERE
clause.
The HAVING clause is applied after the data is aggregated.
Try dragging the OM field into the grid a 2nd time.
Uncheck the "Show" box under this 2nd instance.
Remove the criteria from the 1st instance, and place under the 2nd.

I suggest you use criteria of:
710 OR 711
since that avoids the problem where you also need that AND'd with the
criteria for the [Problem Description] field.

Does that work?

In any case the issues we discussed previously mean that these records
will
not be returned:
- any record that has nothing in the [Problem Description] field;
- any record that has nothing in the OM field.
- any record that has nothing in the [Site ID] field.
- any record that has nothing in the [Store Number] field.
- any record where the [Store Number] does not match a [Site ID] field
returned by the query.


Jon5001 said:
Thanks for your help.

I don't believe either of those situations apply. The two tables have
an
inner join and none of the four fields used have any null values.

Here is the SQL statement:

SELECT Sum([2004 Cost By Class].[Invoice Amount]) AS [SumOfInvoice
Amount],
[2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT
DATABASE].OM

FROM [qry SAME STORES IN IPT DATABASE] INNER JOIN [2004 Cost By Class]
ON
[qry SAME STORES IN IPT DATABASE].[SITE ID] = [2004 Cost By
Class].[Store
Number]

WHERE ((([2004 Cost By Class].[Problem Description]) Like "hand*"))
GROUP BY [2004 Cost By Class].[Store Number], [qry SAME STORES IN IPT
DATABASE].OM
HAVING ((([qry SAME STORES IN IPT DATABASE].OM)=710)) OR ((([qry SAME
STORES
IN IPT DATABASE].OM)=711));

:

Do any of these columns involve nulls?

Or are they from different tables with outer joins, so that a Where on
the
related table limits the query to records where there is some value in
the
related table?

If neither of those issues apply, post the SQL statement (SQL View on
View
menu, from query design window).


I've noticed that when I create a totals query and have "Where" in
the
totals
cell, Access will use "AND" to combine the criteria in the "Where"
column
with the criteria in other columns - even when they are in different
rows!
Everything I've read seems to say that if criteria are on different
rows,
the
criteria will be combined with an "Or" instead.

Could someone please explain what is going on? This is driving me
insane!

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top