CODEWITHSUNDEEP
sqlms-accessms-access-2010ms-office
Macellaria
Macellaria

Reputation: 71

How do I normalize data when an external program writes several records into one record?

I am using an external program that writes onto Access. This program collects data from an electronic form and writes all the data from the submitted form onto my Access database. However, the issue that has arisen and caused a lot of issues and slowed down our database, is that when it writes to my database, the data is not normalized.

The form looks something like this

Name: John Doe
DOb: April 1 1950
SIN: 123456789
Marital Status: Married
Phone: 123456789
Email: [email protected]

Then it writes everything on the form as one record using the Question as the field name and the entered data as the data. Something like this: 

Name    | DOB          |  SIN     | Marital_Status | Phone    | Email
John Doe| April 11 1950| 123456789| Married      | 123456789| [email protected]

See this isn't much of an issue with the example form here, however, we have forms with about 100 questions which which we end up with a table with fields like:

Name|Date|Weather|Question1|Question2|Question3|Question4|...|Question100

.... and so forth.

As a noob, what I have done thus far was using the union sql query to manipulate the data so that it reads:

Name|Date|Weather|Question1
Name|Date|Weather|Question2
Name|Date|Weather|Question3
Name|Date|Weather|QuestionN

I have been able to get by with this but it is seriously slowing down my database and now I am having other issues.

How can I normalize this data when the external program writes data like this? I don't get to manipulate how the program writes to my Access Database.

Upvotes: 0

Views: 43

Answers (2)

Jerrad
Jerrad

Reputation: 5290

Access 2010 has a feature called event-driven data macros, which are similar to triggers in other database systems. I don't personally have any experience using them, but it looks like you should be able to create an After Insert macro that will run when a new row is inserted. Within that macro you could split your questions up and insert them into a more normalized table (which you would then use to report off of).

Upvotes: 3

Erik A
Erik A

Reputation: 32632

You're doing it correctly, a union query is indeed the correct way to normalize a denormalized table. However, consider storing it normalized in addition to denormalized, so you can actually work with the data without having Access executing 100 queries every time you want to access your data. And consider splitting Name|Date|Weather to a different table, since you are repeating them 100 times per question.

You can store the union query result in a table by simply doing SELECT * INTO MyTable From UnionQuery. Combine the import from the other program with this query in a macro.

Obviously, this is not ideal. The ideal fix would be to manipulate the external program to not denormalize the data in the first place

Upvotes: 1

Related Questions