Select query for MAX Date

[DevTycoon]

I need to find the frequency of client's appointment activity after their last appointment in a 4 month window.

My question is:
(1) Is my SQL going to give me the last time a client was in the office within the 4 month window?

(2) What will my SQL need to like to count frequency of appointments before and after window for each client?

(3) Can I do (2) in the same SQL shown above or will I need to make a new SQL statement?

SELECT tblTest.Client, Last(tblTest.AppointmentDate) AS LastOfAppointmentDate INTO tblTest
FROM tblTest
GROUP BY tblTest.Client
HAVING (((Last(tblTest.AppointmentDate))<#4/30/2014#))
ORDER BY Last(tblTest.AppointmentDate);

Details

Date Window I need to select the clients last appointment: Jan 1, 2014 - April 30, 201

Once the last appointment has been determined for each client in that window I need to count the frequency of appointments before and after that date.

Thanks for any feedback. This community is DA BOMB.

 

[plog]

1. Maybe. Last (http://www.techonthenet.com/access/functions/advanced/last.php) is a horrible function and should be removed from Access. You should instead use MAX(http://www.techonthenet.com/access/functions/numeric/max.php)

2. You would need to move the HAVING clause of the query you have now to the SELECT clause and use it in a conditional statement:

SUM(Iif(AppointmentDate<=#4/30/2014, 1,0)) AS AppointmentsBefore

You would make another one to determine appointes after your date.

3. Yes, you can get it all in one, by making a conditional field that returns the greatest appointmentdate after your date.



Now that I read your details, I see you will need a WHERE clause that limits the data to just those with an appointmentdate greater than 1/1/2014

 

[plog]

Now that I re-reread your post I see you don't want appointmentdates greater than 1/1/2014, you just want ones outside that window. Let's see if I understand:

You want the total number of visits before and after 1/1/2014 - 4/30/2014 for patients with a visit inside 1/1/2014 - 4/30/2014 and also their last vist date between 1/1/2014 - 4/30/2014.

This means a person whose last visit was 12/31/2014 wouldn't show in this query. Nor would someone whose only visit in 2014 occured on 5/1/2014.


Try this SQL:

SELECT Client, MAX(Iif(AppointmentDate>=#1/1/2014# AND AppointmentDate<=#4/30/2014#, AppointmentDate, "1/1/2000") AS LastWindowVisit, SUM(Iif(AppointmentDate>#4/30/2014, 1,0)) AS AppointmentsAfterWindow, SUM(Iif(AppointmentDate<#1/1/2014, 1,0)) AS AppointmentsBeforeWindow
FROM tblTest
HAVING MAX(Iif(AppointmentDate>=#1/1/2014# AND AppointmentDate<=#4/30/2014#, AppointmentDate, "1/1/2000") > #1/1/2000#
ORDER BY MAX(Iif(AppointmentDate>=#1/1/2014# AND AppointmentDate<=#4/30/2014#, AppointmentDate, "1/1/2000")

 

[DevTycoon]

Now that I re-reread your post I see you don't want appointment dates greater than 1/1/2014, you just want ones outside that window. Let's see if I understand:

You want the total number of visits before and after 1/1/2014 - 4/30/2014 for patients with a visit inside 1/1/2014 - 4/30/2014 and also their last visit date between 1/1/2014 - 4/30/2014.

This means a person whose last visit was 12/31/2014 wouldn't show in this query. Nor would someone whose only visit in 2014 occurred on 5/1/2014.

(1) I need to determine the last time a client made an appointment inside the window(this example is a 4 month window at the beginning of 2014, later I may change the window to a 4 day window. I want to use Jan 1,2014 - April 30th 2014 for this example).
(2) Once I determine the date from (1), for each client, I need to use that date as a normalization date and count the frequency of appointments before and after the normalization date (normalization date aka "day zero", aka "their last appointment made in that window").

This means a person whose last visit was 12/31/2013 wouldn't have a normalization date assigned to their client ID for this query. Someone who had visited at least once in that window and "n" times either before/after the normalization date would then have a frequency attached to their client id. There would be two frequency fields. Post Normalization date and a pre-normalization date appointment frequency.

If someone visited march 3,2014, march 8th, April 4th and July 5th 2014 the query needs to determine the normalization date as April 4th (last appointment made inside the window) and also needs to count that they have two pre normalization date appointments and one post normalization date appointments.

Once I get this figured out I may need to play with the window that normalization dates are selected from and possibly dimension that as a variable from an input box. For now I feel that tacking solid SQL to represent this situation will aid in any changes I want to make to the normalization date.

The sql results will help me determine how sensitive the appointments are to a specific time in history. This will help predictions of future appointments. I hope.

Knowing this how would your recommendation on SQL change? Thanks for the insight!!!!!!

 

[plog]

I need one more example explained to make sure I have it. These are a patients visits:

12/30/2013
12/31/2013
1/1/2014
1/2/2014
2/2/2014
3/8/2014
4/8/2014
5/1/2014
5/3/2014

What does the query return for that patient when the window is 1/1/2014 - 4/30/2014?

 

[DevTycoon]

I need one more example explained to make sure I have it. These are a patients visits:

12/30/2013
12/31/2013
1/1/2014
1/2/2014
2/2/2014
3/8/2014
4/8/2014 ***************> THIS WOULD BE THE NORMALIZATION DATE
5/1/2014
5/3/2014

What does the query return for that patient when the window is 1/1/2014 - 4/30/2014?

(1) Query would select 4/8/2014 as the normalization date for this client (last date visited in window )


(2) Pre Normalization date attendance count: Six visits (not including normalization date's visit shown in blue or the post norm dates in grey)

Example on what fields will trigger query to count are shown in red:
12/30/2013
12/31/2013
1/1/2014
1/2/2014
2/2/2014
3/8/2014
4/8/2014
5/1/2014
5/3/2014


(3) post normalization date attendance count: Two visits (not including normalization date's visit shown in blue or the per-norm date visits in grey)

Example on what fields will trigger query to count are shown in red:

12/30/2013
12/31/2013
1/1/2014
1/2/2014
2/2/2014
3/8/2014
4/8/2014
5/1/2014
5/3/2014


*******CURRENT QUESTION THAT NEEDS A SOLUTION ******

Reminder (1): each customer will have their own normalization date because they will not all have their last appointment on the same day inside an arbitrary time window such as (Between #1/1/2014# AND #4/30/2014#)

Reminder (2): pre and post norm date attendance count will not have a time constraint

Query result for cusomerID = 1

cusomerID: 1
DateNorm: 4/8/2014
PreNormCount: 6
PostNormCount: 2

***********************BONUS QUESTION*******************
How could this SQL for the first query example be modified to put a time constraint on the counter?...Perhaps only count activity +\- 35 days from norm date not including norm date. The normalization date window will not change and will be Between #1/1/2014# AND #4/30/2014#.
*************************************************************


Reminder (1)
+/- 35 day counter window

reminder (2)
Normalization date selection Between #1/1/2014# AND #4/30/2014#
(120 DAY WINDOW TO SELECT NORMALIZATION DATE)


Query result for cusomerID = 1
cusomerID: 1
DateNorm: 4/8/2014
PreNormCount: 1
PostNormCount: 2




Thanks for fully clarifying my need. I really appreciate the help because I am new to databases in general. They don't teach us this stuff in college .

 

[Galaxiom]

Last is a horrible function and should be removed from Access.

Although First and Last are much maligned they do actually have a purpose.

Sometimes multiple records may have the same value in a field that we only want once. First or Last can be used instead of Group By which is much more intensive.

 

[plog]

I think I have it. Can you provide me with data though? It doesn't have to be real, but an actual data would help. I would need the Appointments table and then the new table you speak of which gives customerID and the date range of each of them to include (e.g. 35 days, 45 days, etc).

Galaxiom: They are rightfully maligned regardless of having a purpose or not (which I'm not convicned they do). Intuitively people, DevTycoon as just one of many examples you will find on this forum, expect them to behave a specific way and they do not. Blame him if you want, but when 99% of people have the same expectation its actually the keywords fault for not behaving the way people expect.

 

[DevTycoon]

I think I have it. Can you provide me with data though? It doesn't have to be real, but an actual data would help. I would need the Appointments table and then the new table you speak of which gives customerID and the date range of each of them to include (e.g. 35 days, 45 days, etc).

I have yanked some data from north wind and made a test database for you. I made a form and query for both scenarios. The second one is the more complicated of the two. I am only really only interested in the SQL statements on the queries for each scenario becuase I will not be using the forms on my actual project. I will most likely change the text box references in the queries to variables or list box inputs. I just wired the queries to the text boxes so you know exactly what I am trying to do with my logic. The test database I have made will simulate my needs and expected query results.


Also, If you run the queries AS IS, the query format I desire will show. Please run the queries for each scenario before you start solving my needs. The strings are concatenated with dates and client ID's to show you exactly what I am looking for in the query results.

I am sorry I did not do this earlier. I think I could have helped you make better use of your time helping me. The only code in the test db is the macro on each form's button to run the query. If you dont want to enable macros just make sure the form is open when you manually run the query.


Scenario #1 does not include references to controlls in the red box. That is for the second scenario. Also I want to accomodate the solutions for both scenarios in one sql statment when this post is solved. If possible.


Tell your boss i said to give you a raise!!

 

[plog]

Wow, this issue is a sweater that just keeps unraveling. Here's what I'll do-- I'll create a query to produce the results you initially asked for. You can substitute in the Form data fields where necessary.

I got all the information you wanted into 1 query, but it uses a sub-query to determine the Normalization date of each client. This is that SQL:

SELECT Appointments.ClientID, Max(Appointments.AppointmentDate) AS NormalizationDate
FROM Appointments
WHERE (((Appointments.AppointmentDate)>=#1/1/2014# And (Appointments.AppointmentDate)<=#4/30/2014#))
GROUP BY Appointments.ClientID;

Paste that into a query and name it 'sub_NormalizationData'. I've hard coded the window dates into it, so the above query is the one you will need to modify to change the window. Below is the SQL for the query that gives you what you want:

SELECT sub_NormalizationData.ClientID, sub_NormalizationData.NormalizationDate, Sum(IIf([AppointmentDate]<[NormalizationDate],1,0)) AS PreNormalizationVisits, Sum(IIf([AppointmentDate]>[NormalizationDate],1,0)) AS PostNormalizationVisits
FROM sub_NormalizationData INNER JOIN Appointments ON sub_NormalizationData.ClientID = Appointments.ClientID
GROUP BY sub_NormalizationData.ClientID, sub_NormalizationData.NormalizationDate;

If you want to change the number of days before/after the Normalization date to look for visits, you would do it in the above query by adding a DateDiff function inside the Iif statements.

If you find any records not returning the right results, provide me with the client ID and what should be produced. Otherwise, I think this does what you want.

 

[DevTycoon]

Wow, this issue is a sweater that just keeps unraveling.

Hahah I am in agreement with you. I will test now and appreciate your help.

As far as unraveling goes...

Can I count client visits in the normalization time window and only run the query on clients with 2 or more visits in the normalization time window for example.

I will play with this and get back to you. Thanks for being patient with my request.!!

 

[plog]

Can I count client visits in the normalization time window...

Yes, you you would edit the second SQL I gave you. Its similar to how you would change it to only include X amount of days before/after the Normalization date. You would change this part:

Sum(IIf([AppointmentDate]<[NormalizationDate],1,0)) AS PreNormalizationVisits

To this:

Sum(IIf([AppointmentDate]<[NormalizationDate] AND [AppointmentDate]>=#1/1/2014#,1,0)) AS PreNormalizationVisits

...only run the query on clients with 2 or more visits in the normalization time window

This would be accomplished in the sub-query. In design view, bring down ClientID and underneath it change it from Group By to Count, then add >=2 to the criteria section.

 

[DevTycoon]

I still have many questions on this but for now I can mark this thread solved becuase my immediate question was answered. I am going to formalize my other questions and post new threads if they become pressing.

Thanks for you interest in my questions!

 

[Galaxiom]

Galaxiom: [First and Last] are rightfully maligned regardless of having a purpose or not (which I'm not convicned they do).

Your inability to understand their use does not constitute a a reason to deprecate them.

Intuitively people, DevTycoon as just one of many examples you will find on this forum, expect them to behave a specific way and they do not. Blame him if you want, but when 99% of people have the same expectation its actually the keywords fault for not behaving the way people expect.

Many newcomers to Access expect the data in a table to have an intrinsic order based on the sequence they were entered too. Do we implore Microsoft to change the reality to match the naive expectation or do we educate the user?

 

[vbaInet]

I'm with Galaxiom on this one. First and Last definitely have their uses. Min and Max work on ordered sets whereas First and Last work on unordered sets.

If you simply want the first or last record(s) (without an underlying sort involved) you would use First and Last. These aggregate functions are less costly when the task at hand will yield the same result as Min or Max.

They may seem similar but they really aren't.

 

[plog]

Your inability to understand their use does not constitute a a reason to deprecate them.

I hate to be "that guy", but I have to use your own words against you:

http://www.access-programmers.co.uk/forums/showthread.php?t=214802

Post #5:

First and Last should be completely avoided as they are unreliable. Always use Min and Max

I have seen queries with my own eyes where First of a field previously subjected to Order By Ascending returns a different record from Last of the same field Order By Descending.

Maybe you've been able to come to understand First/Last since then, but that proves my point. First/Last do not do what people expect.

Even worse, I can't find a link to show them how it works (or help me understand); that's how I came upon your post. I wasn't looking to throw your own words in your face, that's just where my google search lead me. I was trying to find a Microsoft link to explain First/Last and the above link is returned before anything explaining First/Last.

A few months ago I was having a similar discussion about First/Last and did find a microsoft link sort of explaining how it worked, except that explanation essentially said First/Last would return a random result. I was going to show it to you, but I could no longer find that link or my post so I went to google for help. When trying to find out how First/Last works, your 3 year old post appears before anything helpful from Microsoft.

Can you provide a microsoft link that will explain how they intended it to work? Hopefully that will help me understand and then allow me to help others understand.

 

[Galaxiom]

I few months ago I was having a similar discussion about First/Last and did find a microsoft link sort of explaining how it worked, except that explanation essentially said First/Last would return a random result.

I remember the same claim but didn't think it likely.

Can you provide a microsoft link that will explain how they intended it to work? Hopefully that will help me understand and then allow me to help others understand.

I wish I could. It is never really well explained anywhere I have come across.

I came to the conclusion about how it works through elimination. The thread you linked to was an expression of my observation that the results of First() and Last() didn't make sense in the context of ordering either, despite the conjecture I had read on the subject.

My conclusion is that First() means the first record Access comes across while doing other stuff. It is what you want when you don't care what the value is in the field because they will all give equally valid, if not the same, answer in the context of the query.

I have never ventured to just what they meant by Last(). The last value it came across without bothering to look again?

More specifically, I think First() and Last() return the results from an unordered recordset produced at the whim of the engine. First() searches from the beginning while Last() searches from the end or the recordset.

As such they are next to useless functions unless you really don't care which record it returns. Not quite "random" though.

And yes they do have a use in an aggregate query. In a Group By we ask the engine to compare the value of every record in that field. That is a big overhead.

If you don't care which record is returned then use First() if the record you are looking for is likely to be old and Last() if it is likely to be recent. All bets are off it the database has been compacted.

The most incisive thing anyone could do is determine the query asked by a database server when Access translates First() or Last() over ODBC because these functions are not standard SQL.

 

[Galaxiom]

Can you provide a microsoft link that will explain how they intended it to work? Hopefully that will help me understand and then allow me to help others understand.

BTW Don't count on Microsoft having the last word on how Access works. I don't think they really know either.

I know of at least two seriously persistent myths and one blatant error in their documentation.

 

[DevTycoon]

If I want to count products sold for each appointment with respect to the normalized date how do you recommend I would alter the query. What I am trying to do is generate all of this in one query for an excel plot. Thank you.