Using DCount and/or DSum in Update Query

[Abe W]

Using an update query to count records for statistical analysis. The table
has a field, Count, that has a 1 in each record. Thus trying to count the
number of selected records. For example, how many records with STATE = "AR".
How many records with STATE= "AR" and the letter T in a specified field.
This is a volunteer project for USAF MARS, develping statistical data on
participation.

 

[Duane Hookom]

Is there a question in there someplace? Are you attempting to display
details and counts? Is this output to a form or report or just datasheet?

 

[Abe W]

Duane, thanks for responding. My question is the format of the query. I have
tried everything and cannot get a query to place the number of records into a
file. For example, By looking at the table I know there are 254 records
there. But I need that number to develop percentahes later. Then I need to
know how many of the records in that file are in Arkansa, Texas, Oklahoma,
Louisian and Mississippi, so that I can use that number against the whole
number to get their percentages, There are also percentages need for each
state for hours of participation in a number of categories and these must be
compared to the whole, to their state and to other types of participation.
All of these numbers must go into a statistical summary report. I know that
it is not usual to store record counts in a table. But since DCount and DSum
are apparently supposed to work in update queries, I thought that would be
the way to go. In this case I don't see any other way. All the tables
involved have a field called Counter and that field has a 1 in each record so
getting a sum of that field for the selected records should do the trick.
Again for example, If there are 13 members in Arkansas and The query selects
STATE="AR", the counter field should return 13 as the DSum. I have a
sanitized database of the necessary data that I can send Zipped if it would
help.

Thanks so much for your response.

Abe Winters

 

[Duane Hookom]

Abe,
I took your response and pasted it into Word to try to make some sense of
it. I divided your message into at least 5 paragraphs or thoughts to try to
get a handle on what you are asking.

I still don't have a clue regarding your table structure other than a field
named "State". Also, "place the number of records into a file" is completely
out of my understanding. I don't understand where Hours and Categories and
participation are coming from.

It would really help if you provided:
-table(s) structure
-field types
-sample records
-desired output of the sample records

 

[Abe W]

Sorry, I guess I'm too familiar with my problem

I have a TABLE which contains many fields. The table is called MEMBERS. The
table contains information regarding the members. One of the fields is
M_CALL. This field contains the radio callsign of the various members. The
format of the callsign will indicate if a member is in training. For example
AFT4AA would be a trainee, as opposed to AFA4ZZ. The Members table also has
the members name, address, city, state zip, and other data. The State field
contains the US Post Office State abbreviation, i.e., STATE would contain AR
representing Arkansas, OK for Oklahoma, etc. The MEMBERS table has a field
called Counter in which is the number 1 for all records. The purpose is for
counting.

There is another table called PARTICIPATION TIME. It has a number of fields.
One field is the M_CALL as described above. It has other fields for
participation hours (numbers) in various categories of participation. It
also has a field called Counter in which is the number 1 in each record for
counting purposes.
A TABLE is necessary to hold the following numbers to develop a statistical
report
The Table is called Statistics and has fields for the followign data.

Number of members in the region (all STATES)
Number of Members in each STATE
% of members in each state relative to the entire region
Number of Members in training in the entire region (all STATES) Developed by
Selecting M_CALL Like "??T???" in the MEMBERS table
% of members in training relative to the total membership
Number of Members in training in each State
% of members in that state relative to entire region
% of members relative to the total in that state
Number of members whose participation is less than 12 hours (total of all
categories)but 1 hour or greater in the entire region
% of members whose particpation is less than 12 hours but 1 or greater in
the Region
Number of members in each state whose participation is less than 12
hours(total of all categories) but 1 hour or greater.
% of members in each state whose participation is less than 12 hours (total
of all categories) but 1 hour or greater. (Percentage of that state's members)
Number of members in each state who have zero participation (total of all
hours)
% of members with zero participation in each state (percentage of that
states's members)

The total mebership in each region can be as low as 50 or as many as several
thousand.
The participation hours are divided into 9 different categories, but for the
statistics purposes, all categories are added together to determine if they
have some participation but less than 12, or of they have no peraticipation.

I hope this clarifies the problem.

Thanks again for your help.

Abe Winters.

 

[Duane Hookom]

You don't want much do you

I would break this into small, single requirements and create a report or
subreport to solve the requirement. If you can't figure this out, ask one
question at a time.

 

[Abe W]

OK, I understand that I can use a report and subreport but placing nearly 100
subreports on a report is a huge mess.

Here is a simple question.

Is there a method of counting records and taking the result of the count and
placing that number in a table using an update query?

Thanks,

Abe Winters

 

[Duane Hookom]

I would personally not even include a field named "Counter" so storing a
count isn't something I would do. You can use make table queries to build
summary records. You can also use update queries with DSum() or DCount().