Odd Queries

[Dave Eyley]

I have 2 queries with the same source tables, fields and relationships.
The only difference between the two queries is that the criteria for one of the fields is -

=9, in one and,
<>9, in the other.

The problem is that in the query with the criteria is =9 the results show the GroupBy totals correctly, i.e. they show each unique main field and the sum of the criteria field in one row, but the other query displays all of the rows that made up the sum and not the sum itself.

But the only difference is the criteria...!!!!
I copied the bad one from the good one and only altered the criteria.
What am I doing wrong here?

Any help would be gratefully received...

Dave Eyley

 

[FoFa]

If the field that this criteria is based on is in your group by expression, then =9 would be a unique value (9), while <> 9 could be any value the datatype can hold, thus making the group by not unique.

But without seeing the SQL it is hard to tell if that is you problem or not.

 

[Dave Eyley]

Thanks Fofa,

I understand. Perhaps I'll break the query up and try again...

Dave Eyley

 

[Dave Eyley]

I tried unchecking the field but for the <>9 criteria it still shows the undesired result. I am currently reviewing my strategy.
I saw somewhere that's it's possible to subtract the contents of one table from another, so, if I produce a table of the results using the criteria (9) which works and then subtract the result from the main table I might end up with what I need...

Thanks for the help...

Dave Eyley

 

[neileg]

Can't duplicate your problem.

However, are you trying to apply the criteria to the underlying data or to the Sum? If the criterion applies to the sum, it won't return underlying records that have the field value of 9, only where the sum is 9.

If you want the sum of the records where the value is 9, you'll have to have the field twice in the query, once to apply the criteria and once to perform the sum.

Not sure if this helps.

 

[Dave Eyley]

Hi neileg,

The query is made up from 2 tables - property and space linked by a 1 to many.
In the query I have a selection of required fields together with the fields 'Area' and 'SpaceUse'. 'SpaceUse' is the criterion field and 'Area' is the Sum.
If I run the query with a single value criterion (e.g. 9) I get the sum of all the areas per property as required.
If I run the query with a <> 9 then I get all the areas separately. Pat and Fofa have explained why it happens but I'm still struggling to get a series of queries to get the end result.
Which is to have 2 tables with identical fields but one with all the records which fit the criterion and another with all the rest.

Hope this makes it clearer

Dave Eyley

 

[Bat17]

you need to change the Total row to 'Where' instead of 'GroupBy'

HTH

Peter

 

[neileg]

I would take SpaceUse out of the query and replace it with a calculated field that does the test and returns only one of two values.

NewField: iif([SpaceUse]=9,1,0)

Then use the criterion 1 or 0 to filter your records. This means that the records where SpaceUse is not 9 will all return the same value and eliminates problems of the extra level of grouping.

 

[Dave Eyley]

Thanks Bat17 and Neileg,

Both of your suggestions worked fine.
I can move on now...

Thanks again

Dave Eyley