Converting large flat file spreadsheet into Access - need query to populate table

Question

I have a large flat file of data (from Excel) that I am trying to convert into Access.  A particular problem I'm having is writing a query that will populate a table that represents a many-to-many relationship.

What I currently have  is:  tblProfitCentresNPS (this is the many to many table that needs to be populated) and the fields within this are Respondent (RespondeeID lookup from tblRespondees), NPSScore (NPSID from tblNPScore), ProfitCentre (ProfitCentreID from tblProfitCentres), Comments

The flat file contains the following data that needs to populate the above tblProfitCentreNPS table

Respondee, Profit Centre, Profit Centre NPS Score, Profit Centre Comments. 

This table contains multiple responses per Respondee but on one line - so the fields are PC1, PC2, PC3 etc for each profit centre, and similarly headed for score per profit centre, and comments per profit centre.

I am trying to work out a query that will pull over the relevant values per respondent and create a record for each profit centre response per respondent.

I am having difficulty with the joins as there as so many tables involved, but I need to convert the original values into look-up values.

Really a bit stuck....

All advice, gratefully received.

Thanks,

Tonia

Answer 1

On my website are several samples of normalizing repeated columns, such as you are describing. 

• NormalizingRepeatingColumns1 (beginner)

Document Included

This sample demonstrates how to normalize a simple PC_Inventory table.  Its purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.

• NormalizingRepeatedColumns2 (beginner)

Document Included

This sample demonstrates how to normalize a PC_Inventory table that has two sets of repeated columns.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.

• NormalizingRepeatedColumns3 (beginner)

Document Included

This sample demonstrates how to normalize a table that has repeated Yes/N0 columns.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.

• NormalizingRepeatingColumnsVBA.mdbDocument Included

This sample demonstrates how to normalize a table that has repeated columns with VBA.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet with code rather than SQL statements.

Answer 2

Sorry, unable to post the data - commercially sensitive etc.  I will take a look at Ken's example and come back if I have more questions.

Thanks

Tonia

Answer 3

I will take a look at this.  Sadly the work firewall won't let me access this site, but I will take a look once I am home.

Thanks.

Tonia

Answer 4

There is a small file, DecomposerDemo.zip, which illustrates how to decompose a non-normalized table imported from Excel, in my public databases folder at:

http://cid-44cc60d7fea42912.skydrive.live.com/redir.aspx?resid=44CC60D7FEA42912!169

It uses a simple structure of contacts who may have one or more employees, and by means of a series of append queries inserts the data into a set of normalized tables.

Answer 5

Post sample data of the flat file.