Autofill Table field with count of unique records

[MJCRN]

Hello!
I am very new to Access and am hoping this is a simple question. I have created a database that will compile information for our organization. I am trying to COUNT the number of visits each customer makes to the centre and have that number automatically updated in the Customer Information table.

So I have Table 1: Customer Information, includes field for Number of Visits.
I have another [Table 2] & Form that tracks Visit information.
The Customer and Visits both have unique ID numbers.


Can I write a query or something that will easily track this information?
Please help!
Thanks.

[pbaldy]

Normally you wouldn't store something that can be calculated from transactions, and I wouldn't store this. It can easily be calcuated with a totals query:

SELECT Customer, Count(*) As HowManyVisits
FROM Table2
GROUP BY Customer

With the appropriate table and field names.

[MJCRN]

Why wouldn't you store this information?

I have now created a query that will list the number of visits each customer has made (which is great) but is there no way to put this information into the Customer table as a field that updates for each person as they continue to visit?

[pbaldy]

Allen has thoughts on why you shouldn't:

http://allenbrowne.com/casu-14.html

It can certainly be done, but you have to catch any time/method a record is added, modified or deleted in the transaction table. Or run an update query based on this query frequently. Why go through all that when the answer is so easily done with the query?

[pbaldy]

By the way, if your concern is wanting to query the customer table and get the count, in a new query you simply join the customer table with the query you just created and you're able to return customer info along with the current count. You could even put a criteria on the count query and get counts as of 12/31/15, or whenever. Much more flexible.