ACCESS FEED: RE: [MS_AccessPros] Query Does Not Return Any Results

Selasa, 05 November 2013

RE: [MS_AccessPros] Query Does Not Return Any Results

I'm about to start working on a third edition of SQL Queries for Mere
Mortals. I'll keep this one in mind!

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Beshlian,Bill
Sent: Tuesday, November 05, 2013 9:48 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Query Does Not Return Any Results

John,

You are correct table A has all the current certifications an employee has
and table B list all the potential certifications. I never even thought of
using a Cartesian product. I'll try this and let you know how it works.

Thanks!

Bill Beshlian

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Tuesday, November 05, 2013 2:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Query Does Not Return Any Results

Bill-

Does "A" return all the certifications that an employee does have and "B"
return *all* potential certifications? What you then need is the Cartesian
product of both sets, then select the rows where the certification part
isn't in the set related to the current employee.

SELECT A.Employee, A.Name, B.[Job Code], B.[Lic Cert Code], B.GroupNum FROM
REQ_LIC_W_GRP_NUM_QRY AS A, REQ_LIC_JOBCODE_QRY AS B WHERE NOT Exists
(SELECT C.GroupNum FROM REC_LIC_JOBCODE_QRY As C WHERE C.GroupNum =
B.GroupNum AND C.[Job Code] = B.[Job Code] AND C.[Lic Cert Code] = B.[Lic
Cert Code])

That's pretty sloppy, but I think it will work. Essentially, you are
matching all employee records with all certifications, then selecting the
certifications that do not show up in a current employee record.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Beshlian,Bill
Sent: Tuesday, November 05, 2013 5:42 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Query Does Not Return Any Results

Hello,

Here is the SQL Code for my query:

SELECT A.Employee, A.Name, A.[Job Code], A.[Lic Cert Code], A.GroupNum FROM
REQ_LIC_W_GRP_NUM_QRY AS A LEFT JOIN REQ_LIC_JOBCODE_QRY AS B ON (A.GroupNum
= B.GroupNum) AND (A.[Lic Cert Code] = B.[LicCert]) AND (A.[Job Code] =
B.JobCode) WHERE (((A.[Job Code])="000496") AND ((Exists (Select B.GroupNum
From REQ_LIC_JOBCODE_QRY AS B, REQ_LIC_W_GRP_NUM_QRY AS A Where
B.GroupNum=A.GroupNum ))=False));

Currently it returns nothing. What I'd like it to return is:

Employee

Name

Job Code

Lic Cert Code

GroupNum

00000

Smith, Joseph E

000496

ABMS-BC

1

00000

Smith, Joseph E

000496

DEA-NE

5

If I change the condition to True in the Exists statement/subquery it
returns the certifications Dr. Smith already possesses. I am looking for
the two he does not (shown in table above). Is there something missing in
the logic for the query or subquery?

Thank you,

Bill Beshlian

________________________________
The information contained in this communication, including attachments, is
confidential and private and intended only for the use of the addressees.
Unauthorized use, disclosure, distribution or copying is strictly prohibited
and may be unlawful. If you received this communication in error, please
inform us of the erroneous delivery by return e-mail message from your
computer. Additionally, although all attachments have been scanned at the
source for viruses, the recipient should check any attachments for the
presence of viruses before opening. Alegent Creighton Health accepts no
liability for any damage caused by any virus transmitted by this e-mail.
Thank you for your cooperation.

[Non-text portions of this message have been removed]

------------------------------------

Yahoo Groups Links