Hi Chaps,

I haven't used subqueries before, so I am not 100% on the best way to approach this.

The Scenario:

I have tables;

tblJobDetails
tblMissingKit
tblSiteDeficiencies

JobID is the primary key for tblJobDetails. tblMissingKit and tblSiteDeficiencies are both tables containing (funnily enough) Missing Kit and Site Deficiences on the Job's (JobIDs) in tblJobDetails. The problem is, there can be more than 1 amount of missing Kit per JobID and more than 1 site deficiency per JobID.

In an ideal world, when I run this query, I would only get 1 record per JobID, but in a 'MissingKit' field, there would be a list of the missing kit for that site (new lines, comma seperated - doesn't matter) and then the same with Site Deficiencies.

Not sure if this is even doable, but thought I'd ask the people who know.

Thanks in advance,

Taz

You could try SubForms or SubReports

tblJobDetails (Parent)
tblMissingKit (Child)
tblSiteDeficiencies (Child)

Whether or not on the tbljobDetails you keep a trace of if Missing Kit or Site Deficiencies exist is another matter.

Simon