Hello:

How would you create a query of related table to create a single table for exporting to an ascii file

For example, given the 4 tables below, each table is related to the Jobs table, but the other tables are not related to each other.



Jobs table
Contacts table
Appointments table
Document table


The contents of the table would be something like this:


Jobs Table (Fields: jobKey, JobName)
j1, Misty Creek
j2, Palm Island
j3, Longwood Run


Contacts:  (fields: contactKey, JobKey, ContactName
c1, j1, John Smith
c2, j1, Tom Jones
c3, j2, Jane Doe


Appointments: (fields, AppointmentKey, jobkey, Appointment)
a1, j1,  Prepare sketch
a2, j1,  Lunch with client
a3, j3,  Some other appointment


Documents:  (fields, documentKey, jobKey, documentName)
d1, j1,  Document A
d2, j1,  Document B
d3, j2,  Document C


The goal is to create a single table that would include all the records for say, Job 1 (Misty Creek).  


It would look something like this:

j1,  Misty Creek, John Smith, ,
j1,  Misty Creek, Tom Jones, ,
j1,  Misty Creek, , Prepare Sketch,
j1,  Misty Creek, , Lunch with Client,
j1,  Misty Creek, , , Document A
j1,  Misty Creek, , , Document B


Is this even possible?  Or is there a better approach to exporting data like this?  XML Perhaps?  

Thanks

Dale

Dale

> How would you create a query of related table to create a single table for
exporting to an ascii file
>
> For example, given the 4 tables below, each table is related to the Jobs
table, but the other tables are not related to each other.
>
>
>
> Jobs table
> Contacts table
> Appointments table
> Document table
>
>
> The contents of the table would be something like this:
>
>
> Jobs Table (Fields: jobKey, JobName)
> j1, Misty Creek
> j2, Palm Island
> j3, Longwood Run
>
>
> Contacts:  (fields: contactKey, JobKey, ContactName
> c1, j1, John Smith
> c2, j1, Tom Jones
> c3, j2, Jane Doe
>
>
> Appointments: (fields, AppointmentKey, jobkey, Appointment)
> a1, j1,  Prepare sketch
> a2, j1,  Lunch with client
> a3, j3,  Some other appointment
>
>
> Documents:  (fields, documentKey, jobKey, documentName)
> d1, j1,  Document A
> d2, j1,  Document B
> d3, j2,  Document C
>
>
> The goal is to create a single table that would include all the records
for say, Job 1 (Misty Creek).
>
>
> It would look something like this:
>
> j1,  Misty Creek, John Smith, ,
> j1,  Misty Creek, Tom Jones, ,
> j1,  Misty Creek, , Prepare Sketch,
> j1,  Misty Creek, , Lunch with Client,
> j1,  Misty Creek, , , Document A
> j1,  Misty Creek, , , Document B
>

It looks like a union query, for example

SELECT Jobs.Jobkey,Jobs.JobName,Contacts.ContactName,'' AS OtherField FROM
Jobs
JOIN Contacts ON Jobs.Jobkey=Contacts.JobKey
WHERE JobKey=j1
UNION ALL
SELECT Jobs.Jobkey,Jobs.JobName,'',Appointments.Appointment FROM Jobs
JOIN Apointments ON Jobs.Jobkey=Appointments.JobKey
WHERE JobKey=j1
UNION ALL
SELECT Jobs.Jobkey,Jobs.JobName,'',Documents.Document FROM Jobs
JOIN Documents ON Jobs.Jobkey=Documents.JobKey
WHERE JobKey=j1

John

<<<<It looks like a union query, for example

SELECT Jobs.Jobkey,Jobs.JobName,Contacts.ContactName,'' AS OtherField FROM
Jobs
JOIN Contacts ON Jobs.Jobkey=Contacts.JobKey
WHERE JobKey=j1
UNION ALL
SELECT Jobs.Jobkey,Jobs.JobName,'',Appointments.Appointment FROM Jobs
JOIN Apointments ON Jobs.Jobkey=Appointments.JobKey
WHERE JobKey=j1
UNION ALL
SELECT Jobs.Jobkey,Jobs.JobName,'',Documents.Document FROM Jobs
JOIN Documents ON Jobs.Jobkey=Documents.JobKey
WHERE JobKey=j1

John
>>>>

Thanks John:

I'll bet that will work just fine.  Unfortunately, I just got pulled off this project for the next few days so I won't be able to try it out for a while.

Dale