If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
HELP A NEWBIE - EXCEL TO ACCESS
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 |
#3
|
|||
|
|||
HELP A NEWBIE - EXCEL TO ACCESS
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 . |
#4
|
|||
|
|||
HELP A NEWBIE - EXCEL TO ACCESS
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 . |
#5
|
|||
|
|||
HELP A NEWBIE - EXCEL TO ACCESS
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 . |
#6
|
|||
|
|||
HELP A NEWBIE - EXCEL TO ACCESS
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 . |
Thread Tools | |
Display Modes | |
|
|