Query Building

M

Mindy

This is my query: SELECT [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Gender, [Contact Info].Address, [Contact Info].[Address
2], [Contact Info].City, [Contact Info].State, [Contact Info].Zip,
Asbestos.[KY Exp], Asbestos.[IL Exp], Asbestos.[IN Exp], Asbestos.[MI Exp],
Asbestos.[OH Exp], Asbestos.[WI Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR (((Asbestos.[IL
Exp])<#6/27/2008#)) OR (((Asbestos.[IN Exp])<#6/27/2008#)) OR (((Asbestos.[MI
Exp])<#6/27/2008#)) OR (((Asbestos.[OH Exp])<#6/27/2008#)) OR (((Asbestos.[WI
Exp])<#6/27/2008#));




Is there a way I can exlude certifications that are still valid? For
example if the IN cert is expired, but the IL cert is still valid, the IL
expiration date will not show up?

I ask because I want to bring this information into a Publisher Postcard via
Mail Merge.

Thank you so much for your help!
 
M

Michel Walsh

right after the word WHERE, add a space, then the word NOT, then a space,
then a ( and, in the end, just before the semi colon, add a ). Does that
work?


Vanderghast, Access MVP
 
M

Mindy

Sorry I actually posted my SQL wrong...

SELECT [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp],
Asbestos.[WI Exp], Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp],
Asbestos.[AR Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
GROUP BY [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp],
Asbestos.[WI Exp], Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp],
Asbestos.[AR Exp]
HAVING (((Asbestos.[Cert Exp])<#8/5/2008#)) OR (((Asbestos.[KY
Exp])<#8/5/2008#) AND ((Asbestos.[IL Exp])<#8/5/2008#) AND ((Asbestos.[IL
Exp])<#8/5/2008#) AND ((Asbestos.[MI Exp])<#8/5/2008#) AND ((Asbestos.[OH
Exp])<#8/5/2008#) AND ((Asbestos.[WI Exp])<#8/5/2008#) AND ((Asbestos.[NC
Exp])<#8/5/2008#) AND ((Asbestos.[WV Exp])<#8/5/2008#) AND ((Asbestos.[MO
Exp])<#8/5/2008#) AND ((Asbestos.[AR Exp])<#8/5/2008#));


If there is a way to mess it up I will find it!



Michel Walsh said:
right after the word WHERE, add a space, then the word NOT, then a space,
then a ( and, in the end, just before the semi colon, add a ). Does that
work?


Vanderghast, Access MVP


