Query goofed up!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that needs to get all the employee records that have not made
a cobra insurance payment within a specified month. Each employee should be
in the General Cobra db (primary) which is joined by ss# to the Payments db.
I'm trying to match the ss# in each database and then see if they made a pmt
in the month in question. My SQL isn't right and I don't have a lot of
experience with this. I'd really appreciate help with this. Thanks! Please
try not to laugh too much at this:

SELECT [General Cobra Information].[SocialSecurity#], [General Cobra
Information].[First Name], [General Cobra Information].[Last Name]
FROM [General Cobra Information] LEFT JOIN Payment ON [General Cobra
Information].[SocialSecurity#] = Payment.[SocialSecurity#]
WHERE (([Payment].[Month] = [What month?] AND (Payment.[SocialSecurity#]) Is
Null));
 
Have you tried this using the query designer? What happens?

Your SQL appears to only be looking for records in the [General Cobra
Information] table where there is no corresponding Payment record.

Good luck

Jeff Boyce
<Access MVP>
 
You could do this in one query EXCEPT your table and field names have spaces and
special characters (e.g., #). Best way is probably two queries.

QueryA
SELECT [SocialSecurity#]
FROM Payment
WHERE Payment.Month = [What month?]

Save that as QueryA (or whatever name you wish to use)

QueryB Uses QueryA (Note that I've aliased the table names)

SELECT A.[SocialSecurity#],
A.[First Name], A.[Last Name]
FROM [General Cobra Information] As A LEFT JOIN QueryA as Q
ON A.[SocialSecurity#] = Q.[SocialSecurity#]
WHERE Q.[SocialSecurity#] Is Null


Alternative (Probably a LOT Slower since NOT IN is S L O W)

SELECT A.[SocialSecurity#],
A.[First Name], A.[Last Name]
FROM [General Cobra Information] As A
WHERE A.[Social Security#] NOT IN
(SELECT [SocialSecurity#]
FROM Payment
WHERE Payment.Month = [What month?])
 
Thanks Jeff and John for your responses. The solution John sent seems to be
working fine for me, and I actually learned a few things!

John Spencer (MVP) said:
You could do this in one query EXCEPT your table and field names have spaces and
special characters (e.g., #). Best way is probably two queries.

QueryA
SELECT [SocialSecurity#]
FROM Payment
WHERE Payment.Month = [What month?]

Save that as QueryA (or whatever name you wish to use)

QueryB Uses QueryA (Note that I've aliased the table names)

SELECT A.[SocialSecurity#],
A.[First Name], A.[Last Name]
FROM [General Cobra Information] As A LEFT JOIN QueryA as Q
ON A.[SocialSecurity#] = Q.[SocialSecurity#]
WHERE Q.[SocialSecurity#] Is Null


Alternative (Probably a LOT Slower since NOT IN is S L O W)

SELECT A.[SocialSecurity#],
A.[First Name], A.[Last Name]
FROM [General Cobra Information] As A
WHERE A.[Social Security#] NOT IN
(SELECT [SocialSecurity#]
FROM Payment
WHERE Payment.Month = [What month?])

I have a query that needs to get all the employee records that have not made
a cobra insurance payment within a specified month. Each employee should be
in the General Cobra db (primary) which is joined by ss# to the Payments db.
I'm trying to match the ss# in each database and then see if they made a pmt
in the month in question. My SQL isn't right and I don't have a lot of
experience with this. I'd really appreciate help with this. Thanks! Please
try not to laugh too much at this:

SELECT [General Cobra Information].[SocialSecurity#], [General Cobra
Information].[First Name], [General Cobra Information].[Last Name]
FROM [General Cobra Information] LEFT JOIN Payment ON [General Cobra
Information].[SocialSecurity#] = Payment.[SocialSecurity#]
WHERE (([Payment].[Month] = [What month?] AND (Payment.[SocialSecurity#]) Is
Null));
Click to expand...
Click to expand...
 
Back
Top