Firstly your query is unnecessarily grouped as you are not aggregating any
values, and you need not use a LEFT JOIN as you are restricting it on columns
from the Asbestos table, which in effect makes it an INNER JOIN.
The [Todays Date] parameter can be replaced with the DATE() function which
returns the current date:
So, it can be simplified to:
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] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE (((Asbestos.[Cert Exp])<DATE())) OR (((Asbestos.[KY
Exp])<DATE()) AND ((Asbestos.[IL Exp])<DATE()) AND
((Asbestos.[MI Exp])<DATE()) AND ((Asbestos.[OH Exp])<DATE())
AND ((Asbestos.[WI Exp])<DATE()) AND ((Asbestos.[NC Exp])<[Todays
Date]) AND ((Asbestos.[WV Exp])<DATE()) AND ((Asbestos.[MO
Exp])<DATE()) AND ((Asbestos.[AR Exp])<DATE()));
You can return a result set which lists each contact along with any expired
certification by using a UNION ALL operation:
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "Cert" AS [Expired Certification]
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[Cert Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "KY"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[KY Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "IL"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[IL Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "MI"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[MI Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "OH"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[OH Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "WI"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[WI Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "NC"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[NC Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "WV"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[WV Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "MO"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[MO Exp]<DATE()
UNION ALL
SELECT [Contact Info].ID, [Contact Info].[First Name], [Contact Info].[Last
Name], [Contact Info].Address, [Contact Info].[Address 2], [Contact
Info].City,
[Contact Info].State, [Contact Info].Zip, "AR"
FROM [Contact Info] INNER JOIN Asbestos ON [Contact Info].ID = Asbestos.ID
WHERE Asbestos.[AR Exp]<DATE()
ORDER BY [Contact Info].[Last Name], [Contact Info].[First Name];
You might want to expand the constants "Cert", "KY", "IL" etc in the SELECT
clauses into more meaningful string expressions. You can use any word or
phrase you like.
This will return one row per contact for each expired certification, so if
you use this for a mail merge you might get more than one document per
contact. If you use an Access report to create the letters, however, you can
easily create one letter per contact, listing however many certification
expiries there are for the contact by grouping the report on the ID column,
putting the contact data in a group header and the certification expiries in
the detail section.
However, you are only having to resort to this because of a design flaw. By
having separate columns for each type of certification expiry date you are
doing what's known as 'encoding data as column headings'. This contravenes a
fundamental principal of the database relational model, which requires data
to be stored only as values at column positions in rows in tables.
What you should have in your Asbestos table are two columns such as
CertificationType and ExpiryDate, along with an ID foreign key column
referencing the primary key of Contact Info. That way you can simply join
the tables and return rows 'WHERE ExpiryDate < DATE()'. I've no doubt you
are thinking that you are in too deep to amend the design, but it would in
fact quite a simple task to fill a new, correctly structured, empty table
with the data from your existing table by executing a series of 'append'
queries, one for each certification type. Firstly you'd rename the Asbestos
table to Asbestos_Old and then execute queries such as, e.g. for KY Exp:
INSERT INTO Asbestos (ID, CertificationType, ExpiryDate)
SELECT ID, "KY", [KY Exp] FROM Asbestos_Old
WHERE [KY Exp] IS NOT NULL;
Repeat, amending as appropriate, for other certification types.
You should also have a CertificationTypes table with one column,
CertificationType, designated as its primary key and containing the values
KY, IL etc. By enforcing referential integrity in the relationship between
this and Asbestos only valid CertificationType values can be inserted into
the latter, this maintaining data integrity.
I would advise that you do normalize your database in this way, but the
decision on whether to do so or not rests with you of course.
Ken Sheridan
Stafford, England
Mindy said:
I have a query that runs expired certifications for me. I would like to drop
this information into a mail merge with "Your certification for ... (column
name)... has expired"
Is there a way to produce this information from my original query or by
querying off of my original?
I would also like it to NOT return columns that have a date, that has not
expired yet.
This is what I have right now:
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])<[Todays Date])) OR (((Asbestos.[KY
Exp])<[Todays Date]) AND ((Asbestos.[IL Exp])<[Todays Date]) AND
((Asbestos.[MI Exp])<[Todays Date]) AND ((Asbestos.[OH Exp])<[Todays Date])
AND ((Asbestos.[WI Exp])<[Todays Date]) AND ((Asbestos.[NC Exp])<[Todays
Date]) AND ((Asbestos.[WV Exp])<[Todays Date]) AND ((Asbestos.[MO
Exp])<[Todays Date]) AND ((Asbestos.[AR Exp])<[Todays Date]));
Your help is greatly appreciated.