Mindy said:
This is my query: SELECT [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Gender, [Contact Info].Address, [Contact
Info].[Address
2], [Contact Info].City, [Contact Info].State, [Contact Info].Zip,
Asbestos.[KY Exp], Asbestos.[IL Exp], Asbestos.[IN Exp], Asbestos.[MI
Exp],
Asbestos.[OH Exp], Asbestos.[WI Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR (((Asbestos.[IL
Exp])<#6/27/2008#)) OR (((Asbestos.[IN Exp])<#6/27/2008#)) OR
(((Asbestos.[MI
Exp])<#6/27/2008#)) OR (((Asbestos.[OH Exp])<#6/27/2008#)) OR
(((Asbestos.[WI
Exp])<#6/27/2008#));




Is there a way I can exlude certifications that are still valid? For
example if the IN cert is expired, but the IL cert is still valid, the IL
expiration date will not show up?

I ask because I want to bring this information into a Publisher Postcard
via
Mail Merge.

Thank you so much for your help!
Click to expand...
Click to expand...
 
J

John Spencer

SELECT [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Gender, [Contact Info].Address
, [Contact Info].[Address 2], [Contact Info].City
, [Contact Info].State
, [Contact Info].Zip
, IIF(Asbestos.[KY Exp]<#6/27/2008#,[KY Exp],Null) as KYExp
, IIF(Asbestos.[IL Exp]<#/6/27/2008#,[IL Exp],Null) as ILExp
==== Repeat the same type calculation for the remaining Items ====
, Asbestos.[IN Exp]
, Asbestos.[MI Exp]
, Asbestos.[OH Exp]
, Asbestos.[WI Exp]

FROM [Contact Info] LEFT JOIN Asbestos
ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR
(((Asbestos.[IL Exp])<#6/27/2008#)) OR
(((Asbestos.[IN Exp])<#6/27/2008#)) OR
(((Asbestos.[MI Exp])<#6/27/2008#)) OR
(((Asbestos.[OH Exp])<#6/27/2008#)) OR
(((Asbestos.[WI Exp])<#6/27/2008#));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
M

Mindy

John, I actually posted my SQL wrong the first time, could you re-post with
the right SQL? Or how can I fix it in design view? I don't actually use SQL
I just know sometimes that helps people help me...

Mindy

SELECT [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp], Asbestos.[WI Exp],
Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp], Asbestos.[AR Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
GROUP BY [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp], Asbestos.[WI Exp],
Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp], Asbestos.[AR Exp]
HAVING (((Asbestos.[Cert Exp])<#8/5/2008#)) OR (((Asbestos.[KY
Exp])<#8/5/2008#) AND ((Asbestos.[IL Exp])<#8/5/2008#) AND ((Asbestos.[MI
Exp])<#8/5/2008#) AND ((Asbestos.[OH Exp])<#8/5/2008#) AND ((Asbestos.[WI
Exp])<#8/5/2008#) AND ((Asbestos.[NC Exp])<#8/5/2008#) AND ((Asbestos.[WV
Exp])<#8/5/2008#) AND ((Asbestos.[MO Exp])<#8/5/2008#) AND ((Asbestos.[AR
Exp])<#8/5/2008#));





John Spencer said:
SELECT [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Gender, [Contact Info].Address
, [Contact Info].[Address 2], [Contact Info].City
, [Contact Info].State
, [Contact Info].Zip
, IIF(Asbestos.[KY Exp]<#6/27/2008#,[KY Exp],Null) as KYExp
, IIF(Asbestos.[IL Exp]<#/6/27/2008#,[IL Exp],Null) as ILExp
==== Repeat the same type calculation for the remaining Items ====
, Asbestos.[IN Exp]
, Asbestos.[MI Exp]
, Asbestos.[OH Exp]
, Asbestos.[WI Exp]

FROM [Contact Info] LEFT JOIN Asbestos
ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR
(((Asbestos.[IL Exp])<#6/27/2008#)) OR
(((Asbestos.[IN Exp])<#6/27/2008#)) OR
(((Asbestos.[MI Exp])<#6/27/2008#)) OR
(((Asbestos.[OH Exp])<#6/27/2008#)) OR
(((Asbestos.[WI Exp])<#6/27/2008#));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
This is my query: SELECT [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Gender, [Contact Info].Address, [Contact Info].[Address
2], [Contact Info].City, [Contact Info].State, [Contact Info].Zip,
Asbestos.[KY Exp], Asbestos.[IL Exp], Asbestos.[IN Exp], Asbestos.[MI Exp],
Asbestos.[OH Exp], Asbestos.[WI Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR (((Asbestos.[IL
Exp])<#6/27/2008#)) OR (((Asbestos.[IN Exp])<#6/27/2008#)) OR (((Asbestos.[MI
Exp])<#6/27/2008#)) OR (((Asbestos.[OH Exp])<#6/27/2008#)) OR (((Asbestos.[WI
Exp])<#6/27/2008#));




Is there a way I can exlude certifications that are still valid? For
example if the IN cert is expired, but the IL cert is still valid, the IL
expiration date will not show up?

I ask because I want to bring this information into a Publisher Postcard via
Mail Merge.

Thank you so much for your help!
Click to expand...
Click to expand...
 
B

Bob Barrows [MVP]

You can do it yourself - just copy/paste John's sql into a query's SQL
View and switch to Design View to see what you were supposed to do in
Design View.
John, I actually posted my SQL wrong the first time, could you
re-post with the right SQL? Or how can I fix it in design view? I
don't actually use SQL I just know sometimes that helps people help
me...

Mindy

SELECT [Contact Info].[First Name], [Contact Info].[Last Name],
[Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City, [Contact Info].State, [Contact Info].Zip, Asbestos.[Cert
Exp], Asbestos.[KY Exp], Asbestos.[IL Exp], Asbestos.[MI Exp],
Asbestos.[OH Exp], Asbestos.[WI Exp], Asbestos.[NC Exp], Asbestos.[WV
Exp], Asbestos.[MO Exp], Asbestos.[AR Exp] FROM [Contact Info] LEFT
JOIN Asbestos ON [Contact Info].ID = Asbestos.ID GROUP BY [Contact
Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City,
[Contact Info].State, [Contact Info].Zip, Asbestos.[Cert Exp],
Asbestos.[KY Exp], Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH
Exp], Asbestos.[WI Exp], Asbestos.[NC Exp], Asbestos.[WV Exp],
Asbestos.[MO Exp], Asbestos.[AR Exp] HAVING (((Asbestos.[Cert
Exp])<#8/5/2008#)) OR (((Asbestos.[KY Exp])<#8/5/2008#) AND
((Asbestos.[IL Exp])<#8/5/2008#) AND ((Asbestos.[MI Exp])<#8/5/2008#)
AND ((Asbestos.[OH Exp])<#8/5/2008#) AND ((Asbestos.[WI
Exp])<#8/5/2008#) AND ((Asbestos.[NC Exp])<#8/5/2008#) AND
((Asbestos.[WV Exp])<#8/5/2008#) AND ((Asbestos.[MO Exp])<#8/5/2008#)
AND ((Asbestos.[AR Exp])<#8/5/2008#));





John Spencer said:
SELECT [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Gender, [Contact Info].Address
, [Contact Info].[Address 2], [Contact Info].City
, [Contact Info].State
, [Contact Info].Zip
, IIF(Asbestos.[KY Exp]<#6/27/2008#,[KY Exp],Null) as KYExp
, IIF(Asbestos.[IL Exp]<#/6/27/2008#,[IL Exp],Null) as ILExp
==== Repeat the same type calculation for the remaining Items ====
, Asbestos.[IN Exp]
, Asbestos.[MI Exp]
, Asbestos.[OH Exp]
, Asbestos.[WI Exp]

FROM [Contact Info] LEFT JOIN Asbestos
ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR
(((Asbestos.[IL Exp])<#6/27/2008#)) OR
(((Asbestos.[IN Exp])<#6/27/2008#)) OR
(((Asbestos.[MI Exp])<#6/27/2008#)) OR
(((Asbestos.[OH Exp])<#6/27/2008#)) OR
(((Asbestos.[WI Exp])<#6/27/2008#));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
This is my query: SELECT [Contact Info].[First Name], [Contact
Info].[Last Name], [Contact Info].Gender, [Contact Info].Address,
[Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[KY Exp], Asbestos.[IL
Exp], Asbestos.[IN Exp], Asbestos.[MI Exp], Asbestos.[OH Exp],
Asbestos.[WI Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID =
Asbestos.ID WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR
(((Asbestos.[IL Exp])<#6/27/2008#)) OR (((Asbestos.[IN
Exp])<#6/27/2008#)) OR (((Asbestos.[MI Exp])<#6/27/2008#)) OR
(((Asbestos.[OH Exp])<#6/27/2008#)) OR (((Asbestos.[WI
Exp])<#6/27/2008#));




Is there a way I can exlude certifications that are still valid?
For example if the IN cert is expired, but the IL cert is still
valid, the IL expiration date will not show up?

I ask because I want to bring this information into a Publisher
Postcard via Mail Merge.

Thank you so much for your help!
Click to expand...
Click to expand...
Click to expand...
 
M

Mindy

Please review my SQL they are completely different, I tried to make the
changes on my own and it didn't work.

SELECT [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp], Asbestos.[WI Exp],
Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp], Asbestos.[AR Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID=Asbestos.ID
GROUP BY [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp], Asbestos.[WI Exp],
Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp], Asbestos.[AR Exp]
HAVING (((Asbestos.[Cert Exp])<#8/5/2008#)) OR (((Asbestos.[KY
Exp])<#8/5/2008#) AND ((Asbestos.[IL Exp])<#8/5/2008#) AND ((Asbestos.[MI
Exp])<#8/5/2008#) AND ((Asbestos.[OH Exp])<#8/5/2008#) AND ((Asbestos.[WI
Exp])<#8/5/2008#) AND ((Asbestos.[NC Exp])<#8/5/2008#) AND ((Asbestos.[WV
Exp])<#8/5/2008#) AND ((Asbestos.[MO Exp])<#8/5/2008#) AND ((Asbestos.[AR
Exp])<#8/5/2008#));

Bob Barrows said:
You can do it yourself - just copy/paste John's sql into a query's SQL
View and switch to Design View to see what you were supposed to do in
Design View.
John, I actually posted my SQL wrong the first time, could you
re-post with the right SQL? Or how can I fix it in design view? I
don't actually use SQL I just know sometimes that helps people help
me...

Mindy

SELECT [Contact Info].[First Name], [Contact Info].[Last Name],
[Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City, [Contact Info].State, [Contact Info].Zip, Asbestos.[Cert
Exp], Asbestos.[KY Exp], Asbestos.[IL Exp], Asbestos.[MI Exp],
Asbestos.[OH Exp], Asbestos.[WI Exp], Asbestos.[NC Exp], Asbestos.[WV
Exp], Asbestos.[MO Exp], Asbestos.[AR Exp] FROM [Contact Info] LEFT
JOIN Asbestos ON [Contact Info].ID = Asbestos.ID GROUP BY [Contact
Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City,
[Contact Info].State, [Contact Info].Zip, Asbestos.[Cert Exp],
Asbestos.[KY Exp], Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH
Exp], Asbestos.[WI Exp], Asbestos.[NC Exp], Asbestos.[WV Exp],
Asbestos.[MO Exp], Asbestos.[AR Exp] HAVING (((Asbestos.[Cert
Exp])<#8/5/2008#)) OR (((Asbestos.[KY Exp])<#8/5/2008#) AND
((Asbestos.[IL Exp])<#8/5/2008#) AND ((Asbestos.[MI Exp])<#8/5/2008#)
AND ((Asbestos.[OH Exp])<#8/5/2008#) AND ((Asbestos.[WI
Exp])<#8/5/2008#) AND ((Asbestos.[NC Exp])<#8/5/2008#) AND
((Asbestos.[WV Exp])<#8/5/2008#) AND ((Asbestos.[MO Exp])<#8/5/2008#)
AND ((Asbestos.[AR Exp])<#8/5/2008#));





John Spencer said:
SELECT [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Gender, [Contact Info].Address
, [Contact Info].[Address 2], [Contact Info].City
, [Contact Info].State
, [Contact Info].Zip
, IIF(Asbestos.[KY Exp]<#6/27/2008#,[KY Exp],Null) as KYExp
, IIF(Asbestos.[IL Exp]<#/6/27/2008#,[IL Exp],Null) as ILExp
==== Repeat the same type calculation for the remaining Items ====
, Asbestos.[IN Exp]
, Asbestos.[MI Exp]
, Asbestos.[OH Exp]
, Asbestos.[WI Exp]

FROM [Contact Info] LEFT JOIN Asbestos
ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR
(((Asbestos.[IL Exp])<#6/27/2008#)) OR
(((Asbestos.[IN Exp])<#6/27/2008#)) OR
(((Asbestos.[MI Exp])<#6/27/2008#)) OR
(((Asbestos.[OH Exp])<#6/27/2008#)) OR
(((Asbestos.[WI Exp])<#6/27/2008#));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Mindy wrote:
This is my query: SELECT [Contact Info].[First Name], [Contact
Info].[Last Name], [Contact Info].Gender, [Contact Info].Address,
[Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[KY Exp], Asbestos.[IL
Exp], Asbestos.[IN Exp], Asbestos.[MI Exp], Asbestos.[OH Exp],
Asbestos.[WI Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID =
Asbestos.ID WHERE (((Asbestos.[KY Exp])<#6/27/2008#)) OR
(((Asbestos.[IL Exp])<#6/27/2008#)) OR (((Asbestos.[IN
Exp])<#6/27/2008#)) OR (((Asbestos.[MI Exp])<#6/27/2008#)) OR
(((Asbestos.[OH Exp])<#6/27/2008#)) OR (((Asbestos.[WI
Exp])<#6/27/2008#));




Is there a way I can exlude certifications that are still valid?
For example if the IN cert is expired, but the IL cert is still
valid, the IL expiration date will not show up?

I ask because I want to bring this information into a Publisher
Postcard via Mail Merge.

Thank you so much for your help!
Click to expand...
Click to expand...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
J

John Spencer

First, there is no reason to use group by in your query.
Second, what you asked for should still be the same as earlier posted
Third, make life simpler and use a parameter so the date only has to be
entered once if it changes
Fourth, applying criteria against the asbestos table negates the left join.

So the following is what I would recommend for your query.

Parameters [Enter Cutoff Date] as DateTime;
SELECT DISTINCT [Contact Info].[First Name]
, [Contact Info].[Last Name]
, [Contact Info].Address, [Contact Info].[Address 2]
, [Contact Info].City
, [Contact Info].State, [Contact Info].Zip
, Asbestos.[Cert Exp]
, IIF(Asbestos.[KY Exp]<[Enter Cutoff Date],[Ky Exp],Null) as KY
, IIF(Asbestos.[IL Exp]<[Enter Cutoff Date],[IL Exp],Null) as IL
, IIF(Asbestos.[MI Exp]<[Enter Cutoff Date],[MI EXP],Null) as MI
, IIF(Asbestos.[OH Exp]<[Enter Cutoff Date],[OH Exp],Null) as OH
, IIF(Asbestos.[WI Exp]<[Enter Cutoff Date],[WI Exp],Null) as WI
, IIF(Asbestos.[NC Exp]<[Enter Cutoff Date],[NC Exp],Null) as NC
, IIF(Asbestos.[WV Exp]<[Enter Cutoff Date],[WV Exp],Null) as WV
, IIF(Asbestos.[MO Exp]<[Enter Cutoff Date],[MO Exp],Null) as MO
, IIF(Asbestos.[AR Exp]<[Enter Cutoff Date],[AR Exp],Null) as AR
FROM [Contact Info] INNER JOIN Asbestos
ON [Contact Info].ID=Asbestos.ID
WHERE (((Asbestos.[Cert Exp])<[Enter Cutoff Date]))
OR (((Asbestos.[KY Exp])<[Enter Cutoff Date])
AND ((Asbestos.[IL Exp])<[Enter Cutoff Date])
AND ((Asbestos.[MI Exp])<[Enter Cutoff Date])
AND ((Asbestos.[OH Exp])<#[Enter Cutoff Date])
AND ((Asbestos.[WI Exp])<[Enter Cutoff Date])
AND ((Asbestos.[NC Exp])<[Enter Cutoff Date])
AND ((Asbestos.[WV Exp])<[Enter Cutoff Date])
AND ((Asbestos.[MO Exp])<[Enter Cutoff Date])
AND ((Asbestos.[AR Exp])<[Enter Cutoff Date]));

If you do need all the contact info even if no records in Asbestos meet
your criteria, then make a query without the contact infor in it just
all the asbestos field. Then create a new query and join the Contact
Info table to the saved asbestos query using a LEFT (or right) JOIN.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Please review my SQL they are completely different, I tried to make the
changes on my own and it didn't work.

SELECT [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp], Asbestos.[WI Exp],
Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp], Asbestos.[AR Exp]
FROM [Contact Info] LEFT JOIN Asbestos ON [Contact Info].ID=Asbestos.ID
GROUP BY [Contact Info].[First Name], [Contact Info].[Last Name], [Contact
Info].Address, [Contact Info].[Address 2], [Contact Info].City, [Contact
Info].State, [Contact Info].Zip, Asbestos.[Cert Exp], Asbestos.[KY Exp],
Asbestos.[IL Exp], Asbestos.[MI Exp], Asbestos.[OH Exp], Asbestos.[WI Exp],
Asbestos.[NC Exp], Asbestos.[WV Exp], Asbestos.[MO Exp], Asbestos.[AR Exp]
HAVING (((Asbestos.[Cert Exp])<#8/5/2008#)) OR (((Asbestos.[KY
Exp])<#8/5/2008#) AND ((Asbestos.[IL Exp])<#8/5/2008#) AND ((Asbestos.[MI
Exp])<#8/5/2008#) AND ((Asbestos.[OH Exp])<#8/5/2008#) AND ((Asbestos.[WI
Exp])<#8/5/2008#) AND ((Asbestos.[NC Exp])<#8/5/2008#) AND ((Asbestos.[WV
Exp])<#8/5/2008#) AND ((Asbestos.[MO Exp])<#8/5/2008#) AND ((Asbestos.[AR
Exp])<#8/5/2008#));

Bob Barrows said:
You can do it yourself - just copy/paste John's sql into a query's SQL
View and switch to Design View to see what you were supposed to do in
Design View.

Mindy wrote:
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query of Expired Docs 3
validation rule in query? 4
Array.BinarySearch ALWAYS NEGETIVE 3
Formatting Data being extracted from SQL D/B 1
Adding fields produces aggregate function error 3
Web Setup Project failure 0
SP2 Issue 2
converting a for next loop into a for each loop 2

Top