Counting Number Records per Unique ID, saving it to another table:

[Guest]

I have a form frmVendors with a subform frmPayments. The main form shows
information on vendors, and the subform is a continuous form which lists the
checks paid out to each vendor. Users are typically entering new payments
into the subform on the main form.

The forms are linked to tables: Linked by VendorID (one to many)
tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor
City
tblPayments: Key=CheckID, VendorID, PaymentAmount

There is a field in tblVendor called NumberChecks to show the total amount
of checks paid out to that vendor. There is a text box on the main form to
show NumberChecks. Right now, the user must increment NumberChecks manually
when they add a new payment amount.

I need a way to count the number of checks for each VendorID in tblPayments,
and store it in tblVendors in the NumberChecks field. I also want the
NumberChecks field on frmVendors to update when a new payment is added or if
a payment is deleted.
Any Ideas?

 

[Jeff Boyce]

Stop storing the "number of checks"! This value can be derived using a
simple Totals query, or by using the DCount() function.

That way, no one needs to remember to update the field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

You shouldn't store calculated values. As fellow MVP John Vinson likes to
say, "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. "

Use a query to calculate the total. The SQL would be something like:

SELECT VendorID, [Vendor Name], [Vendor Address], Count(*) AS NumberChecks
FROM tblVendor INNER JOIN tblPayments
ON tblVendor.VendorID = tblPayments.VendorID
GROUP BY VendorID, [Vendor Name], [Vendor Address]

Use that query anywhere you would otherwise have used tblVendor.

 

[Guest]

if your subform is filtered to contain only checks for a single vendor you
can use the recordcount property of its recordset object to get the number of
checks for that vendor.

Or you could write a VBA function to open a recordset, run some SQL
statement, and return the record count of the recordset. Pretty much the
same effect, but wouldn't require the subform to be filter.. or even be open.

 

[Guest]

Hi all,
Thanks for the advice. No one ever told me not to store calculated fields
(i'm self taught) but it does make sense. I had been working on a solution
where I had a hidden field txtCheckCount on the subform with control source
=Count([PaymentAmount]).
Then on the main form I had an unbound field with control source
=Forms!frmVendor![frmPayment]!txtCheckCount

This was working to show the number of checks but I wasn't able to store it
in the database. Now I will simply take the NumberChecks field out of
tblVendor calculate the value where needed.

Thanks!