Access 2013 One record from many with same key

It works, and I thank you.

I'm tightening the loose screws in my head now.

My apologies. Since I was typing the query in freehand without an actual database I left out a closing parenthesis for the Sum function:

SELECT [Membership_Person].[PersonName], Sum([Membership_Masterfile].[VIsits]) AS SumOfVisits

FROM [Membership_Person] INNER JOIN [Membership_Masterfile]

ON [Membership_Person].[PersonID] = [Membership_Masterfile].[Person_ID]

GROUP BY [Membership_Person].[PersonName];

and similarly for the other queries.

Note that all queries, without exception, no matter how they're created, ARE SQL. SQL is the language in which queries exist. The wizards and the query grid are just tools to build SQL. So am I at times, just a bit more prone to error, for which I'm sorry!

John,

I can't beat this horse anymore. the SQL comes back with syntax errors...

and like I said before, I didn't originally use SQL... I'm sure I must have use the wizard.

Is there no wizard approach I can use?

AB

To get the sum of visits for a Person, no matter what facility they visited:

SELECT [Membership_Person].[PersonName], Sum([Membership_Masterfile].[VIsits] AS SumOfVisits

FROM [Membership_Person] INNER JOIN [Membership_Masterfile]

ON [Membership_Person].[PersonID] = [Membership_Masterfile].[Person_ID]

GROUP BY [Membership_Person].[PersonName];

To get the sum of visits to a Facility, no matter which person visited:

SELECT [Membership_Facilities].[Facility], [Membership_Facilities].[Address], <other fields as needed>, Sum([Membership_Masterfile].[Visits] AS SumOfVisits

FROM [Membership_Facilities] INNER JOIN [Membership_Masterfile]

ON [Membership_Facilities].[FacilityID] = [Membership_Masterfile].[FacilityID]

GROUP BY [Membership_Facilities].[Facility];

To get subtotals of visits for each combination of facility and person:

SELECT [Membership_Facilities].[Facility], Membership_Person].[PersonName], <other fields as needed from either table>,

Sum([Membership_Masterfile].[Visits] AS SumOfVisits

FROM ([Membership_Facilities] INNER JOIN [Membership_Masterfile]

ON [Membership_Facilities].[FacilityID] = [Membership_Masterfile].[FacilityID])

INNER JOIN [Membership_Person]

ON [Membership_Person].PersonID = [Membership_Masterfile].[Person_ID]

GROUP BY [Membership_Facilities].[Facility], [Membership_Person].[Person_Name];

Any of these queries can have Criteria limiting the dates of visits, the names of people or facilities, or on any other fields in any of the tables. You certainly do not need a separate "subset" table; in fact it's almost always best to have your Table contain the full set of data you'll ever want to report, and use query criteria on properly indexed fields to limit which records appear in a report.

If you want to see the results in a grid display, say with PersonName down the side and Facility across the top, you should be able to use the "Crosstab Query Wizard" after joining the three tables using the joins in my last suggested query. I'm not sufficiently caffeineated at the moment to write the SQL for the crosstab out of my head!