Access 2013 One record from many with same key

Access is not a spreadsheet. You are taking the wrong approach here. If you have multiple records in your Excel table that apply to the same person, then you have a 1 to many relationship. This means you need to create at least 2 tables in Access. A parent table to contain the data that is common to all records, with one record per ID, and a child table with the info that may be different, the many side with the ID as a foreign key to link the 2 together.

As Scott has explained, you need to decompose the table into a set of correctly normalized related tables.  This is done by firstly designing the tables required, and then executing a series of 'append' queries to insert the data from your current 'master' table into the relevant new tables.  This must be done in the correct sequence, inserting rows firstly into the referenced tables, those on the 'one' side of a one-to-many relationship type, and then into the referencing tables, those on the 'many' side.

You'll find an example, which illustrates the process step by step, with an explanation of each step, as DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

Ken,

I see that this particular question has unique Identifiers with unique records. What if (in my case) the excel records, that I've imported into an Access 2013 table have different records for the same Identifier; how do I parse and process these records so that I may eventually tally the data in each (identical and not) field?

AB

Just to be as clear as possible, in Access, we don't speak of cells, but instead of Fields, and Records; however, when a single field has multiple data, that require multiple cells in the same record... how do we parse this; with macros (if A1 is filled, then populate A2, if A1 and A2 is filled, then populate A3) and a 'catcher,' or second table set up for multiple fields, A1, A2, A3?

Have you looked at my demo?  The simple Excel worksheet which it uses, and consequently the MasterTable into which the data is imported, also has 'different records for the same Identifier', the identifier in my case being the combination of FirstName and LastName, which is not a satisfactory key in real life as personal names are not distinct, but is used as such for simplicity in my demo.

It's not a question of 'parsing' but of 'decomposition' (or more strictly speaking in the language of the database relational model 'non-loss decomposition') which is the mechanism by which a non-normalized table is replaced with a set of normalized tables.

Once you have a set of correctly normalized related tables it is then simply a question of building queries in which the relevant aggregation operator are used, COUNT, SUM etc, to return whatever summary values you wish.

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?