I've been out of the IT Business for several years and have come out of retirement to work with a local community college on Training Grants. We collect specific pieces of data on each student that works for the company that we have the state grant for. It is becoming a nightmare to track everyone. Currently we have 10 grants (each with a unique grant number) and some of the grants have 2,000 or more students. Each grant has a course number and name (course number not unique), number of hours of the course. Each student record has: SSN#, name (first initial last), birthdate, city, state, zip, child under 18 (y or n), vet status (Y or N), exit status (number, i.e., 20 -Completed - Successful, 210 Complete-Not Successful, etc.) NOTE: For someone to achieve the status of 20, they have to have completed ALL their assigned training course (which can be many different ones) and remain on the payroll for at least 90 days to be counted.
Additional data collected is: exit date, hire date, term date, course number (not unique), course name they took, start date of that course, end date of that course, hours completed for that course, total hours for that course, employer name, employer taxid and zipcode.
I will need to generate report showing all students for a company, all training they have had, training status, billing report for each company showing who has completed their 90 days, a duplicated and unduplicated report (a student can have multiple courses and will need to be counted for the duplicated but only counted once for the unduplicated)
I know this is long, but I am needing so advice as to the best way to layout the database to give me maximum flexibility and performance.
The data is collected in an Excel 2007 spreadsheet and will need to be transferred to Access 2007. There is a spreadsheet for each company and new students are added to the bottom as well as any adjustments to existing students.
Thanks in advance for any suggestions or help.