Unique count based on two fields

[Jeff H]

I have a subform of services provided. The Services table records each service, but I need a count of visits, and a provider can provide more than one service per visit. I can get away with assuming that there would only be one visit per day. But the patient could be visited by more than one volunteer in one day.

So in my query the criterion would be: All instances where the VID & Date are the same count as one visit. Is that doable?

[HansV]

Perhaps

SELECT Count(*) AS UniqueCount
FROM (SELECT DISTINCT VID, Date FROM Services)

[Jeff H]

Sorry, but I have no experience with SQL. I don't see where to add that. The auto-generated SQL for this query is:

Code: Select all

SELECT Activity.*, Activity.Date, Volunteers.[Full Name]
FROM Volunteers INNER JOIN (Patients INNER JOIN Activity ON Patients.PatientID = Activity.PatientID_FK) ON Volunteers.VolunteerID = Activity.VolunteerID_FK
ORDER BY Activity.Date DESC;

[HansV]

What exactly do you want the query to return?

[Jeff H]

This query is for a sub-form in a form based on patients. I want the sub-form to display all the service events for the selected patient.

I've included all fields (*) from the Activity table plus FullName from the Volunteers table and Date from the Activity table to be the sort field. The query grid includes both the Volunteers and Patients tables lined 1 to Many with the Activity table.

[Jeff H]

BTW, if this is relevant, I'm already getting the sum of hours and miles with footer calculated fields in the sub-form. If this could be done that way, that would be fine. Trying to puzzle it out, though, it seemed to me it might be easier in the query.

[HansV]

It's better to calculate it separately.

[Jeff H]

Do you mean in a calculated field on the sub-form? How would I write it there? I couldn't figure it out in the builder and I don't see where to access SQL for a control.

[HansV]

Can you attach a stripped down and zipped sample database? Thanks in advance.

[Jeff H]

Here it is. I just removed the personally identifying information.

In preparing this, I noticed that the sums of hours and miles in the Activities by Patient form are correct for some patients and wrong for others. There is some fundamental element of Access that I can't seem to click into. In Excel I have my technical limitations, but in general I get it and I can apply VBA and formulas in creative ways. I understand the basis of relationship databases, but on a practical level I get confused. I'm hoping that having created this practical database in Excel will help me make the transition to Access.

If you can see from this a key element or concept I'm missing, I would really like to learn how to use Access effectively. But I understand if my level of confusion is too much for you straighten me out in a forum context.

BTW, I have started looking into SQL resources, starting with CodeAcademy. Many years ago when I was building Access databases for work, I had a very helpful programmer who always solved my problems with SQL. I think having more familiarity with it might help me understand a little better.

- Jeff

Sample.zip

[HansV]

Can you give me some examples of a patient for whom the total hours and miles are incorrect?

[Jeff H]

I was thinking that record 2 of 192, EA01, was wrong because it shows 0 hours and 1,254 miles (which I thought was too much).

But now I see that the miles are correct. The problem in this case is summing hours: there are actually 120 hours which is rendered with the format [h]:mm in Excel but I don't know how that would be accomplished in Access.

[HansV]

Thanks. Access doesn't have a cumulative time format, so you could display decimal hours:
- Set the Control Source of HoursSubTotal to =24*Sum([Hours])
- Set the Format property to Fixed and Decimal Places to 2.

[HansV]

For the number of visits, create a query named qryVisits with the following SQL:

SELECT DISTINCT PatientID_FK, VolunteerID_FK, Date
FROM Activity;

Set the Control Source of the text box in the subform footer to

=DCount("*","qryVisits","PatientID_FK=" & Nz([PatientID_FK],0))

[Jeff H]

Yes!! These fixes worked. Thank you!

[HansV]

There's no hurry, but I would eventually replace the text fields Status, WellSpring, Admin and Board in the Volunteers table with number fields that are linked with the primary key in the tables of the same name.

[Jeff H]

Yes, thanks for pointing it out. Those will be lookup fields. I brought the data in first and now I'm taking it little by little to adapt it all.

[HansV]

I thought so - that's fine!