How do you count records in Access?

[Stavrogin]

Hi,

I am working with a database mailing list of about 10,000 names. One
field in this list specifies whether a person is a "Prospect" or a
"Customer".

So my first question is... is there anyway I can count up the number of
records that are listed as "customer"? I would like to create another
table/query where I can easily know the number of people that are
listed as "customers"?


Now for my second question. Let's say of these 10,000 people, 5,000
are male and 5,000 are female. In addition, let's say 3,000 live in
New York, 2,000 live in New Jersey and 5,000 live in Boston. Is there
anyway I can award a numerical value to this variables and then add
them up? In other words, can I give the variable "female" 2 points and
"male" 1 point and also give the variable "New York" 5 points, "New
Jersey" 3 points and "Boston" 1 point? The end I have in mind is to be
able to determine that some prospects rank as a 7 (are female and from
New York) while others might only rank as a 4 (are male and from New
Jersey).

Any ideas?

Thanks in advance for any help you can provide.

 

[Dirk Goldgar]

Hi,

I am working with a database mailing list of about 10,000 names. One
field in this list specifies whether a person is a "Prospect" or a
"Customer".

So my first question is... is there anyway I can count up the number
of records that are listed as "customer"? I would like to create
another table/query where I can easily know the number of people that
are listed as "customers"?


Now for my second question. Let's say of these 10,000 people, 5,000
are male and 5,000 are female. In addition, let's say 3,000 live in
New York, 2,000 live in New Jersey and 5,000 live in Boston. Is there
anyway I can award a numerical value to this variables and then add
them up? In other words, can I give the variable "female" 2 points
and "male" 1 point and also give the variable "New York" 5 points,
"New Jersey" 3 points and "Boston" 1 point? The end I have in mind
is to be able to determine that some prospects rank as a 7 (are
female and from New York) while others might only rank as a 4 (are
male and from New Jersey).

Any ideas?

Thanks in advance for any help you can provide.

The easiest way for simple counts is to use queries that employ the
Count function. For example:

How many of the records in table Contacts are customers?

SELECT Count(*) FROM Contacts
WHERE ContactType = 'Customer'

Let's get a breakdown of the records by contact type.

SELECT ContactType, Count(*) As HowMany
FROM Contacts
GROUP BY ContactType

To implement your point system -- in an inflexible way -- you could
write a query like this:

SELECT
ContactID, ContactLastName, ContactFirstName,
IIf(Sex='F', 2, 1) +
Switch(Location='New York', 5,
Location='New Jersey', 3,
Location='Boston', 1,
True, 0)
As ProspectRank
FROM Contacts

A more flexible way would involve having point tables for the various
ranking factors, and joining them in the query to the appropriate
fields, to pick up the points associated with each factor.

 

[Stavrogin]

Thanks Dirk.

I'm experiencing a problem (or two). I'm new to Access so please bear
with me.

When I try to create a query with one field (attempting to count the
number of males in the "gender" field), I get the following error: "the
syntax of the subquery in this expression is incorrect. check the
subquery's syntax and enclose the subquery in parentheses".

I get this error when using the following criteria: SELECT Count(*)
FROM Contacts WHERE ContactType = 'Customer'



Any ideas?

 

[Dirk Goldgar]

Thanks Dirk.

I'm experiencing a problem (or two). I'm new to Access so please bear
with me.

When I try to create a query with one field (attempting to count the
number of males in the "gender" field), I get the following error:
"the syntax of the subquery in this expression is incorrect. check
the subquery's syntax and enclose the subquery in parentheses".

I get this error when using the following criteria: SELECT Count(*)
FROM Contacts WHERE ContactType = 'Customer'

Any ideas?

Where does a subquery come into it? Or criteria? The SQL I gave you
was the whole SQL view of the query; you wouldn't paste it into the
criteria row for a query field. Of course, I don't know what your
table's actual name is, nor the names and types of the fields in the
table, so you have to consider my SQL to be mere examples, not literal
SQL to be pasted in and run.

What's your table name? Is it, in fact, named "Contacts", or is it
named something else?

Is the field name "Gender"?

What type of field is it? Text, number, yes/no?

What value in the field indicates that the contact is male? Female?

 

[Stavrogin]

