Ready for the easier way to do this? How about with no SQL to type?

-DataGet External Data
\Use Excel Files
\Select the Course_Students.xls file
\Select the rngNameCourse range
\Click [Next] until the last screen and edit the query
(so far...same as before)

Once MS Query displays the data
-Click the Add Table(s) button
-At the bottom of the dialog, click the Workbook dropdown
\Select the Course_Instructors.xls file
(it must be located in the same directory as the first file)
\Select the rngCourseInstr range and click [OK]

Now you should see 2 tables displayed.
-Drag the COURSE field from one table to the COURSE field on the other table
-Drag the DATE field from one table to the DATE field on the other table

-Double clidk on the INSTRUCTOR field
(it should now appear in the returned data)

-Click the Query Now button (!) to get the latest data
....and if you check the SQL, it's even more compact than the code I threw
together.

-Click the Return Data to Excel button
Done

Easier to do, yes?

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


"Ron Coderre" wrote:

It looks like you have a couple typos in the WHERE clause:
rngCourseIntr should be
rngCourseInstr

(missing an "s" in Instr in both statements)

Does that fix it?

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


"will A" wrote:

Hi Ron,
When I click on OK I'm getting this msg "SQL Query cannot be represented
graphically, continue?" I clicked yes and get this msg "Could not add the
table '(SELECT'." Anything wrong with my query below? Thanks Ron.

SELECT
rngNameCourse.FNAME,
rngNameCourse.LNAME,
rngNameCourse.COURSE,
rngNameCourse.DATE,
rngCourseInstr.INSTRUCTOR
FROM
(SELECT
rngNameCourse.FNAME,
rngNameCourse.LNAME,
rngNameCourse.COURSE,
rngNameCourse.DATE
FROM `N:\InfoMgmt\waFILES\OTHERS2\course_students`.rngN ameCourse)
rngNameCourse,
(SELECT
rngCourseInstr.DATE,
rngCourseInstr.COURSE,
rngCourseInstr.INSTRUCTOR
FROM `N:\InfoMgmt\waFILES\OTHERS2\course_INSTRUCTOR'.rn gCourseInstr)
rngCourseInstr
WHERE
rngNameCourse.COURSE = rngCourseIntr.COURSE
AND rngNameCourse.DATE = rngCourseIntr.DATE

"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?