Rusty and Need Help

20 pts.
Tags:
Access 2007
Excel 2007
Microsoft Access
Microsoft Excel
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. Bluize

Answer Wiki

Thanks. We'll let you know when a new response is added.

You need to have a student master with all relevant details and student number as a primary key, a course master with a unique course number (has to be unique key) – so course no. is the primary key, a company master with company no. or ID as primary key, a grants master with grant ID as a primary key. There will be a separate transactions table to take care of all transactions to take care of – grants allocated to students, student allocated to course, student – company relationship, days on a course, completed/ not completed flag, billing details.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bluize
    I guess I should have been more specific in my descriptions. Here is what data I have to work with in a master spreadsheet that we have to fill-in for the state on each grant (same pieces of info is collected for each company): There is a grant or contract number for each grant (this is unique for that grant There are a list of courses that the grant covers (which consists of the CIPCODE (not unique) and Course Name (unique for that company)), start and end dates of the grant, number of hours for each of the courses... Side Note: (we can have the same CIPCODE in one grant that may be related to "Communication Skills for Supervisors" and in another grant the exact CIPCODE may refer to "Communication Skills for Call Center Employees" or the same CIPCODE different course name within the same grant. The actual student record consists of many fields which include such items as, SSN, name, address, city, state, job title, training status (20=completed their 90 days, 21=completed all training, but not 90 days, etc.),hire date, term date,cipcode of class taken, class name, class start date, class end date, hours completed of class, class length, employer, employer taxid. Not captured but info I have access to from working with the company to decide who gets what training is a matrix showing that say a "Truck Driver" gets the following courses - Defensive driving, customer communication skills, basic computer concepts, dealing with difficult customers. I want to be able to generate a report that will indicate all the training each employee should get and how much they have had YTD. So I would want to include this in a table to be able to link a student to his/her required training. I hope this helps and thank you to Jaideepkhanduja for input so far. Bluize
    20 pointsBadges:
    report
  • Jaideep Khanduja
    I think the desired report is very much possible – what you want (and the source from your data) Employee (name), training opted for (CIPCODE of class taken), Start date, end date, training status
    9,100 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following