The subquery might be my own doing. As I mentioned, I'm really new to
Access with my only knowledge comes from a "for Dummies book." And I
am quickly learning that there is a difference between learning about
Access and actually using it.

Just to clarify... I tried to create a query and stick that in as the
critera for the first query field. Am I right in presuming that this
created the subquery?

The tables name is "Names" and the field is "Gender", with two
variables, "Male" and "Female".

 

[Stavrogin]

I should clarify that I am aware that the information needs to be
tailored to my particular database & tables.

So I am using the code: SELECT Count(*) FROM Name WHERE Gender = 'Male'


What I am confused about is where this code should go. Should this be
part of a query or a separate table.... or something else?


Anyway, thanks for your patience.

 

[Chaim]

It would really help to see your SQL.

Open the query in Design View, right click in the table panel and select SQL
View from the drop down menu that appears. You can then copy the text of the
SQL and paste it into a post so we can see it.

 

[Stavrogin]

Ok, I'm a little less experienced than you're thinking. The only SQL I
was using was what was offered by Dirk. So, SELECT Count(*) FROM Names
WHERE Gender = 'Male'

I still don't understand how to write SQL at this point... so the
problem I am having is not related to not being able to get a
particular expression to work (since I can't write them at all!).

So let me reclarify the issue by explaining my purpose in all of this.
Basically, I have a table of 10,000 names. This table includes a
person's gender, address, yearly salary and also their "status" (i.e.
are they a prospect or are they a customer).

What I am seeking to do is create a table (or a query) that will allow
me to analyze this data. For example, I want to be able to know how
many males are in this list of 10,000. I would also like to know how
many male prospects are in this database.

I understand that I can produce these results through a language called
SQL. What I don't understand is how I would go about producing these
results. Would I use a table... if so, where would I enter in the SQL
expression?


My apologies if this post is really confusing... I'm trying to learn as
I go and unfortunately not doing a very good job.


Thanks.

 

[Dirk Goldgar]

I should clarify that I am aware that the information needs to be
tailored to my particular database & tables.

So I am using the code: SELECT Count(*) FROM Name WHERE Gender =
'Male'

I think you left the "s" off of "Names". Your query is correct, based
on what you've told me, if the SQL is:

SELECT Count(*) FROM Names WHERE Gender = 'Male';

What I am confused about is where this code should go. Should this be
part of a query or a separate table.... or something else?

This is where you went wrong. By pasting it into the Criteria row of
another query, you created a subquery. That's not what you wanted.
Instead, take that query (or create a new one, if that one has some
other use) and change it to SQL View. Then completely replace the SQL
for the query with the new SQL. If you now change to Design View you'll
see how this is represented in the visual query designer (you could have
built it that way, in Design View, but SQL is easier to post). And if
you switch to Datasheet View you'll see the result of the query.

 

[Stavrogin]

That's it!! It works. Thanks very much.

If I may just ask one more question so I can get started researching a
new topic... now that I have learned how to count records, what is the
standard way of analyzing such data?

To give a practical example of what I am asking:

-say I have a database of 10,000 people with information on their age,
gender, salary, location and status (prospect or customer)
-I want to be able to determine what sort of person becomes a customer
and what sort of person remains a prospect (in other words, what sort
of people buy my product and what sort of people don't buy it) so that
I can send promotions only to the first group
-I want to be able to create a table that can show, for example, that
5% of all males buy my product whereas only 3% of females buy my
product AS WELL as showing that 30% of engineers buy my product whereas
only 6% of grocery store owners buy my product. Ultimately, I also
want to move a step further and determine the percentage of male
engineers that purchase my product.

Any ideas on how one would go about doing this?

I appreciate this might be a rather involved issue... so if anyone
could just point me to some articles that I could use to research this
topic, that would also be terrific.

What I am lacking right now is a frame of reference that will let me
understand what it is I need to learn more about.


Thanks

 

[Larry Linson]

What you are now asking for is application-specific design information,
rather than "how-to" information about using Access. It would only be
happenstance if you encountered someone here who has the specific
application information you need. I think you'll have to try to find
newsgroups, or websites that deal with the specific type of calculation /
computation that you want to do.

Larry Linson
Microsoft Access

 

[Stavrogin]

Ok, that makes sense. Thanks!