Excel formatting for Access Import

vapid2323 · Nov 12, 2010

Hey guys, I have found a solution for an import issue I was having with Excel to access but I am finding a flaw, where I am unsure if there is an easy fix.

We have a excel document that is a form for QA to use when auditing a facility out in the field, each excel form is then emailed into the main office where I can access the data and import it into Access.

The form has been created to make it user friendly for the field staff, meaning that we have only a few columns and the user enters data from top to bottom (about 70 questions in all)

I created a hidden tab that reformats the data so it all shows up in an easy to import format, so instead of having all the questions show up in rows they now show up in column. (See the attached file to get a better idea)

My issue is that this form is getting changed a lot more than was planned and I have to go into the document and update everything every week!! And that is causing me problems in access as well since I have to reformat the tables >.<

I am looking for any tips on how to modify this setup in order to prevent all this extra work.

HiTechCoach · Nov 13, 2010

vapid2323 said:
My issue is that this form is getting changed a lot more than was planned and I have to go into the document and update everything every week!!

If the Excel file keeps changing then there will always be extra work.

vapid2323 said:
And that is causing me problems in access as well since I have to reformat the tables >.<

Why should you need to reformat any table design? When properly normalized you should be able to just add data and records without adding any fields to any tables.

I created a hidden tab that reformats the data so it all shows up in an easy to import format, so instead of having all the questions show up in rows they now show up in column. (See the attached file to get a better idea)

Unfortunately this is normally a huge read flag to normalization issues if this is done to match your table design.

vapid2323 · Nov 15, 2010

HiTechCoach said:
If the Excel file keeps changing then there will always be extra work.

Why should you need to reformat any table design? When properly normalized you should be able to just add data and records without adding any fields to any tables.

Unfortunately this is normally a huge read flag to normalization issues if this is done to match your table design.

Thank you for your feedback sounds like I have a lot to learn!!

What direction should I go in order to resolve this, I can do the research but a kick in the right direction will help

Should I be looking at how I import data from excel? You make it sound like I dont need to update my "DATA" tab... Should I be pulling records directly from the same tab the users work with?

Questions are being completely removed and some are being added at this point, how can I normalize that will allow this?

I did find the following, is this the path I should follow?
http://www.access-programmers.co.uk/forums/showthread.php?t=36545

vapid2323 · Nov 15, 2010

As I feel that this question is not only related to Excel I have created another topic dealing with Normalization at http://www.access-programmers.co.uk/forums/showthread.php?p=1016173#post1016173

HiTechCoach · Nov 15, 2010

Questions are being completely removed and some are being added at this point, how can I normalize that will allow this?

Adding questions hold be a matter of adding data (records) Each question's answer is a record, not a field. You would use an additional field to identify the question.

So adding a new question would be a matter of adding a record to a lookup table.

If a question has ever been used, you really should not delete it from the master lookup list. I would just mark it some was as no longer active.

Should I be looking at how I import data from excel? You make it sound like I dont need to update my "DATA" tab... Should I be pulling records directly from the same tab the users work with?

That is correct. The tab the User are using should more closely match the records in your table.

Here is an example that is will shows a method I would modify for importing your data:
Import Data from Microsoft Excel

You will need to modifiy the way it stores the data to match your database.

I did find the following, is this the path I should follow?
http://www.access-programmers.co.uk/forums/showthread.php?t=36545

Yes. The-Doc_Man's post is very close to what I would recommend.

Excel formatting for Access Import

vapid2323

Scion

Attachments

HiTechCoach

Well-known member

vapid2323

Scion

vapid2323

Scion

HiTechCoach

Well-known member

Similar threads

Users who are viewing this thread