×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Field to count specific records

Field to count specific records

Field to count specific records

(OP)
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!!

RE: Field to count specific records

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

CODE --> SQL

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

RE: Field to count specific records

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.

RE: Field to count specific records

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close