count if or equivalent

[Guest]

I read a lot of responses and I'm not seeing what I'm looking for. I would
like to build a query to count how many times a certain name is included in
the table. I have a customer list that will include: Company Name, Contact
Name, BTN, Address, etc. I have a lot of records where the same contact name
might be used for several btn's. This is going to be used as a call-out
database for my sales team, and I'd like to be able to show them on my form
if this contact person is representing more than one location. I don't want
them to call someone, hang up, go to the next number and realize they're
going to have to call the same person again. I was thinking I could use a
sum(abs( but I don't want to have to type each contact persons name in " ".
That would be a lot of queries. I hope this makes sense, can anyone help?

 

[Guest]

If you are only worried about the number of times a client name appears in
the table, you can use:

select [contact name], count([contact name]) from [MyTable] group by
[contact name];

In the query grid, add your table, drag the 'contact name' field down to the
grid, select a 'Total Query' (summation sign on the Toolbar) and select Count
from the pull down in the Total row; drag 'contact name' down to the second
column of the grid, select 'Group By' in the Total row.

Good Luck!

 

[[MVP] S.Clark]

I would show the Contact name on a main form (Maybe not 'THE' main form
you're using now, thus you may need to create another), and all of their
BTN, or whatever data they are the multiple of.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Guest]

I made a separate query that counted the number of times the contact name is
included in the table. I then went back to my originial query, added the new
query above the query grid, "linked" contact name to contact name, and then
added the count column to the end of the grid. This works great for the
query, however, when I open up the form that runs off this query, I can't
change any of the data. An example is when I try to choose an option in one
of my pull down lists, all I hear is a sound, it won't allow me to make a
choice. Everything on the form seems locked down now.

If you are only worried about the number of times a client name appears in
the table, you can use:

select [contact name], count([contact name]) from [MyTable] group by
[contact name];

In the query grid, add your table, drag the 'contact name' field down to the
grid, select a 'Total Query' (summation sign on the Toolbar) and select Count
from the pull down in the Total row; drag 'contact name' down to the second
column of the grid, select 'Group By' in the Total row.

Good Luck!
--
Chaim

I read a lot of responses and I'm not seeing what I'm looking for. I would
like to build a query to count how many times a certain name is included in
the table. I have a customer list that will include: Company Name, Contact
Name, BTN, Address, etc. I have a lot of records where the same contact name
might be used for several btn's. This is going to be used as a call-out
database for my sales team, and I'd like to be able to show them on my form
if this contact person is representing more than one location. I don't want
them to call someone, hang up, go to the next number and realize they're
going to have to call the same person again. I was thinking I could use a
sum(abs( but I don't want to have to type each contact persons name in " ".
That would be a lot of queries. I hope this makes sense, can anyone help?