Access 2013 One record from many with same key

Your design still has a lot of redundancy. In the 14th Visit table are all the FacilityID's equal to 14? Or how were you intending to relate the Facility table to visits?

You have the PersonName in the Person table - that's good, that's where it should be; but you ALSO have a PersonName in the Visit table. That's redundant and an invitation for trouble; with this design it would be quite possible to have the Person table contain PersonID 123, "Dave Robertson" and one of the Visits table contain PersonID 123, "Laura Buckram". 

However... before we get your tables properly normalized... is this going to be a database used in an ongoing manner, with more persons, visits and facilities? Or is this a "one shot" problem, just trying to get this desired report out for this specific set of data?

Have you tried reading what John and others have been trying to tell you? YOU SHOULD NOT have 28 Visit tables. You have not explained what the difference is between these tables. But I see no reason for 28 of them.

You can create a dummy of your spreadsheet showing the actual column headings, but with dummy data. We NEED to see what this looks like to be able to give meaningful help.

If you really want our help, then help us help you.

John,

First off let me apologize for not revealing specifics; I'm doing this work for a third party and I don't want to reveal more than necessary when it comes to actual data. However, I think I have things set properly and in such a way that I can share what I have, so that I can complete this task.

Background: I worked with ACCESS decades ago, but I don't think I ever mastered the outcome.

I have

28 Visit tables

With the following fields:

FacilityID, PersonID(Key), PersonName, Visits

1 Facility table

With the following fields

FacilityID (Key), Facility, Address, City, State, Zip

1 Person table

With the following fields

PersonID, PersonName

I presume this is enough to create a report that is functional with each person and the number of visits to each facility (which I will want to sum for each person)

I hope this is good and valid for our purposes

but as far as from where from and to, yes - we need to talk

From... I have an excel of course you know... in fact all 28

No. I don't know. You have not answered my questions and I cannot see your spreadsheet.

I have tables in Access of all 28

I did not suggest that and it is not needed, though if you have copied the data into Access it can certainly be used to fill your normalized tables.

I have tables I've created with your help

Now, as far as appending a Query - an explanation would help.

I was talking about a particular type of query - an APPEND QUERY, which takes data from one place (your spreadsheet or a part of it, or from your 28 Visits tables) and appends (copies new records into the target table)  the data in that source into the new Visits table. If (as repeatedly requested) you show us the current structure (tablenames, fieldnames, spreadsheet screen image) of your data we'll be able to help you create such a query.

Now, as far as appending a Query - an explanation would help. 

I don't know why you can't open my file, but the following are the labels and queries from the demo, which should give you an idea of the process involved in decomposing a 'flat file' table.  The demo uses a simple international contacts lists so there are countries, regions and cities involved in the addresses.  Also each contact can have more than one employer:

The query below inserts rows into the Countries table  with unique values of the Country column imported from Excel into the table MasterTable.  This is necessary before rows can be inserted into the Regions table as that table references the Countries table in a many-to-one relationship.

INSERT INTO Countries (Country)

SELECT DISTINCT Country

FROM MasterTable;

Having inserted rows into the Countries table rows can now be inserted into the Regions table with the query below.  This joins the MasterTable to the newly filled Countries table on the Country columns and inserts unique values from the Region column of the MasterTable and the CountryID column of the Countries table into the Regions table.

INSERT INTO Regions (Region, CountryID)

SELECT DISTINCT Region, CountryID

FROM MasterTable INNER JOIN Countries

ON MasterTable.Country=Countries.Country;

Having inserted rows into the Regions table rows can now be inserted into the Cities table with the query below.  This joins the MasterTable to the newly filled Regions table on the Region columns.  The Countries table is joined to the  MasterTable on the Country columns and to the Regions table on the CountryID columns, thus taking account of any regions of the same name in different countries.  The query inserts unique values from the City column of the MasterTable and the RegionID column of the Regions table into the Cities table.

INSERT INTO Cities (City, RegionID)

SELECT DISTINCT MasterTable.City, Regions.RegionID

FROM Countries INNER JOIN (MasterTable INNER JOIN Regions

ON MasterTable.Region=Regions.Region)

ON (MasterTable.Country=Countries.Country)

AND (Countries.CountryID=Regions.CountryID);

The previous queries inserted rows into the Countries, Regions and Cities tables. Following the insertion of data into the last of these, Cities, it is now possible to insert rows into the Contacts table as this only needs to reference the Cities table, the relevant Region and Country being referenced via the relationships between these three tables.  The query below does this by joining the MasterTable to both the Cities table, on the City columns, and to the Regions table, on the Region columns.  The Cities table is also joined to Regions on RegionID and the Countries table is joined to the MasterTable on Country and the Regions table on Country ID. This is to take account of the possibility of two cities having the same name, but being in different regions,  which themselves could theoretically  have the same name but be in different countries, so that the correct CityID value is inserted into Contacts.

For simplicity it is assumed that contacts at the same address have unique names.  This might not always be the case, particularly with commercial premises (I once worked with two Maggie Taylors in the same building!).  In such cases, however, there is likely to be some distinguishing value such as Job Title or Department which could be used.

INSERT INTO Contacts ( FirstName, LastName, Address, CityID )

SELECT DISTINCT MasterTable.FirstName, MasterTable.LastName, MasterTable.Address, Cities.CityID

FROM Countries INNER JOIN ((MasterTable INNER JOIN Cities ON MasterTable.City = Cities.City)

INNER JOIN Regions ON (Regions.RegionID = Cities.RegionID)

AND (MasterTable.Region = Regions.Region))

ON (Countries.CountryID = Regions.CountryID)

AND (Countries.Country = MasterTable.Country);

The query below inserts rows into the Employers table  with unique values of the Employer column imported from Excel into the table MasterTable.  This is necessary before rows can be inserted into the ContactEmployers table as that table references the Employers table.

INSERT INTO Employers (Employer)

SELECT DISTINCT Employer

FROM MasterTable;

Having inserted rows into the Contacts and Employers table it is now possible to insert rows into the ContactEmployers table which models the many-to-many relationship between Contacts and Employers.  The query below does this by joining the MasterTable to Contacts on the Address, LastName and Firstname columns and to the Employers table on the Employer columns.  The ContactID values from Contacts and EmployerID values from Employers are inserted into the two columns of ContactEmployers.

INSERT INTO ContactEmployers (ContactID, EmployerID)

SELECT Contacts.ContactID, Employers.EmployerID

FROM (Contacts INNER JOIN MasterTable

ON (Contacts.Address=MasterTable.Address)

AND (Contacts.LastName=MasterTable.LastName)

AND (Contacts.FirstName=MasterTable.FirstName))

INNER JOIN Employers ON MasterTable.Employer=Employers.Employer;

The above does assume that the imported data is completely consistent, which might not be the case.