enter a count function to count records?

  • Thread starter Thread starter Kimberly
  • Start date Start date
K

Kimberly

I am desperate I don't know vb, yet I want to enter a count function to count
records in a table or query. Any help is greatly appreciated. I need as
soon as possible.
 
Open your query in Design View

In the white area, on the row labelled Field:
in a blank column type

MyCount: DCount("*","YourTablesName")

(obviously, you would substitute YourTableName with the real name of your
Table)

When you look at your query in its normal view you will see a field called
MyCount with the number of records in the Table which you named.

Each record will have the same number in the MyCount field.

If you want t show this count in a form or report, then put a text box into
either of these in Design View and type into it

=DCount("*","YourTablesName")

Evi
 
Thanks much, however I did not clarify my needs correctly. (Trying to meet a
deadline) I want the count function to number the records in ascending order
starting with 1,2,3 and so on. Like the autonumber feature? Any suggestions?
 
I'm not being odd but it depends on why you need it. The wrong method could
cause you problems
1. To display in your query - it has no particular function (you won't be
able to sort the query via this field - it relies on the sort order of your
query)
2. You want it to go into your table as a sequential numbr
3. You want it in a report to number your records - eg number items in an
order

Evi
 
I think she needs it the same way I do.

<<1. To display in your query - it has no particular function (you won't be
able to sort the query via this field - it relies on the sort order of your
query)>>

I have an invite list of over 2000 people. I need to be able to count how
many people are in each specified query.

For example, How many are Affiliated by Government? A column with an
autonumber will quickly give me the answer, I just have to look at the last
number to see how many individual people I have on this particular list.

This number does not have match, attach, or work with any particular field,
I just want to know how many data fields matched my query.
 
If you just need to know how many records there are in a query then look at
the Navigation bar
in the querie's datasheet view towards the bottom of the screen. It will say
eg Record 1 of 200
If you need to display this in a report then you can use

=Count(*)
in a report footer
To show how many records there are.

if you want to count a group of records in a report and have them numbered 1
to the Total then you would use a running sum text box.

You could also use DCount to count things according to a criteria which you
specify.

In your case, Char, are you talking about putting this data into a report?
Do you have a field which tells you that someone in the record is affiliated
by government? Is it a yes/no field or a text field or is it a Number field
(perhaps the Foreign Key field of another table?

Do you see why I need more details?

Evi
 
This data will mostly be separated into queries, parsed down to a readable
form. I am splitting a list of 2000 contacts into Family, Government,
Retired, etc.

The majority of the queries will be based on Affiliation, a text based field.

I would like an autonumber displayed, but a simple count would suffice as
well. On top of that, there is a Spouse field for every contact which if it
is NOT null, needs to be counted as well. This I think may be impossible, so
I will settle for individual counts for each data entry.

Simply appearing in a footer or header is acceptable as well.

Is this enough information?

:

If you just need to know how many records there are in a query then look at
the Navigation bar in the querie's datasheet view towards the bottom of the
screen. It will say eg Record 1 of 200 If you need to display this in a
report then you can use

=Count(*)
in a report footer to show how many records there are.

if you want to count a group of records in a report and have them numbered 1
to the Total then you would use a running sum text box.

You could also use DCount to count things according to a criteria which you
specify.

In your case, Char, are you talking about putting this data into a report?
Do you have a field which tells you that someone in the record is affiliated
by government? Is it a yes/no field or a text field or is it a Number field
(perhaps the Foreign Key field of another table?

Do you see why I need more details?

Evi
 
Now I know what you mean by an affiliation.

There is still something that isn't clear in what you are saying I don't
understand what you mean by 'parsed down to a readable form' . What exactly
do you want to see (give an example). Do you want it to be in a query, or in
a report?

How do you deal with people who have more than 1 affiliation? From your
sample, it is clear that that one person can be both a Family member and
retired and possibly in government too (after watching the House of Lords in
Yesterday In Parliament!)

Do you have one table listing the various affiliations available, one
listing the people and one linking the people with however many affiliations
they belong to? If not, your data could end up as illogical.

If you design your db correctly you won't need to split the data up into
different queries, you could put the whole list in 1 report, use the
SortingGrouping button to Group by Affiliation with a header and footer, add
a runningsum textbox to the number items in each group and add a text box to
the group footer with
=Count(*)
typed into it.

.. If you want a sequential number field in a query to act as a kind of row
counter, have a search in this group for Karl Dewey's post on Ranking (RANK)
in a query..



Evi
 
Evi said:
There is still something that isn't clear in what you are saying I don't
understand what you mean by 'parsed down to a readable form' . What exactly
do you want to see (give an example). Do you want it to be in a query, or in
a report?
Click to expand...

I mean i have 30 fields for each individual, and in order to compose an
invite list, my boss only needs to see the names and their current invitation
status (5 fields).
How do you deal with people who have more than 1 affiliation? From your
sample, it is clear that that one person can be both a Family member and
retired and possibly in government too (after watching the House of Lords in
Yesterday In Parliament!)
Click to expand...

Since each person needs only to be reviewed once, more than one affiliation
is not required. Each person is given an affiliation with the highest value,
for example a Family Member will trump any other Affiliation, VIP will trump
any other affiliation EXCEPT Family, Personal friends will trump any other
Affiliation EXCEPT Family & VIP, etc., etc., and so on.
Do you have one table listing the various affiliations available, one
listing the people and one linking the people with however many affiliations
they belong to? If not, your data could end up as illogical.
Click to expand...
If you design your db correctly you won't need to split the data up into
different queries, you could put the whole list in 1 report, use the
SortingGrouping button to Group by Affiliation with a header and footer, add
a runningsum textbox to the number items in each group and add a text box to
the group footer with
=Count(*)
typed into it.
Click to expand...

I wish I could print the entire database, but unfortunately 82-ytear-old men
can't work with that amount of information.

Thus, I am narrowing fields down so that he can easily think about ONLY
Friends, Family, or VIPs, etc. This way, he may only have to go through 100
or so names in each affiliation, instead of the 2000 name list I currently
have.

It is illogical to print a list of 2000 names anyway, for a man or woman of
any age.
.. If you want a sequential number field in a query to act as a kind of row
counter, have a search in this group for Karl Dewey's post on Ranking (RANK)
in a query..

Evi
Click to expand...

Thanks! Will do.

Char
 
You say:
I mean i have 30 fields for each individual, and in order to compose an
invite list, my boss only needs to see the names and their current invitation
status (5 fields).
Click to expand...

if you're talking about a report (you haven't said) then remove those fields
which you don't require in the report's design view. Or do you mean to say
something else.

I do suggest (if you don't already have it) that you have a linking table
( for your affiliations and a field which ranks them according to
importance. It will be very simple to filter your report by this AffilRank
field so that you only see the highest rank (or the highest 2 ranks, if it's
going to be a bigger group.)


Evi
 
Back
Top