Access 2013 One record from many with same key

Actually John

I'm working on creating a summation of the visits per person.

It's very interesting: as I mentioned, I haven't built a database in decades - when Access first came out. I found myself in familiar waters with the same familiar confusion. Now that it's functional, and I only have three files... I remember, yes, I've been here before as well.

I've always held that retroactive interference to learning and strategies to combat it are underrated. It's in line with the idea of not learning from history, rather than past mistakes. I was drawn to repeat the same errors I committed when first learning Access on the fly, both then and now. There is something attractive about committing all the errors before creating a final and really, simple solution.

So I'm off to create the summation. I have the idea, that it's a single new table that I will Access through one more relationship, hence RDB.

Thanks again; you accomplished, or we accomplished what I couldn't accomplish with all the videos and white papers on the Internet.

AB, John

You can't imagine how good that message makes me feel, Arlan. I love your term "retroactive interference" - I've seen it often (in myself and in others); "unlearning" something that you know is "the way this is always done" is indeed much harder than learning something from a blank slate.

I'm very glad I was able to help, and wish you all the best with your application.

So, I can't believe the problems I'm having duplicating processes.

I have a master file with the various visits to facilities by people. In some cases the people are in the master file more than once..

Yesterday I pulled the total visits for each person (period).

Today I can't do it...

The Three files are linked - and I don't remember how I pulled the total visits for each person.

Plus, today, I have a subset of the Master file (with no duplicates)... I managed to couple the subset with the PersonID, for each person... and here too, I want the total visits...

I can't link the subset table with any of the other three tables with any referencial integrity - and of course I can't retrieve the total number of visits.

Is it me, or is this the life of a newbie?

:-{(

Sorry this is being such a frustration!

Without knowing what you have tried (the SQL of the query) I can't tell why it isn't working; nor do I know what you did to get a subset, nor why you can't link.

To get the total visits per person you should be able to create a Query based on the Visits table (joined to the Persons table if you want to see the visitor name, as I'm sure you do). Change it to a Totals query by clicking on the Greek Sigma icon (like a sideways M) in the query design ribbon; Group By PersonID and use Count as the totals operator on VisitID. If this isn't working please post the SQL view of your query and indicate in what way it's not working.

Likewise, please describe the subset table (fieldnames, datatypes); how it was created (and why, you should be able to use a select Query to extract a subset without creating a new table); and what happens when you try to establish RI.

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