Who's due for a shot?

[Donna Brooks]

Hi,
I am having a little trouble figuring out how to design a
query to pull my necessary data, which may be due to the
way I set up the database. Our facility has to give
employees TB shots. They must have a first step, a second
step, and an annual shot. I would have just used one
record per employee, but the number of annuals is
ongoing. So what I done was, for example:

RecNo EmpName Description Date
1 Jo Blo First Step 01/01/02
2 Jane Doe First Step 02/04/02
3 Jo Blo Second Step 02/06/02
4 Jane Doe Second Step 03/04/02
5 Jo Blo Annual 01/01/03
6 Jane Doe Annual 02/04/03
7 Jo Blo Annual 01/01/04
8 Jane Doe Annual 02/04/04

Now,how would you query this to ask the question, Who is
due for a shot on [Enter Date]. Who is due for a shot and
what shot, first, second or annual?

Thanks,
Donna Brooks

 

[Martin]

Did you try using a cross tab query with annual, first step and second step
as the column heading and date as the row heading?

You can then query the cross tab on the selected date.

HTH.

Martin.

 

[Guest]

Hi,
I am having a little trouble figuring out how to design a
query to pull my necessary data, which may be due to the
way I set up the database. Our facility has to give
employees TB shots. They must have a first step, a second
step, and an annual shot. I would have just used one
record per employee, but the number of annuals is
ongoing. So what I done was, for example:

RecNo EmpName Description Date
1 Jo Blo First Step 01/01/02
2 Jane Doe First Step 02/04/02
3 Jo Blo Second Step 02/06/02
4 Jane Doe Second Step 03/04/02
5 Jo Blo Annual 01/01/03
6 Jane Doe Annual 02/04/03
7 Jo Blo Annual 01/01/04
8 Jane Doe Annual 02/04/04

Your structure is almost right. I would have a EmpFirstName and
EmpLastName. Then you can join them together by [EmpLastName] & ", " &
[EmpFirstName] or [EmpFirstName] & " " & [EmpLastName].

You also need to be able distinguish between employees with the same
name; maybe an employee number field.

Now,how would you query this to ask the question, Who is
due for a shot on [Enter Date]. Who is due for a shot and
what shot, first, second or annual?

It seems to me if you query for who is due for a shot on [Enter Date],
you are going to be running the report every day. If you build a query
to show the last shot date and description, on the form you could
calculate the next shot date and description.


The query would be (change the table name to yours):

SELECT [tblTest].[EmpName], [tblTest].[ShotDate], [tblTest].[Description]
FROM tblTest
WHERE (((tblTest.ShotDate) In (SELECT TOP 1 [Self].[ShotDate] FROM
[tblTest] AS [Self] WHERE [Self].[EmpName] = [tblTest].[EmpName] ORDER
BY [Self].[ShotDate] DESC)))
ORDER BY [tblTest].[EmpName];


Create a new form and set the Data Source to the query you just created.

Add a text box with the control source set to EmpName. Add another text
box. Set the caption to "Next Shot Date". In the control source enter
=DateAdd("y",1,[ShotDate]). Add a third text box. Set the control source
to =IIf([Description]="First Step","Second Step","Annual") and set the
caption to "Next Shot Desc"(or whatever you want to call it).


If you want to limit the "shots due" to this month and the next month,
change the query to (copy and paste this into the SQL view of the query):


SELECT tblTest.EmpName, tblTest.ShotDate, tblTest.Description,
Month([shotdate]), Year([shotdate]) AS Expr1
FROM tblTest
WHERE (((tblTest.ShotDate) In (SELECT TOP 1 [Self].[ShotDate] FROM
[tblTest] AS [Self] WHERE [Self].[EmpName] = [tblTest].[EmpName] ORDER
BY [Self].[ShotDate] DESC)) AND ((Month([shotdate])) Between
Month(Now()) And Month(DateAdd("m",1,Now()))) AND
((Year([shotdate]))=IIf(Month([shotdate])=12,(Year([shotdate])) Between
Year(Now()) And Year(DateAdd("yyyy",1,[shotdate])),Year(Now()))))
ORDER BY tblTest.EmpName;



HTH