Access 2013 One record from many with same key

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?

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.

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.

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

Thank you very much. That helps. Just one question: in the line

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

is

"Person - 123456 - Visits 4"

in one spreadsheet cell and

"Person - 234567 - Visits 5"

in the next cell over?

Or is

"Person - 123456 - Visits 4; Person - 234567 - Visits 5; Person - 345678 - Visits 2..."

just one long text string in a single cell?

Either way can be managed but of course the code/query would be different.

Since it appears that the number of visits in a single spreadsheet row is variable and unpredictable, I expect that VBA code will be needed in either case.