Okay, but you've cut out the fields that actually have the survey results, right? Give me a couple of examples of the Q's and A's, please.
Code:
Participant Name
Participant Email
Company
Invitation Date
Reminder1 Date
Reminder2 Date
Feedback Date
Primary Addr Country Name
Primary Phone Number
----------((5 invisible fields))
Recommend score
----------((7 invisible fields))
Survey Month
----------((9 invisible fields))
# of Surveys Taken
Common Respondent
Fiscal Year
Common Respondent According to FY
All right, here's an ultra-high-level description of the process:
Before you start, you'll group the data columns into meaningful groups -
* data about the participant, name, company, country
* data about a particular survey itself (like dates, survey month),
* data that is answers to the questions, and
* data that represents analysis of other data (# of surveys taken, common resp).
You'll also have to decide if you have enough respondents from the same company and/or address that you want to have a company-level record. You know your data, I don't.
Then, you'll begin the import process.
First, you'll do some setups and import your excel data into a temp table.
Next, you'll massage the Participant data to identify and eliminate duplicates, without losing the ability to connect the deduped data back to the individual rows of data.
Then, you'll move the survey questions/answers off into a table of their own, retaining the links back to their unique row.
Finally, you'll verify that you can replicate the analysis information from the actual data. If so, you'll be ready to delete all the redundancies and put the database into action.
Okay, here's the high-level description of importing the data, down to breaking out the participant data -
1) Before starting, create a play version of your excel workbook and change the column name in the top of each column to what you really want each field to be named. Short, simple, distinct, no spaces in the name. Add a Rowkey field in column A, with = row() as the value.
If you have more than 255 columns of data, there will be special work to get the last few. Let me know and I'll describe that too.
2) From Access, in a new blank "play" database, import the worksheet into a working table
Code:
tblTempSurvey
RowKey
PartName
PartEMail
PartCompany
(etc)
3) Create an empty table with this structure. It should include every field that has to do with the participant, but nothing that has to do with individual surveys or summary or analysis:
Code:
tblTempParticipant
TempPartID (autokey)
RowKey
PartName
PartEmail
PartCompany
PrimaryAddr
PrimaryPhone
(etc)
Run an SQL INSERT query similar to this code to copy the participant data into that table
Code:
INSERT INTO tblTempParticipant
FIELDS (RowKey, PartName, PartEmail, PartCompany, ... other fields...)
(SELECT TTS.RowKey, TTS.PartName, TTS.PartEmail, TTS.PartCompany, ... other fields...)
FROM tblTempSurvey AS TTS)
4) Next, You'll do analysis on that table. For instance, see how many data problems you have with people changing email addresses, changing companies, two different names using the same email address, and so on. Here's a sample query for the same person with two different email addresses.
Code:
SELECT T1.PartName, T1.PartEmail, T2.PartName, T2.PartEmail
FROM tblTempParticipant AS T1, tblTempParticipant AS T2
WHERE T1.PartName = T2.PartName
AND T1.PartEmail <> T2.PartEmail;
Do the analysis first, to see if you have BIG problems. If you've only got onesies and twosies, then you can proceed. On the other hand, if you have LOTS of weird stuff regarding email multiple email addresses and people switching companies, then the design will need some tweaking before you go to the trouble of importing.
5) If all is well, then we'll end up creating a little translation table with the from and to TempPartIDs for the survey.
Windows 7 64bit 
Access 2010 64bit
Reply With Quote
