Access Import From Excel 07.09.08
This project involves developing a project that will import information from an Excel 2003 file and append information from the file into two tables in an Access Database.
This is a master program for a school district. Each of the schools in the district will have their own desktop application where they will enter student infractions. At the end of the year, they will generate an excel file like the sample included. Their file will contain a single school year (like 2008-2009) and not several as is the case with the sample.
At the beginning of the process, the user will be able to browse to the location of the Excel file.
I want the process you develop to do two basic things.
Student information will be imported into the Students table. You will need to get a unique list of students from the file based on the StudentID. (There are 533 infractions, but only 19 students. These 19 names and information will be added to the Students table. If a student is already in the table (based on the StudentID number, that student isn’t imported.)
Infraction information is appended to the Offenses table. Before the import, your process will check to see if infractions with the same School Name and School year are already in the table. If these records exist, the user will get a message.(”You already have records for [SchoolName] for the [SchoolYear]school year. Do you want to remove these records and replace them with records from your import?”
If the answer is yes, records for that school and school year are deleted from the table and the records from the Excel file are appended. If the answer is no, the process stops.
Users who will be executing this process are nontechnical and the process should be logical enough so that anyone can follow it and the imports will be success each time. The structure of the table and the structure of the Excel file will always be the same so no allowances have to be made here.
Please examine the Access Database and the Excel file carefully before you bid. The tblDTMasterExport is only there so you can look at the two queries to see which information goes where.
Please ask any questions you may have. I’d be glad to provide any information you need.
