Extracting data from tables

[mathteacher]

I'm trying to import Excel tables into Access and extract specific data from each table and compile into one table.

For example, here are the tables:

Table 1: Student Roster with student names, phone numbers, school they attend, etc.

Table 2: List of Chicago schools that contain phone numbers along with names and email addresses of school employees

Table 3: A list of non-Chicago Schools with employee info

Table 4: Private Schools with employee info

Table 5: School semester Dates

I want to be able to extract a student's name and school name, then match the school name with a school on Table 2, 3, or 4 and extract pertinent data for that school, and then extract the semester dates, from Table 5. Then, I would like to create a report with all this info for each student.

The tables are constantly changing, so I would have to be importing them over and over as they are updated.

This could save me tons of time if I knew how to do this!

Can anyone help?

Thanks!

 

[SydneyGeek]

You may find it simpler if you set it up like this:
Table 1 -- as before (tblStudents). StudentID is Autonumber
Table 2, 3 and 4 -- combined to one table, as they all have information about schools. (tblSchools). Differentiate with a Category field (Chicago, Non-Chicago, Private). SchoolID is Autonumber
Table 3 -- as your original Table 5 (tblSemesters). 2 fields, SemesterID (Autonumber) and Semester (text)
To pull the data together you will need a fourth table -- tblEnrolments. This has EnrolmentID (Autonumber), StudentID (Number), SchoolID (Number) and SemesterID (Number). This table can hold all possible combinations of student, school and semester. Your reporting queries will be based around this table.

This is the first step -- more detail as you get further into it.

here is a link with some good structures and strategies.

Denis

 

[mathteacher]

Thank you, Denis!

I had thought about combining the tables, as you suggested, but I wasn't sure how easy that would be since the tables are constantly changing. In addition, the school tables contain different columns so they don't match up exactly.

Another question, do you know if Excel is capable of doing any of this without using Access?

One more bit of info, I know NOTHING about Access and very little about Excel. I will tinker with your suggestions and probably come back with more questions. )

Thanks, bunches!