pull date from list of dates

[kdoran]

Is it possible to pull a specific date range from a table of dates to see if there was an entry made weekly.

tblFieldTag

FieldTagID
WeeklyCheck (yes/no)
MonthlyCheck (yes/no)

tblFieldNotes

FieldNotesID (autonumber)
FieldTagID (This is a required field)
FieldNotesDate
FieldNotesText

for example here is a list of tblFieldNotes entries (minus autonumber)

FieldTagID01 01/02/06 weekly check completed
FieldTagID02 01/03/06 weekly check completed
FieldTagID01 01/12/06 weekly check completed
FieldTagID02 01/13/06 weekly check completed
FieldTagID01 01/23/06 weekly check completed
FieldTagID01 01/12/06 weekly check completed
FieldTagID02 02/13/06 weekly check completed

I need to find out if the check was done or not for each week and be able to report out.

Any ideas?

thanks,
kel

 

[sxschech]

Option 1: (If you plan to do counts etc)
SELECT FieldNotesDate
FROM tblFieldNotes
WHERE FieldNotesText Is Not Null
GROUP BY FieldNotesDate;

Option 2:
SELECT DISTINCT FieldNotesDate
FROM tblFieldNotes
WHERE FieldNotesText Is Not Null;

If you only want to see records for FieldNotesText that say exactly "weekly check completed", then change

WHERE FieldNotesText Is Not Null

to

WHERE FieldNotesText = "weekly check completed"

 

[kdoran]

Unfortunately the text will never be the same and may be empty at times.

 

[PHV]

A starting point.
Create a query named rankFieldNotes:
SELECT A.FieldNotesID, A.FieldTagID, A.FieldNotesDate, A.FieldNotesText, Count(*) AS Rank
FROM tblFieldNotes AS A INNER JOIN tblFieldNotes AS B ON (A.FieldNotesDate >= B.FieldNotesDate) AND (A.FieldTagID = B.FieldTagID)
GROUP BY A.FieldNotesID, A.FieldTagID, A.FieldNotesDate, A.FieldNotesText;
And now a query to get periods with missing weekly checks:
SELECT X.FieldTagID, X.FieldNotesDate, X.FieldNotesText, Y.FieldNotesDate, Y.FieldNotesText
FROM rankFieldNotes AS X INNER JOIN rankFieldNotes AS Y ON X.FieldTagID = Y.FieldTagID AND X.Rank = Y.Rank - 1
WHERE Y.FieldNotesDate - X.FieldNotesDate > 13;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886