Hi,

I am having trouble trying to figure out how to build two queries:

One that lists only clientids that exist in the reservation table just
once.

And a query that lists clientids in the reservation table that exist
more than once.

Any ideas?

Thanks,

-paulw

SELECT *
FROM tblReservation
GROUP BY ClientID
HAVING (((Count(ClientID))=1));

SELECT *
FROM tblReservation
GROUP BY ClientID
HAVING (((Count(ClientID))>1));

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://access.mvps.org
Co-author: "Access Solutions", published by Wiley



"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

SELECT reservation .[ClientID], Count(reservation .[ClientID]) AS
[CountOfClientID]
FROM reservation
GROUP BY reservation .[ClientID]
HAVING (((Count(reservation .[ClientID]))>1));

SELECT reservation .[ClientID], Count(reservation .[ClientID]) AS
[CountOfClientID]
FROM reservation
GROUP BY reservation .[ClientID]
HAVING (((Count(reservation .[ClientID]))=1));


"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

On Thu, 1 Sep 2011 17:13:31 -0400, "Arvin Meyer" <arvinm (AT) invalid (DOT) org>
wrote:


Thank you both! I totally forgot about the HAVING clause and also
count.

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:f13067l2tamb6hh01m6s1fd2ku1fpf21hd (AT) 4ax (DOT) com:

If you'd write your queries in the Access query designer, it will
use HAVING any time you put criteria under an aggregated field
(unless you specifically choose WHERE from the dropdown list).

I am really convinced that most people don't use the QBE enough --
it makes many, many things MUCH easier...

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Sat, 3 Sep 2011 20:35:20 +0000 (UTC), "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:

David. I guess I am old fashioned but I do realize the power of the MS
Access query designer. My wife is very good at it but I keep trying to
do things in code I suppose. At least when I can't figure it out!

Thanks so much.

-paul

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:qru5675v0re4osnmhktvjcaure7rlg7j6e (AT) 4ax (DOT) com:

Avail yourself of the assistance Access provides! Don't write your
SQL by hand!

I usually start my SQL in the query designer when I'm going to use
it in code. I especially avoid writing JOINs by hand, since the
Access syntax is so picky about parens and order.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/