[thecoolest2009]

I am using Windows XP with Office 2007

I have two separate tables that I imported into Access that include student data for an entire school district. Each table includes some similar data (state test scores, suspensions, attendance, ethnicity,etc.), but most is different.Table 1 holds data for every student in the school (1300 students). Table 2 holds data for students that took an ESL test (300 students). The column headings are mostly different and I don't want to change them because there are too many.

I would like to use Access to combine the two tables and make one giant data source. For example, I want to take all of the columns from Table 2 that don't exist in Table 1 and add them.

Here is what I've done so far and haven't succeeded. Each table has a unique student ID that I use as the primary key. I also use the ID to create a relationship between the two tables.

I've run queries and I lose students and end up with 300 students. Which means that the query is only showing data that appears in both tables (I think). The goal is to have 1300 students with the table 2 columns added.

Trying to append, I keep getting an error message that tries to get me to rename columns so that both table have matching column titles. As stated before, I don't want to do this.

I may not need to run a query. I am really not sure how to do this...PLEASE HELP!

[OBP]

The whole point of a "Relational Database" is to not have "one giant data source", but related data in separate tables joined by unique data like your StudentID, with no duplication in the tables.
So why do you want to put all the data together?
You just use a query to "Join" the data for Viewing and Searching purposes.

[thecoolest2009]

Thanks for your response!

I work for one of the largest school districts in the US. Our main data source is a DOS based program that can only generate reports for specific areas-Special Ed, ESL, General Ed, Attendance,Regents exams, etc. Of course somebody has to justify their job, so they will not create one report that includes all student data.

Unfortunately, most of my 300 schools do not have the technical know-how to link one report to another using MS Excel (VLOOKUP, HLOOKUP,etc), and therefore, they print out multiple reports and use a hi-lighter and calculator to cross-reference and determine student performances. This is obviously extremely time consuming. Since I can't train 300 hundred schools, and I do not have the time to use Excel's basic formulas to merge reports, I was hoping for an easier solution. Furthermore, if I can merge all the data into spreadsheet, it would be very helpful for my schools. Access might not be the answer. Do you have any suggestions?

[OBP]

Access is the perfect answer, you can import as many reports as you like and then combine the data in queries.
If you need help doing so, I cna do it for you.

[thecoolest2009]

Wow, I am very impressed with the immediate responses!! Thank you!

So far this is the only problem I have.

Table 1 includes a list of every student in a school. It includes home language, admit dates, ethnicity, etc. The current number of students in table 1 is 1300.

Table 2 has only the students that took a state ESL exam. It includes only the students that took the exam and the break down of their scores. There are 300 students in the report.

When I attempt to merge the tables, I either get 300 students or 1000 students. It appears to me that the merge only includes matched or unmatched records.

What do I have to do first to remedy this issue?

[OBP]

Click on the table Join and change it to
All records from the main table and only those matching from the Exam table

[thecoolest2009]

I don't think that I understand what you are saying. Click on the table-Table 1 or table 2? Where am I clicking on it? In the tables pane on the left or while the table is open on the right?

Thanks!

[OBP]

You click on the Line between the 2 Tables, see the attached Excel sheet for a printscreen.
There are 3 options available.

[thecoolest2009]

Thank you, It Worked!!!

Have a great day!

[OBP]

Well done, let me know if you need any more assistance.