Help with a Query Please

B

Bob Vance

I want to sort Horse that has Invoicing (true)
But if the same Horse has a (False) no sort
Horse can appear Many times in this Column so only If they are all (same
Horse)True to Sort

Thanks in advance for any help with this......Bob
WindowsXP..MS Access 2007

SELECT tblHorseDetails.Horse, tblHorseDetails.Invoicing
FROM tblHorseDetails
GROUP BY tblHorseDetails.Horse, tblHorseDetails.Invoicing;
 
T

Tom van Stiphout

On Tue, 15 Apr 2008 12:58:38 +1200, "Bob Vance" <[email protected]>
wrote:

The word "sort" means something specific to database programmers, i.e.
"ranking in alphabetical order" such as done with the ORDER BY clause.

I think you meant "filter", which means "take a subset" such as done
with the WHERE clause.

If I understand correctly what you want, it is to only show those
horses with a perfect Invoicing record.

I don't have the table that you have, but this query should be pretty
close:

SELECT tblHorseDetails.Horse
FROM tblHorseDetails
WHERE Count(Horse) = Count(Invoicing)
GROUP BY tblHorseDetails.Horse

-Tom.
 
B

Bob Vance

Thanks Tom . I am getting this error:
"cannot have Aggregate in Where clause Count(Horse) = Count(Invoicing)
Regards Bob
 
T

Tom van Stiphout

Ah, sorry, move that to the HAVING clause.
Thanks Tom . I am getting this error:
"cannot have Aggregate in Where clause Count(Horse) = Count(Invoicing)
Regards Bob
Click to expand...
 
J

John W. Vinson/MVP

Bob Vance said:
Thanks Bob but that did not filter them either..........Regards Bob V
Click to expand...

Actually the Count() operator counts RECORDS, not "true" values - so that
query will return all records. Try

SELECT tblHorseDetails.Horse
FROM tblHorseDetails
GROUP BY tblHorseDetails.Horse
HAVING Count(*) = Sum(Abs(Invoicing));

This will compare the count of all records for a horse, with the the number
of True values for Invoicing; True is stored as -1 (+1 in SQL./Server),
False as 0 so summing the absolute value of Invoicing will give the number
of Trues.
 
B

Bob Vance

John , BRILLIANT :) :) Thank you very much...regards Bob
Can I introduce one more filter, Under another Table/field same HorseID ,
with the field misspell [Invocing] text Yes/No
Filter if text is [Yes]
SELECT tblHorseDetails.HorseID, tblHorseInfo.Invocing
FROM tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID
GROUP BY tblHorseDetails.HorseID, tblHorseInfo.Invocing
HAVING (((Count(*))=Sum(Abs([Invoicing]))));
 
P

Pieter Wijnen

"Better" SQL would be:

SELECT A.Horse
FROM tblHorseDetails A
WHERE Not Exists (SELECT 'X' FROM tblHorseInfo B
WHERE B.Horse=A.Horse
AND B.Invoicing=0)

HtH

Pieter
 
B

Bob Vance

Figured this one out from your query works great, does it look allright to
you!
Regards Bob
SELECT tblHorseDetails.HorseID, qryHorseNameActive.Name,
tblHorseInfo.Invocing
FROM (tblHorseDetails INNER JOIN qryHorseNameActive ON
tblHorseDetails.HorseID = qryHorseNameActive.HorseID) INNER JOIN
tblHorseInfo ON qryHorseNameActive.HorseID = tblHorseInfo.HorseID
GROUP BY tblHorseDetails.HorseID, qryHorseNameActive.Name,
tblHorseInfo.Invocing
HAVING (((tblHorseInfo.Invocing) Like "Yes") AND
((Count(*))=Sum(Abs([Invoicing]))));

"Pieter Wijnen"
 
J

John W. Vinson

Figured this one out from your query works great, does it look allright to
you!
Click to expand...

Could be a little better. The LIKE operator is used *only* for criteria
containing wildcards; if you don't have wildcards it works exactly the same as
the = operator, but slower. And you should use the HAVING clause only for
values generated by a Totals operation; criteria on table fields should be
done in the WHERE clause, which operates before the totals operations are
done. Try

SELECT tblHorseDetails.HorseID, qryHorseNameActive.Name,
tblHorseInfo.Invocing
FROM (tblHorseDetails INNER JOIN qryHorseNameActive ON
tblHorseDetails.HorseID = qryHorseNameActive.HorseID) INNER JOIN
tblHorseInfo ON qryHorseNameActive.HorseID = tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Invocing) ="Yes"))
GROUP BY tblHorseDetails.HorseID, qryHorseNameActive.Name,
tblHorseInfo.Invocing
HAVING Count(*)=Sum(Abs([Invoicing]));

One caution: you have both [Invocing] and [Invoicing]. Note the spelling
difference - I suspect only one is correct (and Invoicing is the English word,
he said invoking the dictionary).
 
Top