Access 2013 One record from many with same key

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.

SELECT MembershipPersonIDPerson.Person, MembershipPersonIDPerson.PersonID, AAMasterfile.Visits, AAMasterfile.Facility

FROM AAMasterfile INNER JOIN MembershipPersonIDPerson ON AAMasterfile.PersonID = MembershipPersonIDPerson.PersonID;

So I have an inner join that gives me all the information for my smaller group, i.e. visits to facilities.

Now I want to total those visits per the person.

If This isn't enough information for you?

1. I'm surprised and ready to learn why

If this is enough information for you, I hope to see the solution.

AB, John