Access 2013 One record from many with same key

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.

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,

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.

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.

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.