Count of records and Update

[Andrew]

Hi All,

I have a few separate queries that count the number of data records from a
listing after they filter out for a particular type of data (Passive,
Unreviewed, etc...).

These counts now need to update into fields in another table (DMTrack).

My count queries work fine, but I'm having a problem trying to update the
final table.
The final table (DMTrack) contains 143 records (all subject data
demograhics, therefore 143 subjects, this record total will never change and
as such the indexing does not allow duplicates).
The subject records have data fields in the table called Passive, Unreviewed
etc...(all number fields to collect numerical totals).
These are the fields that need to have the derived totals, from the count
queries, updated.

The count queries pull the totals of particular records into a field called
eg. CountOfPassives, and I want to update the Passives total in the final
table (DMTrack).

Please help if you understand the issue.

 

[John W. Vinson]

Hi All,

I have a few separate queries that count the number of data records from a
listing after they filter out for a particular type of data (Passive,
Unreviewed, etc...).

These counts now need to update into fields in another table (DMTrack).

Why?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you have a DEMONSTRATED - not assumed! - case in which the totals queries
are intolerably slow, then consider storing the data.

My count queries work fine, but I'm having a problem trying to update the
final table.
The final table (DMTrack) contains 143 records (all subject data
demograhics, therefore 143 subjects, this record total will never change and
as such the indexing does not allow duplicates).
The subject records have data fields in the table called Passive, Unreviewed
etc...(all number fields to collect numerical totals).
These are the fields that need to have the derived totals, from the count
queries, updated.

The count queries pull the totals of particular records into a field called
eg. CountOfPassives, and I want to update the Passives total in the final
table (DMTrack).

A single Crosstab query with the type of data as the column header should do
this in one operation, and always reflect the actual data in your table.

 

[Andrew]

Thanks John,

I'm not too sure how crosstab Qs work, as yet.
I know they work to summarize data points, but I'll have to look up some
code for updating the totals on the fly.

Do I reference the crosstab Q directly from the Form and set it as a control
source?

 

[John W. Vinson]

Thanks John,

I'm not too sure how crosstab Qs work, as yet.

Like a whole bunch of totals queries rolled into one. But you only need to
have the one (more complex) query, rather than one totals query per field.

I know they work to summarize data points, but I'll have to look up some
code for updating the totals on the fly.

You don't *UPDATE* the totals because you don't store the totals anywhere. The
Crosstab query dynamically calculates the totals.

Do I reference the crosstab Q directly from the Form and set it as a control
source?

You would use the Crosstab query as the Recordsource for the form... and
simply open the form to run the query and display its values.