How to write an aggregate to count visits when multiple visits sometimes count as one

[JJeffH]

I have a query to summarize volunteer services to patients during a specified time period. The query will be used for a report which will be organized by patient, then under each patient each volunteer providing services to that patient will be listed along with the number of visits, total hours spent, and total miles traveled.

The basic query is laid out like this:

• Grouped by Patient first;
• Grouped by Volunteer second;
• Number of visits is the problem at hand;
• Hours are calculated as Sum([tblActivity].[Hours])*24;
• Miles are the Sum of tblActivity.Hours;
• Then it’s sorted by Patient Last, Patient First, Volunteer Last;
• And the Date is included for filtering.

The sorting and filtering fields are not displayed.



However, I don’t know how to write an expression for the Number of Visits because there is a special condition.

If a volunteer provides more than one category of service during one visit, then each service is recorded separately. The service categories don’t enter into this query, but multiple entries in the Activity table, where the Patient, Volunteer, and Date are all the same, must be counted as one visit. Other than that case, each entry in the Activity table counts as one visit. (It's fair to assume that a volunteer would not visit the same patient more than once in a day.)

Thanks,
- Jeff

[Micron]

You might be trying to do too much at the query level if reports are the final output. Reports have their own grouping, sorting and summing capabilities that you can't do in a query. Not only that, you can sum over a group or over all as well as do running sums. They also have the ability to contain a sort of calculated control that you can sum over (e.g. you set this unbound textbox source to =1 and sum it in a footer and you get a count in that group). So reports can be quite powerful if you learn some of their tricks.

To do this at the query level would require that your tables design and data input are suitable. If the visit data is separate from the services data as I think it should be, then per patient per provider, there is only one visit in a given day, regardless of the number of services provided - according to your explanation. If you have melded this data, then I think that is your problem and the db lacks sufficient normalization.

[Cottonshirt]

If a volunteer provides more than one category of service during one visit, then each service is recorded separately.

in which case you are not counting visits, you are counting services.

maybe if you set your query up to count services, you would get the answer you are looking for.


many thanks,


Cottonshirt

[JJeffH]

You might be trying to do too much at the query level if reports are the final output. Reports have their own grouping, sorting and summing capabilities that you can't do in a query.

This the kind of guidance I'm looking for here. Thanks. I have already created this database fully in Excel, but I'm using that model to try and learn Access, as I've wanted to do for years. This particular issue, recording the category of service, is something new. Before I gave them a database they basically ignored the specific services, but once they saw that a database could potentially help them track those categories they asked me to write in that capability. I have seen the kind of report-level control you mean when I confronted this same counting issue in the Activity input form in Access. Here I thought I needed a query before I started creating the report, but I'm going to start on the report now. I'm sure I'll be posting over there before long!

To do this at the query level would require that your tables design and data input are suitable. If the visit data is separate from the services data as I think it should be, then per patient per provider, there is only one visit in a given day, regardless of the number of services provided - according to your explanation. If you have melded this data, then I think that is your problem and the db lacks sufficient normalization.

This is a helpful insight. I need to consider how I might separate visit events from service delivery. In Excel I had them enter multiple services in one visit with comma separated code numbers, then parsed out each service in VBA for reporting. I'm guessing Access might provide a more efficient way to do something like that.

Thanks!
- Jeff

[Micron]

I'm using that model to try and learn Access

Do yourself a favour and forget what you know about Excel when you develop a db. That will only lead you down the wrong path. Study Access, understand normalization. Maybe start with these tips:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[JJeffH]

Micron, I had written a reply to your last post this morning and I thought I posted it, but I see now I didn't. The gist was: Many thanks for all the links.

It's not that I'm trying to applying Excel methods to Access. Years ago I wrote several usable dbs in Access, but I always got bogged down in the syntax and structures, and I never had time to get comfortable with it. I found Excel much easier so I've worked with that a lot. In this case, I wrote this organization's db in Excel because I could, even though I knew it would be better in Access. Now that I'm retired I have time to take the model of how that db functions and use it as a template to transition into Access ... I hope.

These references should help a lot.

- Jeff

[Micron]

Good luck! The forum will be here when you need it.