Query - only certain records based on dates.

[George Avery]

Hello,

Can someone please help me with my mental block.

I have a table that records visits to clients.
There are multiple entries per client. I am reporting on clients who have -

1. Been visited in the last 12 months [Date of Visit]>(Date()-366). That
works fine.

2. Not had a visit in the last 12 months. What I need to do is completely
exclude the records where a visit is recorded less than 12 months ago. At
the moment I am getting records of visits older than a year but some of
them have also got visits withing the last year.
T.I.A.
GA

 

[George Avery]

oops...
What I meant to say was -
2. Not had a visit in the last 12 months. What I need to do is completely
exclude the records where a visit is recorded in the last 12 months ago
notwithstanding other older visits.

 

[George Avery]

Rick Brandt wrote:
[snip]

Thanks Rick
This -
WHERE (((Visits.[Date of visit]) Not In (SELECT [Date of visit] FROM
Visits WHERE [Date of visit]>(Date()-366)) And [Date of
visit]<(Date()-366)))

gives me the same result as -
([Date of visit]<(Date()-366)

or have I missed something.
GA

 

[George Avery]

[snip]

Your SQL is looking for records where [Date of visit] is not in the sub-query. You
need to use the field(s) that uniquely identifies clients.

Rick
....doh - On my way home I realised that I hadn't followed your advice
i.e. I didn't use the Primary Key. I'll try and get it right tomorrow.
Thanks
GA

 

[Dick Adams]

You need two things:
1. A table of all clients, each with a number: ie
Clientnumber Auto number
Clientname Text
2. A table of all appointments: ie
Appointment Auto Number
Clientname Text
Clientnumber (from above table)
Visitdate Date (short date)


Then join your query for "visits in last year" to the
client list with the second selection in Join Properties
Set criteria for visitdate to Is Null
The result will give those clients who have not visited
within last year!!!
Any questions, contact me.

 

[George Avery]

Rick,
Many thanks worked a treat when I did it 'properly'
GA
p.s. new thread for next problem :^)