The query I recommended will display the ClientID's and the dates they were
last seen when the date last seen is in the date of service column, as long
as that date was more than 60 days ago. However, if there's no date listed,
then Jet won't know _which_ date it should be, so that record won't be
displayed. If you want to bring up all of these records without dates along
with those clients who were last seen more than 60 days ago, then try:
SELECT ClientID, [date of service]
FROM tblMyTable
WHERE (([date of service] < DateAdd( "d", -60, Date( ))) OR (ISNULL([date of
service])))
ORDER BY [date of service];
.. . . where tblMyTable is the name of the table.
We have to
discharge clients ID's who have not come in 60 days from the 1st of every
month.
Whoa. "60 days from the 1st of every month" will almost always be a
different date than "more than 60 days ago." The only time these two dates
will match is on the first of every month. If it needs to be 60 days from
the 1st of the current month, no matter which day of the month the query is
run, then please let me know and I can alter the query for you. And you
might as well give the name of the table, because I already know the ClientID
and date of service columns, so you could copy and paste the final query
right into your SQL View pane.
should i be running dates or client ID'?
You should be comparing dates if you want to find the clients' previous
service dates that are more than 60 days ago.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
Confused said:
Confused said:
Help!!
would anyone know how to build a query that can distinguish clients who have
not recieved services in the last 60 days? this query would run weekly to
find those clients who have not been seen 60 days or more so that we can
update our data for each client.
I have fields that contiain [date of service];[ClientID]
69 Camaro thanks fo the help
its not bringing back clientID that recievd services six months ago. Maybe
I'm asking the wrong question.
Could Access retrieve clientID's that have not been in to recieve services
in the past 60 days?
Our system scans data into access so the table has multiple entries of the
same clientID(No Primary Key).Primary key is a AutoNumber. the table holds
5000 records. Clients come in for different types of services. We have to
discharge clients ID's who have not come in 60 days from the 1st of every
month.
should i be running dates or client ID'? I would like dates so that I could
see multiple ClientID's?
again thanks for all the help!! been trying different things My minds about
to burst!!
