query for data >4 times

[buckpeace]

I have a table that has member ID's in it. Some are in it 1 times, some 2,
some 3, 4 etc......I need to write a query to pull out member id's that are
in the table 4 times. The field is called "member id". I am having a
lot of trouble pulling out members that are in the table 4 times.....I dont
wnat to do this manual!!!

 

[Dirk Goldgar]

I have a table that has member ID's in it. Some are in it 1 times,
some 2, some 3, 4 etc......I need to write a query to pull out member
id's that are in the table 4 times. The field is called "member
id". I am having a lot of trouble pulling out members that are
in the table 4 times.....I dont wnat to do this manual!!!

Use this SQL as a model:

SELECT [Member ID], Count([Member ID]) AS HowMany
FROM YourTableNameHere
GROUP BY [Member ID]
HAVING Count([Member ID]) >= 4;

 

[Wayne-I-M]

Hi Buck

I would use a query

To select "only" the members with 4 ID's and use something like this (change
YourTable to whatever your table is called)

Select memberID, Count(memberID) FROM YourTable Group By memberID WHERE
Count(Email) =4

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _

To select all the members with "4 or more" ID's
Select memberID, Count(memberID) FROM YourTable Group By memberID WHERE
Count(Email) >3

 

[buckpeace]

Wayne,

I am not sure I understand where the language of -- "Select memberID,
Count(memberID) FROM YourTable Group By memberID WHERE
Count(Email) =4 "

If I am putting this on the CRITERIA section of an access database.....is
there another way to write it other than SQL??? By the way....the UK is
easily one of my Favorite places on earth - Especially Scotland.

Buck

Buck

 

[buckpeace]

Dirk,

Actually, I am not putting htis in SQL. It is in the criteria field of the
Access Query....I am not understnadfing how to write the query from your
response.....Is there anyway you can write it as it shoudl be on the cirteria
part of DESIGN VIEW on a query????? I really appreciate your help.

Buck
--
Buck

I have a table that has member ID's in it. Some are in it 1 times,
some 2, some 3, 4 etc......I need to write a query to pull out member
id's that are in the table 4 times. The field is called "member
id". I am having a lot of trouble pulling out members that are
in the table 4 times.....I dont wnat to do this manual!!!

Use this SQL as a model:

SELECT [Member ID], Count([Member ID]) AS HowMany
FROM YourTableNameHere
GROUP BY [Member ID]
HAVING Count([Member ID]) >= 4;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Actually, I am not putting htis in SQL. It is in the criteria field
of the Access Query....I am not understnadfing how to write the query
from your response.....Is there anyway you can write it as it shoudl
be on the cirteria part of DESIGN VIEW on a query????? I really
appreciate your help.

The easiest way to see this is to switch the query to SQL View, paste in
the SQL (fixing up the table and field names as needed to match yours),
and then switch back to Design View. Voilá.

Note that this query will give you the member IDs. It will not return
all the records for those members; for that you need a more elaborate
query.

 

[Dirk Goldgar]

Hi Buck

I would use a query

To select "only" the members with 4 ID's and use something like this
(change YourTable to whatever your table is called)

Select memberID, Count(memberID) FROM YourTable Group By memberID
WHERE Count(Email) =4

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _

To select all the members with "4 or more" ID's
Select memberID, Count(memberID) FROM YourTable Group By memberID
WHERE Count(Email) >3

Note that you need HAVING, not WHERE, in the SQL above, and substitute
"memberID" (or whatever the field name really is) for "Email".

 

[Wayne-I-M]

Hi Dirk

I agree with you about the e mail / member ID thing (too busy doing 2 things
at once, as my wife keeps telling e <

But the HAVING and not WHERE ???

Can you explain this as I always like to see where I have gone wrong. I
just loaded a few fields into a trail table and then used the WHERE in a
query and it seems to work. BUT I have learnt to trust the advice of MVP's so
if you could give me a hint I would appreciate it.

Thanks




--
Wayne
Manchester, England.
2 things to remember:
Enjoy whatever it is you do and the F1 Key doesn’t bite.

 

[Dirk Goldgar]

Hi Dirk

I agree with you about the e mail / member ID thing (too busy doing 2
things at once, as my wife keeps telling e <

But the HAVING and not WHERE ???

Can you explain this as I always like to see where I have gone wrong.
I just loaded a few fields into a trail table and then used the WHERE
in a query and it seems to work. BUT I have learnt to trust the
advice of MVP's so if you could give me a hint I would appreciate it.

Sure. Let's see the SQL of the query that "seems to work". I say that
any criteria based on an aggregate function must be applied in a HAVING
clause, not a WHERE clause. A WHERE clause may be used to filter the
records before the grouping takes place, but it can't contain an
aggregate function such as Count() or Sum(). If I paste your proposed
SQL into the SQL View of a new query, I can't even switch to Design
View, because it tells me, "Syntax error (missing operator) in query
expression 'memberID WHERE Count(Email) =4'.

Did you build your query in Design View? If so, and you simply entered
your criterion on the Criteria: line after making it a Totals query, you
will have gotten a HAVING clause, not a WHERE clause. To get a WHERE
clause in a Totals query, you have to select "Where" on the Totals:
line, as well as put the criterion on the Criteria: line.

I don't see how anything like the SQL you posted could work. It's
always possible that I'm missing something, though, so post your working
SQL and let me see what you've done.