Sorry for the delay....

"will A" wrote:
It works fine, however, what if I need the file3 to contain ALL file1 (like
an update only) even if there is a match or unmatch. Right now, the Query
will only show me matched items in file3. Thanks again.

THAT..MS Query does not do so automatically. You're asking it to do an
outer join on 2 fields: Not allowed in MS Quer, but Easily done in Access.

Anyway...since you asked for it...I worked around MS Queries limitation by
creating a virtual 'helper field' for each table, much like we use in Excel,
except these are built at runtime in SQL. The 'helper field' (CourseID) is a
concatenation of the COURSE and DATE fields.

Here's the SQL:

SELECT
Students.FNAME,
Students.COURSE,
Students.DATE,
Instructors.INSTRUCTOR
FROM
{oj
(SELECT
FNAME,
LNAME,
COURSE,
DATE,
COURSE & '_' & FORMAT(DATE,'YYYYMMDD') AS CourseID
FROM
`C:\Course_Students`.rngNameCourse) Students
LEFT OUTER JOIN
(SELECT
INSTRUCTOR,
COURSE & '_' & FORMAT(DATE,'YYYYMMDD') AS CourseID
FROM
`C:\Course_Instructors`.rngCourseInstr) Instructors
ON Students.CourseID = Instructors.CourseID}

To test, go to the Instructors list and delete some rows.
When you run the query, you should see students taking courses with no
instructors.

Does that solve your problem?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Ron Coderre" wrote:

If you really must do this and you don't mind playing with a little SQL code,
Here's how I did it using MS Query....

Using your Column Headings in 2 different Excel workbooks,
Course_Students.xls
Range named: rngNameCourse
Referring to this list:
FNAME LNAME COURSE DATE
First1 Last1 Course1 31-Jan
First2 Last2 Course1 1-Feb
First3 Last3 Course1 2-Feb
First4 Last4 Course1 3-Feb
First5 Last5 Course1 4-Feb
First6 Last6 Course1 5-Feb
First7 Last7 Course1 6-Feb
First8 Last8 Course1 7-Feb
First9 Last9 Course1 8-Feb
First10 Last10 Course1 9-Feb
First1 Last1 Course2 10-Feb
First2 Last2 Course2 11-Feb
First3 Last3 Course2 12-Feb
First4 Last4 Course2 13-Feb
First5 Last5 Course2 14-Feb
First6 Last6 Course2 15-Feb
First7 Last7 Course2 16-Feb
First8 Last8 Course2 17-Feb
First9 Last9 Course2 18-Feb
First10 Last10 Course2 19-Feb

Course_Instructor.xls
Range named: rngCourseInstr
Referring to this list:
COURSE INSTRUCTOR DATE
Course1 Al 31-Jan
Course1 Ben 1-Feb
Course1 Chuck 2-Feb
Course1 Dave 3-Feb
Course1 Ed 4-Feb
Course1 Gus 5-Feb
Course1 Harry 6-Feb
Course1 Irv 7-Feb
Course1 Jim 8-Feb
Course1 Kara 9-Feb
Course2 Al 10-Feb
Course2 Ben 11-Feb
Course2 Chuck 12-Feb
Course2 Dave 13-Feb
Course2 Ed 14-Feb
Course2 Gus 15-Feb
Course2 Harry 16-Feb
Course2 Irv 17-Feb
Course2 Jim 18-Feb
Course2 Kara 19-Feb

Next, in a new workbook....
DataGet External DataNew Database Query
-Data Source is Excel File
-Select the Course_Students.xls
-Select the rngNameCourse
-Continue through the screens, but at the end...opt to edit the query.

Click the [SQL] button to see the SQL code.

Edit the SQL to be this (edit to file location to match yours):
SELECT
rngNameCourse.FNAME,
rngNameCourse.LNAME,
rngNameCourse.COURSE,
rngNameCourse.DATE,
rngCourseInstr.INSTRUCTOR
FROM
(SELECT
rngNameCourse.FNAME,
rngNameCourse.LNAME,
rngNameCourse.COURSE,
rngNameCourse.DATE
FROM `C:\Course_Students`.rngNameCourse ) ngNameCourse,
(SELECT
rngCourseInstr.DATE,
rngCourseInstr.COURSE,
rngCourseInstr.INSTRUCTOR
FROM `C:\Course_Instructors`.rngCourseInstr) rngCourseInstr
WHERE
rngNameCourse.COURSE = rngCourseInstr.COURSE
AND rngNameCourse.DATE = rngCourseInstr.DATE

Click the [OK] button to see that data that will be returned
If the data is ok...Click the button to return data to Excel

Here's my returned data:
COURSE FNAME LNAME DATE INSTRUCTOR
Course1 First1 Last1 1/31/2005 0:00 Al
Course1 First2 Last2 2/1/2005 0:00 Ben
Course1 First3 Last3 2/2/2005 0:00 Chuck
Course1 First4 Last4 2/3/2005 0:00 Dave
Course1 First5 Last5 2/4/2005 0:00 Ed
Course1 First6 Last6 2/5/2005 0:00 Gus
Course1 First7 Last7 2/6/2005 0:00 Harry
Course1 First8 Last8 2/7/2005 0:00 Irv
Course1 First9 Last9 2/8/2005 0:00 Jim
Course1 First10 Last10 2/9/2005 0:00 Kara
Course2 First1 Last1 2/10/2005 0:00 Al
Course2 First2 Last2 2/11/2005 0:00 Ben
Course2 First3 Last3 2/12/2005 0:00 Chuck
Course2 First4 Last4 2/13/2005 0:00 Dave
Course2 First5 Last5 2/14/2005 0:00 Ed
Course2 First6 Last6 2/15/2005 0:00 Gus
Course2 First7 Last7 2/16/2005 0:00 Harry
Course2 First8 Last8 2/17/2005 0:00 Irv
Course2 First9 Last9 2/18/2005 0:00 Jim
Course2 First10 Last10 2/19/2005 0:00 Kara

That query joins the two tables (students, courses) to asign instructors for
each course/date combination.

Once back in Excel, you refresh the data by right-clicking on the queried
data and selecting Refresh Data.

I'd recommend duplicating my model to see how it works before you tackle it
with your data.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"will A" wrote:

I have 2 files EX. file1 FNAME, LNAME, COURSE, DATE and file2 COURSE,
INSTRUCTOR, DATE. How can I populate file1 with file2 to create file3 of
FNAME, LNAME, COURSE, INSTRUCTOR, DATE. Or how do Excel merge two files?