Access 2013 One record from many with same key

Raw Record in Excel / Access 2013

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

Table: Facility B: Person - 123456 - Visits 3; Person - 234567 - Visits 4; Person - 456789 - Visits 1...

...

...

Table: Facility N:

(Now that I see it this way, it should be straight forward... it's definitely a design problem on my side)

Final Table of Record output

Person - 123456:  Facility A Visits-Total; Facility B Visits-Total... Facility N... Total Visits

Person - 234567:  Facility A Visits -Total; Facility B Visits - Total... Facility N... Total Visits

...

...

Person - N

I'm sorry, but we're just not communicating.

You can see your data. You understand what it means. I cannot. I do not know how to manage your data because I don't know what your data looks like.

If you would be willing to post two or three rows of sample data, and/or a real world description, I'll be glad to try to help; but at the moment I'm dead in the water.

Disregard (I'm thinking this out... ) unless I'm right.

John, (I cannot manually enter 10,000 UniqueID "A," "B," or otherwise.. so SQL is forbidden, right? )

Thank you for replying. My first ProductTable was in fact as you have described, except for the fact that the 10,000 unique records (tall and thin) became 14,000 not-so unique records... so there must be something in the SQL that I'm overlooking.

Shame on me: I'm not intimately familiar with Unions, beyond the general concept (so a bit fuzzy on what to expect in appearance), however, I  presume the SQL above is a summation proxy or formula, because I can capture the raw information without SQL.

You do not need 10000 tables.

You do not need 28 tables.

You need 3 tables!

One table will have 10000 records, one per unique ID. It can be populated using an Append query based on a SELECT DISTINCT query on your spreadsheet.

Another table will have 28 records, one for each of what you're calling a "record" - though it is far from clear to me what that might be. Let's call its primary key RecordID.

The third table will have one record for each unique combination of unique ID and your "record". I suspect that this table can be populated using a "Normalizing Union Query" based on your spreadsheet, something like

SELECT spreadsheet.UniqueID, "A" As RecordID, [A] As RecordValue

FROM spreadsheet WHERE [A] IS NOT NULL

UNION ALL

SELECT spreadsheet.UniqueID, "B" As RecordID, [B] As RecordValue

FROM spreadsheet WHERE [B] **** IS NOT NULL

UNION ALL

<for all 28 columns>

You may be in the spreadsheet mindset that you have only one grid of many columns crossed with many rows. That is NOT how relational databases work! Instead they're "tall and thin", with few fields and many rows.

Possible to post a more comprehensible example of your data, such as two or three rows of your spreadsheet or a nonconfidential description of the real world data that it models?

Ken, (summary values - yes!)

I see what you mean by decomposition. I can't run the program you've posted due to errors in decompiling, I presume.

But let me run the ideas I have going around in my head.

The excel file has about 10,000 unique IDs matched with 28 known records.

(of course it's recorded as 14,000 records, with 28 fields)

It is not possible to create 10,000 tables, nor should I have to

I have created 28 tables but that is what I have in excel already.

If I could import the 10,000 unique IDs as fields, then I should be able to list the 28 records and populate them... wrong approach... requires double entry  

Shouldn't be this complicated... and we should be able to state the solution in finite steps.  

Excel file is brought into Access as a table that states:

Id = 123

field A = 4

Id = 123

field A = 5

Possible to state solution in finite steps?