Access 2013 One record from many with same key

Anonymous
2014-04-30T19:33:12+00:00

So here is the problem: I have an excel file of

records. Among these records are some that belong to the same person,

different data. Because each person is identified with their own Id,

i.e, unique key, I am having an issue placing the data from two or more

records belonging to the same person in a single record for a data

summation.

I eventually, or simultaneously want to include the synthesized record with all other records for a count of data contained within all the records.

Of course, I have imported the records into an Access 2013 table for

manipulation, but I can't create a single record with multiple data from

the multiple records with identical keys.

If anyone has a solution or requires further clarification, I would appreciate help on the project.

Additional information; the table holds fields for all the data.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

54 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2014-04-30T22:27:45+00:00

    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.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-04-30T21:34:07+00:00

    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?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2014-04-30T21:11:31+00:00

    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.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2014-04-30T20:52:12+00:00

    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.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments