Field to count specific records

[monkeysee]

I have a client DB that records visits. I would like to add a field in the table that will count the actual number of visits a client has in their history. I'd like it in the table so that the field can be used effortlessly in a form, report or query.

If I use the count function in query it works as long as there is no other filters in the query. If there are other filters, then it only counts the visits from the filtered results; thus not returning the correct count of visits the client has.

This is what I actually want to accomplish. When billing the clients insurance, the user needs to know if the client is an established client or a new client since there is different billing coding for each. With the existing query, data returns with the visits that have not yet been billed by filtering the field: InsBillDate to "null". This is helpful, but the user has no way of knowing if the client is an established or a new client and the user will have to query each client to see if they have other visits on record or if they are new to the clinic.

I have a table with the following fields: VisitID; Client_ID; VisitDate; InsBillDate
I would like to add another field as VisitCount; but am unsure as to how to have this field calculate.

I am open to other ideas....

Thank everyone in advance! I have always received great advice in the past from Tek-Tips and appreciate everyone's patience with those of us that are not 'experts' in access!!

 

[dhookom]

I wouldn't store the value. You could create a simple group by query:

SELECT Client_ID, Count(VisitDate) as Visits
FROM [table with the following fields]
GROUP BY Client_ID

This can be displayed in a subform or subreport that is linked Master/Child using the Client_ID.

You could also use DCount() or create a small user-defined function to return the number of visits.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

I wouldn't store the value, either.
You could create a query/view (based on Select statement given by Duane) and treat it as another table.
You can retrieve number of visits per client, or you can include this table to any other Select statement if you want to have - for example - list of clients who have more than 15 visits.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

The only issue with including the totals query in a form's record source is the records will not be editable. This is a great solution for reports and read-only forms.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016