how to approach this task any ideas more than welcome thanks

[AWilderbeast]

Hi, ive been asked to provide a solution, for an electronic spreadsheet be sent out via email then returned by customers, once filled in for all the data to be collected onto one sheet that looks like the attached sheet. the easiset way i can see is to not use a spreadsheet but to use a datbase instead and just put it in the desired format, how easy is it to import mutliple spreasheets into correct fields on a dbtable thanks for any input or ideas

Al

 

[Bilbo_Baggins_Esq]

"Easy" is a term relative to the programmers knowledge level including a time function.

Importing and exporting basic spread sheets to/from Access is not terribly difficult, however, formating exported spread sheets can be time consuming within Access.

If you want to house the data in Access, I would actually consider doing this with VBA from within Excel and then export from Excel to the Access database for storage of the raw data (assuming you need to house the data).

Others may have a different approach, but I've had much more ease in formating Excel Sheets, with VBA from within Excel.

 

[WayneRyan]

Al,

I'd also like to add that as you import the returned spreadsheets you're
gonna have major headaches when you see the range of responses for
a "Yes" field --> you'll have "X", "Y", "YES", "Yes" and whatever else
people can come up with.

If possible, it would probably be easier to send them a very small
Access app that could more easily control the data AND make it
easier to import into Access and append to your table(s).

Wayne

 

[boblarson]

I'd also like to add that as you import the returned spreadsheets you're
gonna have major headaches when you see the range of responses for
a "Yes" field --> you'll have "X", "Y", "YES", "Yes" and whatever else
people can come up with.

I might make an observation about this comment. This doesn't have to be true. You can set validation on Excel cells so that if you want a YES / NO, etc. you can use Validation (in Excel - select your cells then DATA > VALIDATION) to give them a combo to select the value you want them to use for a certain type of response. You can also validate for a date, length of text field, etc.

Then, importing won't be such a hassle.