Merge Excel with Access

[simon4amiee]

Question, we use an Excel sheet for Audits which have 200 questions on, I said it may be better to put on Access, cant remember the reason why something to do with formulas. Anyway, is there a way to auto enter into a database, because i had to sit and manually enter 100's of audits into a database which took weeks, sorry im not a programmer or specialist in VB, just an Analyst looking for a different approach, i can explain more but dont really know what questions to ask, hope there is a boffin on here that can help

 

[DCrake]

You can either link or import data from Excel alot depends on the structure of the workbook. Can you post a sample worksheet of the type of data you want migrate.

David

 

[RuralGuy]

You can import Excel spreadsheets into Access if that helps.

 

[simon4amiee]

Ill do my best

We use Excel for Audits, these are taken off the intranet and via macros stored on your PC when 1st open, you then complete the audit stating Area/Dept/Date/Name/Occupation plus more headings, then there are 200 questions to enter all yes no answers!

Then when the save button is pressed (button on the spreadsheet), it stores to a file into a central location and autorenamed. And it generates the results automatically for print too in the form of graphs charts etc!

We then once a year add all the audits into a database for annual figures, which we then convert back to Excel as pivot tables lol!

We are now merging 2 different audits questions, so more questions, but again instead of using access we created 1 audit entry for with all questions and created 2 separate forms which concatenate from the main one thus creating 2 audits for the work of one.

These I presume waill also want adding to a datbase!

Surely there is an easier way to this because it seems so long winded!

 

[DCrake]

So at the moment you cannot audit your history until the whole year has passed, that sucks. You could quite easily create a data capture form in Access to collect the yes/no's and store them in Access. how many records are we talking about in a year?

You could also build in a report generator which will permit year to date audit reports in stead of waiting the full year.

What type of questions are being asked? and are all people asked the same questions or do certain questions relate to certain departments/areas etc.?

You would simply have a table of questions which would be maintained by yourself incase you need to change/add/delete them. You would also have a series of lookup tables for areas/departments/etc for easy selection.

David

 

[simon4amiee]

Yep 12 sections eg Section 1 - Care Of Equipment

Q1 Does the ward have a checklist
Q2 Is the patient eqipment clean
etc etc to Q15

All with Yes No NA answers, surely forms/reports/data access pages can generate exactly whats currently on Excel and produce the reports too that are needed.

Reports/CHarts etc include

Overall %
by Section %
by area %
by unit %
by question %,
best and worst sections,

basically all the analytical charts and stats that we then use for trust figures, and when finalised and check will go on our intranet!

 

[DCrake]

Precisely, Access can generate/populate Excel spreadsheets quite happily for you. You can choose whether to calculate in Access or Excel the options are yours to decide.

What you need to do now is to sit down and draw out a plan of all the tables and fields you think you need. What data types they are going to be. What fields are primary index fields. remember to look at reserved words, naming conventions and normalisation. Look at the relationships between each table, one to one; one to many; many to many.

When you have go that far you need to then look at what you need to get off the user this will determine the style of the forms you'll need.

Finally look at the queries that will give you the analysis of the data. This will become the backbone of the application in respect to the final output, beit in Access or Excel.


David