Access 2013 One record from many with same key

John,

I have to fill the tables, first, right?

Just so I'm clear.

I'm sorry, but we're still not communicating clearly. I don't know how to say it any better; maybe I should get another pair of eyes on this thread.

You do NOT NEED 28 VISITS TABLES.

You need ONE visits table, with ALL OF THE VISITS TO ALL 28 FACILITIES in it. Each row in the table will have a field to indicate which facility it is, and a field for the PersonID, and a field for the data, so you don't NEED a separate table for each facility.

This table will need to be populated from your spreadsheet, but as Scott says, it's not at all clear what your spreadsheet looks like. You posted:

Table: Facility A: Person - 123456 - Visits 4; Person - 234567 - Visits 5; Person - 345678 - Visits 2..

which looks (to me, maybe my inexperience with Excel conventions is showing) like one Row of a spreadsheet, possibly with multiple cells or maybe just one cell with semicolon separated data.

Then a few lines down you post

Facility A

Person        Visit

123456         4

234567         5

345678         2

Facility B

Person        Visit

123456         3

234567         4

456789         1

That appears to be the same data laid out differently. Is what you have in the five rows starting with "Facility A" one worksheet? one Workbook? an Excel Table object within a worksheet?  Do you have the same data displayed twice, once with all the values on one row and again as separate tables?

Remember: we can only see what you post. We can't see your screen, we can't read your mind! Could you please, as Scott also requested, post a picture of your spreadsheet?

And yes, of course you have to fill in the tables. I presume we can fill them in by running an Append Query from your spreadsheet, so no, you don't need to rekey anything! But until I understand what we're copying FROM then I can't help write the query.

John,

Thank you again...

I apologize for the cross talk; I wasn't clear, but I believe I am now.

I'll see tomorrow.

Have a good one, and thank you all again.

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

I have tables in Access of all 28

I have tables I've created with your help

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

I hope I

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.