Access 2013 One record from many with same key

Scott,

I'm sure you understand it very well and then some; believe me when I say, I don't: there are nuances that you are overlooking. If you want to be helpful, help me sum without SQL or Setting anything unusual in the design Query, because I ran a sum and I didn't use any of those two option. I ran something between three files and came up with what I needed.

The only difference here is that instead of drawing from the largest population, I'm drawing from a subset sitting in another file. Now I use the word file as you might use the word table.

Understand, finding even the words to describe are reaching for me.

Thank you in advance; I am also working to integrate the data into a letter. I presume I can use Microsoft Word, in template form, and I presume that it should be so user friendly, as to 123, and I'm done, if not, how far from easy is it?

I can handle code if that's what it takes. I don't know how long it will take me, but if its code, then I need to know up front. As you know there are many dead ends when you are searching and developing in new territory, at least with this one, I haven't attempted it before. I made a new discovery, an equation of sort that reflects the indirect relationship between efficacy and unmet success over repeated trials.

I hope not to repeat that phenomenon; it's draining to say the least, and after all, technology is suppose to be end-user friendly.

I will mention, since Scott brought it up, I don't follow Group By... I think I've used it before, but I couldn't make it work.

What's not to follow? A Group By query allows you to aggregate values across multiple records with the same value. This is why we kept telling you that you needed only ONE Visits table with a one to many relationship with your People table. With that structure a Group By query allows you to do those aggregates.

As John said earlier, when in Query Design Mode, you press the Sigma icon on the Ribbon and it adds a Group By row to the grid area. In this row you can choose how to treat the value in that column. If you select Group By, then it will only show one row for that value. If you use multiple Group Bys, then if groups from left to right. So lets say you wanted a sum of visits, not only by patient but by Facility as well. So you put the Patien in the leftmost column as a Group By, Facility in the second column also as a Group by then add Visits in the third column, choosing a aggregate function. In your case, Sum, but you could also use Average or whatever is appropriate.

This will give you what you want.

You can also do this in a report. You can use the Report wizard to create the report and select to Group By Patient. Then you can select an aggregate on the Visits column to Sum the visits.

Try

SELECT MembershipPersonIDPerson.Person, MembershipPersonIDPerson.PersonID, Sum(Visit) AS NumberOfVisits

FROM MembershipPersonIDPerson INNER JOIN AAMasterFile

ON AAMasterFile.PersonID = MembershipPersonIDPerson.PersonID

GROUP BY AAMasterFile.PersonID;

What this will do will be to assemble all of the AAMasterFile records for each Person into a group (that's the GROUP BY), and within that group sum up (SUM) the values of all the Visit field values. It will then display the values of Person (the person's name I presume), the PersonID (not of much interest but maybe useful for debugging) and the sum of  Visit, using the alias name NumberOfVisits for the total.

I will mention, since Scott brought it up, I don't follow Group By... I think I've used it before, but I couldn't make it work.

First, you should rename your tables. Change MembershipPersonIDPerson to tblPeople. No need to have such a long and redundant name. The other change to tblVisits. It is a more meaningful name.

Then follow John's instructions to turn use that query to make a Group By query to get your totals.