Access 2013 One record from many with same key

I'm at the stage of migrating data, so you are correct there

That is why I have N tables, ready to migrate

I'm ready exactly what you are proposing.

Thank you

Each Person may have zero, one or many Visits: a one-to-many relationship from Persons to Visits on PersonID.

Each Facility may be the site of zero, one or many Visits: a one-to-many relationship from Facilities to Visits on FacilityID.

You seem OBSESSED with the idea that you must have "a series of N tables". YOU DO NOT NEED N TABLES and I don't understand why you feel that you do! At the VERY most, you might have N tables, temporarily, just to migrate your data, but in the production database you will need one and only one Visits table with many records, for many persons, for many facilities. I don't know where you're getting your data (on spreadsheets from an external source perhaps?) but what you will need to do is import or link to the exteral source and run one - or many - Append queries, appending a VisitID, a FacilityID, and a Visits number value from each record in your source table into a new record in the normalized tall-thin Visits table. When you want to generate a Report you would create a new, three-table query joining Visits PersonID to Persons.PersonID, and Visits.FacilityID to Facilities.FacilityID. In that query you can then select the name of the person from Persons, the name of the facility from Facilities, and the number field from Visits; you can group by, sum, average, count, crosstab this query however you like.

Here is where I'm at:

I'm looking at relationships

I have two feeding into one, with a 1 to many relationships.

Person, Facility, and Visits; I want to stream the data in from a series of N tables.

How  do I stream them?

John,

You may have guessed it.. I don't know what the **** I'm doing, frankly.

I am working to get this project out.. but in the interim, I hope to gain the fundamental information that will allow me to create this again, because I will be asked to do so, and I want to.

I suspect my understanding of Tables (and keys-dominant and foreign) is off. It shouldn't be difficult, but rather straight forward.

re: the SQL of the Query: its just

Insert into Visits

Select tablename.*

From tablename

I'll write more information... in a bit

Well, it's still really hard to see what you're doing. I've got to get my telepathy module upgraded :-{)

Your Visit table SHOULD NOT HAVE a PersonName field. The person's name can and should be looked up from the Person table.

The error you're getting suggests that you have an error in your Append query, which (again) I cannot see. Could you please (PLEASE!?) post the SQL view of the query? If the fieldnames are confidential, could you obfuscate them, but at least post the actual SQL with fake fieldnames?

Your eventual goal will be to have tables with data like:

Person

  PersonID (unique primary key)

  PersonName (or, better, LastName, FirstName, MiddleName, Suffix, Title, other needed demographic data)

1  Doe, John

2  Roe, Jane

3  Wu, Shih-han

Facilities

  FacilityID (unique primary key)

  Facility

1  U.S. Congress

2  U.S. Senate

3  Leavenworth Federal Correctional Institution

Visits

  VisitID (autonumber primary key)

  PersonID (Long Integer link to Persons, who visited)

  FacilityID (Long Integer link to Facilities, what did they visit)

  Visits (your number field)

1; 1; 1; 5 - John Doe visited the Congress five times

2; 1; 2; 3 - John Doe visited the Senate three times

3; 3; 3; 3 - Shih-Han Wu visited Leavenworth three times (just visiting his former Congressman of course)

If the data in your 28 tables has duplicate values (the same numeric FacilityID referring to two or more different Facilities, or PersonID 41 referring to Joe Doaks in one table and PersonID 41 referring to Lee Smith in another) then you will have to step back and figure out how to clean up your data.