Access 2013 One record from many with same key

Actually I don't think there are nuances that I'm overlooking. I think you are seeing complexities where they don't exist. That isn't meant to be a put down, just its your mindset that is causing a problem.

As far as doing a sum of visits by person, the only way is the way we've described it. Either a Group By query or a report. A Group By query using the query grid is basically a GUI where you just select what you need. The Create Report wizard will walk you through the process.

If you designed and populated your tables properly (as we instructed), then you shouldn't need another table with a subset of the data. Just a query.

As for doing a mail merge letter. Once you have a query in Access that shows the records you want. The Mail Merge wizard in can help, but its not that simple to create the merge document in the first place.

John,

Okay, I have cleared the cobwebs; I have cleared the relationship between my masterfile (Visits), and Facilities, and Persons... and created a new relationship between my subset, call it MembershipMasterfile (Visits), Facilities, and Persons... AND, yes, I have a Related Database (again). Newbie Mistake.

So, I am with this project, as I was with the other project, ready to create the table of sum visits.

Before: I created the Sum Visits results without SQL; without Group by

If you have no idea how, fine; just let me know that

I'm trying to clear the way for a linear approach.

I'll be happy to try to give you the best advice I can. You're making that task as difficult as possible, however, by not posting your actual table structure.

If you are (still) assuming that you must have a TABLE of Sum Visits, though, you're misunderstanding how relational databases work. Static data (Visits, Persons, Facilities) are stored in Tables. Dynamic or calculated data (sums) are generated on the fly, in queries. Storing derived data in a Table wastes disk space (trivial these days), wastes time (usually), and worst, risks data corruption; if the underlying Visits data gets edited, your Sums will now be WRONG, with no easy way to detect that they are wrong.

If you're ok with having potentially wrong data in your database and your reports, when a perfectly standard and easy to implement query alternative is available (a very simple Totals query), I don't know what I can say to help other than Good Luck.

John,

I am better than fine with the concept of three files, and a query on the fly for the Sum.

I am not looking to re-write the book. I am looking to replicate whatever I did before, and I'm sure it was a query that resulted in a successful SUM field in a query.

So here is the leanest set of tables. I hope we can easily construct a SUM Query.

Table: Membership_Masterfile

Fields: ID (Key), PersonID, FacilityID, Visits(number)

(where PersonID is listed for each facility with at least a single visit )

Table: Membership_Facilities

Fields: FacilitiyID (Key), Facility, Address, City, State, Zip

Table: Membership_Person

Fields: PersonID (Key), PersonName

(I know I should have noted the SQL in the Query that gave me the sum before)

(I know you and others have probably told me before how to do it)

One very last time, please.

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!