Query of Expired Docs

[Mindy]

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.

 

[Ken Sheridan]

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

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.

 

[Mindy]

Um WOW. Ok, so I would rather, go ahead and rebuild the asbestos table than
doing the appends... I don't really understand it and I think it would be
easier to re-do it.

I can have duplicate entries... like all of my people are certified in more
than one state, so I could have something that would look like this?

Primary Key Cert Type Expire Date
4 Kentucky 4/11/2009
6 Missouri 6/12/2008
4 Illinois 5/12/2009

But I have all of my fields coming into my datasheets from a form... How
would I then need to change my form so I could have multiple entries on one
table?

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

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.

 

[Ken Sheridan]

The Cert Type and Expire Date columns are fine for recording the expiry dates
per state ( I now see what the KY, IL etc, values refer to!), but I'd simply
call the Cert Type column State as that's what it represents.

I'm not sure what the 'Primary Key' column is doing, however. Is this the
primary key of the new table, e.g. an autonumber? If so its not necessary as
the State (your Cert Type) and ID columns (more about the latter in a moment)
form a composite 'candidate key' of the table and can be designated as the
primary key in table design view. Or is 'Primary Key' in fact a foreign key
referencing the primary key of Contact Info? If the latter then call it ID
or similar (ContactID would be a better name in both tables). If not then
you need an ID or similar foreign key column to map rows in this table to the
relevant row in Contact Info.

Also you should have a States table with a State column as its primary key,
and one row per state, enforcing referential integrity in the relationship
with the new Asbestos table. This prevents an invalid state name being
entered in Asbestos, e.g. by a typo.

For data entry you'd have a Contacts form, in single form view, based on the
Contcat Info table, or better, based on a query which returns all columns
from the table and is sorted by LastName; the form will then show contacts
ordered by name.

Within the contacts form you'd have a subform based on the new Asbestos
table, or a query on the table ordered by State or by Expire date, whichever
you prefer the records to be sorted by. This can be in continuous form view
or datasheet view. It would need controls bound to the State (your Cert
Type) column and the Expire Date column. The latter should be a text box,
the former a combo box. With the States table suggested above the RowSource
property of the combo box would be:

SELECT State FROM States ORDER BY State;

The user can then select a state from the list. No control is needed for
the ID foreign key column as its value is inserted automatically into the
underlying table by the linking mechanism described below.

To create the subform you first create it as a continuous form or datasheet
view form in the usual way. With a form which is going to be used as a
continuous subform like this I usually set its NavigationButtons property to
False (No) and its ScrollBars property to 'Vertical Only'. That way a user
can scroll up and down the rows in the subform.

Then back in the contacts form in design view add a Subform Control to the
form, usually at the bottom of the detail section. Set its SourceObject
property to the name of the form you created above. Set its LinkMasterFields
and LinkChildFields properties both to ID (being the name of the primary key
of Contact Info and the corresponding foreign key in the new Asbestos table);
this links the parent form and subform in much the same way as you'd join the
tables on these columns if you were creating a query.

Size the subform control you added to the contacts form to get the best fit
for the subform.

When you open the contact form at a contact you can enter as few or as many
rows as necessary in the subform for that contact by selecting a state and
entering an expiry date in each case. As toy move between contacts the
subform will be requeried to show the certifications for the current contact.

One refinement you might like to add to the main contacts form is a combo
box to enable you to select a contact and move to their record. To do this
add an unbound combo box to the form in a suitable place and label it 'Find
Contact:' or similar. Set its properties up like so:

Name: cboFindContact

ControlSource: Leave blank.

RowSource: SELECT ID, [First Name] & " " & [Last Name] FROM [Contact
Info] ORDER BY [Last Name], [First Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

You'll now have to dabble in the black art of VBA code writing by putting
some code in the combo box's AfterUpdate event procedure. Don't worry, its
not as scary as it sounds. Firstly you need to open the VBA window at the
event procedure, which you do like this:

Select the combo box control in form design view and open its properties
sheet if its not already open. Then select the AfterUpdate event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Paste the code which I'll give you below between these two
existing lines.

The code works by first finding the selected ID in a clone of the form's
underlying recordset. It then synchronizes the form's Bookmark with the
recordset's Bookmark; this causes the form to move to the selected contact.
Here's the code:

Dim rst As Object
Dim ctrl As Control

Set ctrl = Me.ActiveControl
Set rst = Me.Recordset.Clone

With rst
.FindFirst "ID = " & Nz(ctrl,0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Now, you might move to a contact in a different way so to keep the combo box
in sync with the current contact put the following code in the form's Current
event procedure:

Me.cboFindContact = Me.ID

Remember that if you do change the name of the ID column in Contac Info to
ContactID or similar you'd need to change it in the above also. I've assumed
that ID is a number data type, not text. If it were text you'd need to make
one small change to the code:

.FindFirst "ID = """ & ctrl & """"

Finally, you have a table called Asbestos. Do you have other tables
representing certifications for other things? If so you should really
combine them into one table with an extra column, CertificationCategory say,
containing values for whatever the certification relates to, i.e. you'd have
'Asbestos' in this column for all the asbestos certification rows. This
extra column would be part of the primary key along with ID and State. You'd
then have another table, CertificationCategories with a primary key column
CertificationCategory to ensure data integrity through the enforcement of
referential integrity. The Asbestos table, now being all encompassing, would
then be renamed something like ContactCertifications of course.

If you do have separate table for each category of certification you are
again storing data other than as values at column positions in rows in
tables. It’s a very common mistake newcomers to relational databases make.
A good set of relational database tables should look like a local convention
of Barak Obama look-alikes, relatively few in number and mostly tall and
skinny >.

Ken Sheridan
Stafford, England

Um WOW. Ok, so I would rather, go ahead and rebuild the asbestos table than
doing the appends... I don't really understand it and I think it would be
easier to re-do it.

I can have duplicate entries... like all of my people are certified in more
than one state, so I could have something that would look like this?

Primary Key Cert Type Expire Date
4 Kentucky 4/11/2009
6 Missouri 6/12/2008
4 Illinois 5/12/2009

But I have all of my fields coming into my datasheets from a form... How
would I then need to change my form so I could have multiple entries on one
table?

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

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.