HELP A NEWBIE - EXCEL TO ACCESS

Hi Everyone:

I'm new to Access and I need help automating a process to save me a ton of
time.

I'm a math department chair and I have a database of student information
such as marking period grades and exam scores. It's a very simple database
and every year I import all of the 6-12 students as new records with the same
fields, but just a different school year.

Anyway, all of the students have a unique id number in the district.
Teachers send me spreadsheets of data with the students names, id number and
then different columns of assorted data. I now have to update the records in
the main table from these spreadsheets. I have been first importing the data
into a new table from the Excel spreadsheet, and then once the new table is
created I create an update query for each teacher. Is there an easier way? I
have 24 teachers and this takes about 15 minutes or so each. I would really
like to know if there is another way, and also if there is a better way to
gather information from teachers than Excel. They like to add information
from home and so there is no access from home to the database.

Thanks for the time,

Bill

If you can standardize the excel file the teachers use so it has the same
columns you could automate the process to read in all the excel files.
Excel is one of the best file formats to use.

Example:
- you put all the spreadsheets in a directory
- you press a button to read in all the results

If it's taking you 6 hours each time the entire process could be automated
in less time than that (if you can get
consistant columns in the excel spreadsheets.

HTH,

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

"BillT" wrote in message
...
Hi Everyone:

I'm new to Access and I need help automating a process to save me a ton of
time.

I'm a math department chair and I have a database of student information
such as marking period grades and exam scores. It's a very simple database
and every year I import all of the 6-12 students as new records with the
same
fields, but just a different school year.

Anyway, all of the students have a unique id number in the district.
Teachers send me spreadsheets of data with the students names, id number
and
then different columns of assorted data. I now have to update the records
in
the main table from these spreadsheets. I have been first importing the
data
into a new table from the Excel spreadsheet, and then once the new table
is
created I create an update query for each teacher. Is there an easier way?
I
have 24 teachers and this takes about 15 minutes or so each. I would
really
like to know if there is another way, and also if there is a better way to
gather information from teachers than Excel. They like to add information
from home and so there is no access from home to the database.

Thanks for the time,

Bill

That sounds exactly like what I need Mark! So I could send out an Excel sheet
with each teachers classes and the same column headers and they could just
email me back. Could you point me toward a "How To"?

Bill

"Mark Andrews" wrote:

If you can standardize the excel file the teachers use so it has the same
columns you could automate the process to read in all the excel files.
Excel is one of the best file formats to use.

Example:
- you put all the spreadsheets in a directory
- you press a button to read in all the results

If it's taking you 6 hours each time the entire process could be automated
in less time than that (if you can get
consistant columns in the excel spreadsheets.

HTH,

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

"BillT" wrote in message
...
Hi Everyone:

I'm new to Access and I need help automating a process to save me a ton of
time.

I'm a math department chair and I have a database of student information
such as marking period grades and exam scores. It's a very simple database
and every year I import all of the 6-12 students as new records with the
same
fields, but just a different school year.

Anyway, all of the students have a unique id number in the district.
Teachers send me spreadsheets of data with the students names, id number
and
then different columns of assorted data. I now have to update the records
in
the main table from these spreadsheets. I have been first importing the
data
into a new table from the Excel spreadsheet, and then once the new table
is
created I create an update query for each teacher. Is there an easier way?
I
have 24 teachers and this takes about 15 minutes or so each. I would
really
like to know if there is another way, and also if there is a better way to
gather information from teachers than Excel. They like to add information
from home and so there is no access from home to the database.

Thanks for the time,

Bill

.

Check out the transferspreadsheet method and you could automate that where
you wouldn't have to do a thing. Just Google Transferspreadsheet.
--
Milton Purdy
ACCESS
State of Arkansas


"BillT" wrote:

That sounds exactly like what I need Mark! So I could send out an Excel sheet
with each teachers classes and the same column headers and they could just
email me back. Could you point me toward a "How To"?

Bill

"Mark Andrews" wrote:

If you can standardize the excel file the teachers use so it has the same
columns you could automate the process to read in all the excel files.
Excel is one of the best file formats to use.

Example:
- you put all the spreadsheets in a directory
- you press a button to read in all the results

If it's taking you 6 hours each time the entire process could be automated
in less time than that (if you can get
consistant columns in the excel spreadsheets.

HTH,

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

"BillT" wrote in message
...
Hi Everyone:

I'm new to Access and I need help automating a process to save me a ton of
time.

I'm a math department chair and I have a database of student information
such as marking period grades and exam scores. It's a very simple database
and every year I import all of the 6-12 students as new records with the
same
fields, but just a different school year.

Anyway, all of the students have a unique id number in the district.
Teachers send me spreadsheets of data with the students names, id number
and
then different columns of assorted data. I now have to update the records
in
the main table from these spreadsheets. I have been first importing the
data
into a new table from the Excel spreadsheet, and then once the new table
is
created I create an update query for each teacher. Is there an easier way?
I
have 24 teachers and this takes about 15 minutes or so each. I would
really
like to know if there is another way, and also if there is a better way to
gather information from teachers than Excel. They like to add information
from home and so there is no access from home to the database.

Thanks for the time,

Bill

.

You'll find various code examples for importing data from EXCEL into ACCESS
here, including importing multiple workbooks / worksheets:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm
--

Ken Snell
http://www.accessmvp.com/KDSnell/


"golfinray" wrote in message
...
Check out the transferspreadsheet method and you could automate that where
you wouldn't have to do a thing. Just Google Transferspreadsheet.
--
Milton Purdy
ACCESS
State of Arkansas


"BillT" wrote:

That sounds exactly like what I need Mark! So I could send out an Excel
sheet
with each teachers classes and the same column headers and they could
just
email me back. Could you point me toward a "How To"?

Bill

"Mark Andrews" wrote:

If you can standardize the excel file the teachers use so it has the
same
columns you could automate the process to read in all the excel files.
Excel is one of the best file formats to use.

Example:
- you put all the spreadsheets in a directory
- you press a button to read in all the results

If it's taking you 6 hours each time the entire process could be
automated
in less time than that (if you can get
consistant columns in the excel spreadsheets.

HTH,

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

"BillT" wrote in message
...
Hi Everyone:

I'm new to Access and I need help automating a process to save me a
ton of
time.

I'm a math department chair and I have a database of student
information
such as marking period grades and exam scores. It's a very simple
database
and every year I import all of the 6-12 students as new records with
the
same
fields, but just a different school year.

Anyway, all of the students have a unique id number in the district.
Teachers send me spreadsheets of data with the students names, id
number
and
then different columns of assorted data. I now have to update the
records
in
the main table from these spreadsheets. I have been first importing
the
data
into a new table from the Excel spreadsheet, and then once the new
table
is
created I create an update query for each teacher. Is there an easier
way?
I
have 24 teachers and this takes about 15 minutes or so each. I would
really
like to know if there is another way, and also if there is a better
way to
gather information from teachers than Excel. They like to add
information
from home and so there is no access from home to the database.

Thanks for the time,

Bill

.

Save all files to a directory first then
You would need some code that loops thru the files in a directory and then
use the transferspreadsheet method to do the importing for each filename and
then use an append or update query or both to move the data from your import
table into your real table.

Sometimes you might need to cleanup the import table a bit (extra rows with
blanks that you don't want etc...).

With excel you can also use a subset of the columns and it will still work.

It looks like the examples Ken pointed you to would help you make the code.

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com


"BillT" wrote in message
...
That sounds exactly like what I need Mark! So I could send out an Excel
sheet
with each teachers classes and the same column headers and they could just
email me back. Could you point me toward a "How To"?

Bill

"Mark Andrews" wrote:

If you can standardize the excel file the teachers use so it has the same
columns you could automate the process to read in all the excel files.
Excel is one of the best file formats to use.

Example:
- you put all the spreadsheets in a directory
- you press a button to read in all the results

If it's taking you 6 hours each time the entire process could be
automated
in less time than that (if you can get
consistant columns in the excel spreadsheets.

HTH,

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

"BillT" wrote in message
...
Hi Everyone:

I'm new to Access and I need help automating a process to save me a ton
of
time.

I'm a math department chair and I have a database of student
information
such as marking period grades and exam scores. It's a very simple
database
and every year I import all of the 6-12 students as new records with
the
same
fields, but just a different school year.

Anyway, all of the students have a unique id number in the district.
Teachers send me spreadsheets of data with the students names, id
number
and
then different columns of assorted data. I now have to update the
records
in
the main table from these spreadsheets. I have been first importing the
data
into a new table from the Excel spreadsheet, and then once the new
table
is
created I create an update query for each teacher. Is there an easier
way?
I
have 24 teachers and this takes about 15 minutes or so each. I would
really
like to know if there is another way, and also if there is a better way
to
gather information from teachers than Excel. They like to add
information
from home and so there is no access from home to the database.

Thanks for the time,

Bill

.