Problem with Retrieving Results

  • Thread starter Thread starter Paul Bush
  • Start date Start date
P

Paul Bush

I have 2 tables. One for softwareTitleOwners and one for SoftwareOC
(out-of-compliance). The table for Software that is out-of-compliance
simply has a field for software title and the employee name. I want to
search for ALL users of a particular software title. The results should
include those users that are out-of-compliance as well for reporting
purposes.

Here's what I am using:

SELECT tblSoftware.SoftwareTitle, tblSoftware.EmployeeName,
tblSoftwareOC.EmployeeName
FROM tblSoftware LEFT JOIN tblSoftwareOC ON tblSoftware.SoftwareTitle =
tblSoftwareOC.SoftwareTitle
WHERE (((tblSoftware.SoftwareTitle)="Adobe Acrobat 6.0.1 Professional")
AND ((tblSoftware.EmployeeName) Is Not Null) AND
((tblSoftwareOC.EmployeeName) Is Not Null));

This almost achieves the results that I want. Is there a way that I can
possibly create a field and include all the EmployeeNames from both
tables that are linked to a specific piece of software?
 
I think you just want a UNION query:

SELECT tblSoftware.SoftwareTitle, tblSoftware.EmployeeName,
FROM tblSoftware
WHERE ((tblSoftware.SoftwareTitle)="Adobe Acrobat 6.0.1 Professional")
UNION
SELECT tblSoftwareOC.SoftwareTitle, tblSoftwareOC.EmployeeName,
FROM tblSoftwareOC
WHERE ((tblSoftwareOC.SoftwareTitle)="Adobe Acrobat 6.0.1 Professional");

(Typically I just use Access' query-by-example to create the components of
the UNION query separately, then copy/paste the SQL together, adding the
UNION in place of the first semicolon.)
--Bruce
 
Thanks for the reply. I have copied and pasted your revised SQL string
for me to use and am getting the following error:

The SELECT statement includes a reserved word or an argument name that
is misspelled or missing, or the punctuation is incorrect.

This error confuses me in more ways than one. Access is a great
program but the errors are highly ambiguous. I cannot, for the life of
me, figure this out. Here is my SQL string:

SELECT SoftwareTitle, EmployeeName
FROM tblSoftware
WHERE (SoftwareTitle = "Adobe Acrobat 6.0.1 Professional")
UNION
SELECT SoftwareTitle, EmployeeName,
FROM tblSoftwareOC
WHERE (SoftwareTitle = "Adobe Acrobat 6.0.1 Professional")

Is there something I don't see here?
 
I'm back again!

In the bottom SELECT query, there might not always be data in the
table. How would I implement an IIf function to the bottom query?
 
Back
Top