N
ngan
I've read the Help topic about what isn't updateable and queries with more
than one table usually falls into that category. But I am trying to do that
and need help with the code.
I have a table of NoShows (we log when a client doesn't show up for an
appt). I want to send out a letter to clients who has between 2 and 5
noshows within a floating 60 day period. Below is the query, qryNSCount,
that gives me the clientID:
SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID;
Now that I have the clientIDs, when someone goes to print the letter, I want
Access to timestamp each noshow record within that 60 days period so we know
that the client has been notified of those noshows.
Unfortunately, if I combine the Count query with the table tblNoshow, I
can't update:
SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow INNER JOIN qryNSCount ON tblNoShow.AUX_CLIENT_ID =
qryNSCount.AUX_CLIENT_ID WHERE LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2
Is it possible to split up the two queries and do a Loop function so I can
timestamp the letterprintdate and letterprintby?
I just don't know how to code it.
than one table usually falls into that category. But I am trying to do that
and need help with the code.
I have a table of NoShows (we log when a client doesn't show up for an
appt). I want to send out a letter to clients who has between 2 and 5
noshows within a floating 60 day period. Below is the query, qryNSCount,
that gives me the clientID:
SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID;
Now that I have the clientIDs, when someone goes to print the letter, I want
Access to timestamp each noshow record within that 60 days period so we know
that the client has been notified of those noshows.
Unfortunately, if I combine the Count query with the table tblNoshow, I
can't update:
SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow INNER JOIN qryNSCount ON tblNoShow.AUX_CLIENT_ID =
qryNSCount.AUX_CLIENT_ID WHERE LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2
Is it possible to split up the two queries and do a Loop function so I can
timestamp the letterprintdate and letterprintby?
I just don't know how to code it.