Create One Record Set from two Tables located in Two Databases

Create One Record Set from two Tables located in Two Databases

Post by Joseph Landr » Thu, 27 Nov 1997 04:00:00



Hypothetical situation:
I work at the main office of a company with a chain of retail stores.  
I have a database by the name C:\EMPLOYEES.MDB
One of the tables in this database is NAME
This table consists of three fields: Store, Employee, First
(it associates the First Name with the Employee Number for each Store
Number)

The table might look something like...

Store   Employee        Name
===========================
1       1               Bob
1       2               Susan
1       3               Bill
2       1               Fred
2       2               Amy
2       3               Fran
3       1               John
3       2               Ann
3       3               Alice

I have a second database by the name of C:\SCHEDULE.MDB
One of the tables in this database is MONDAY
This table consists of four fields: Store, Employee, Department, Time
(it indicates when each employee is scheduled to come to work today and
which department they will work in)
Note: If an employee is not scheduled to come to work on MONDAY, they are
not in the table

The Table might look something like...

Store   Employee        Department      Time
==================================
1       1               1               1:00
1       2               1               3:00
2       1               1               2:00
2       2               2               5:00
3       1               2               4:00
3       2               1               6:00

I need a record set that consists of everyone who works in Department 1
sorted by their arrival time for MONDAY

The record set should look something like...

Store   Employee        Name            Time
==================================
1       1               Bob             1:00
2       1               Fred            2:00
1       1               Susan           3:00
3       2               Ann             6:00

Note that the record set combines fields from two tables, each table is in
a seperate database, the two tables have a different number of rows and a
different number of fields, each table requires two fields to uniquly
identify a record (the employee)

Can someone help me in creating an SQL statement that can build just such a
record set?

Thanks,
--
Joseph
(Remove nospam_ for reply)

 
 
 

1. Retrieve data from two tables, from two databases, in two registered servers

In SQL Server 6.5, I have two registered servers that I can view in
Enterprise Manager.

Registered server #1 called DATA1, and has a database called Marketing, and
a table called Pricing.
Registered server #2 called DATA2, and has a database called Sales, and a
table called Products.

How can I retrieve data from these two tables, from these two databases,
from these two registered servers?

Thank you.

2. HELP: Powerful database components?

3. Help..join on two table , Selecting records from two tables

4. DAO Multiple database 4pt integrated program w/VB

5. How to use ADO to create one to one relationship between two tables

6. !!! Visual Basic/C++ Edmonton,AB

7. Using one record to access two (or more) records in another related database

8. Creating tables at run time with ADO 2.5 and VB6

9. Two Servers Two databases one query

10. Creating one table from two tables

11. Create one table from 10 tables in two files

12. Adding record in one table from form with two tables

13. Selecting unequal fields from two records in two tables based on PK being equal