First() won't work - subquery question

[Rémi]

Hello.
I've got a client table and an appointment table. I'd like to display
a list of clients and, for each, the date and reason of the *last*
appointment the client's had. (A read-only form, of course.) I've
been thinking about how to best query for this data.

I thought that First() or Last() aggregate functions - which I've never
used - might do the trick. My Jet SQL reference states that "they
return the value of a specified field in the first or last record,
respectively, of the result set returned by a query." This would work
great if First() and/or Last() take into consideration the ORDER BY
clause I apply to the subquery.

It was pretty easy to uncover the docs aren't quite accurate. (Or, I
suppose, the subquery used isn't considered to be the "result set
returned".) After loading a few test records, I tried this:

qrySortedAppointments:

SELECT clientId, appointmentId, appointmentDate
FROM tblAppointment
ORDER BY clientId, appointmentDate DESC;

qryTestFetchFirstApp:

SELECT clientId, First(qrySortedAppointments.appointmentId) As
MostRecentAppointment
FROM qrySortedAppointments
GROUP BY clientId


When changing qrySortedAppointment's sorting order from DESC to ASC, I
still get the same results in the second query.

My SQL is pretty good, but I'm scratching my head a bit with this
one... Easy enough to determine the earliest date with Min(), but I'll
have problems if a client had more than one appointment on a given day.
(The date field won't have a time component.) I also can't fetch the
Max() autoincrement, since they won't necessarily be entered in
chronological order.

Anyone offer suggestions?

Regards,
Remi.

 

[Allen Browne]

If you can't use Max(), use TOP 1 and include the primary key in the ORDER
BY clause so Access can distinguish between equals.

 

[Rémi]

Thanks for the reply, Allen.
I should have been more specific in my original post: won't work for
me, I'm aiming to setup a continuous form. Essentially, I want to
setup a snapshot of clients which can be filtered, and the user can
select any row to view client details much more closely.

(I had been browsing the JetSQL help files when I came across First(),
which I wasn't familiar with. And since the docs stated it was the
first record from the result set, I tried setting up a subquery that
would order the result set how I want it.)

As it is, my fall back is to have the form itself open a snapshot of
the appointment table, bind the fields to functions, and have the
functions return the correct value for the current clientId. I suppose
I had a nagging feeling in the back of my head that this shouldn't be
necessary :-(

Unless someone has another suggestion?

Regards,
Remi.

 

[Jason Lepack]

Remi,

Based upon your description in your first post the database has no firm
rule in place for determining which visit is the most recent.
Therefore, trying to order the list would be futile.

Say for example that I visit twice on this 1/23/2007, once at 13:00 and
again at 16:30. Now if each visit is given a consecutive ID all you
would need to do is find the max(ID) and then you would have my most
recent visit. However, you stated above that these visits may or may
not be entered in order. If they're not (16:30 is entered and then
13:00) then this result would return my 13:00 visit. Not acceptable.

Therefore all I can see is that based on your data definition you can
only determine that I visited twice on 1/23/2007 and give no more
accuracy than that.

I would suggest that instead of recording the date, record the time
with it too (that's why it's a date/time field). Then my visits would
have two unique times, 1/23/2007 13:00 and 1/23/2007 16:30.

Cheers,
Jason Lepack

 

[Rémi]

Jason,
everything you've said is correct. It is futile to determine which
appointment to select when two or more occur during the same day. I'm
not concerned about this scenario - I will be selecting one of the
records arbitrarily, and using it as the "most recent".

In case this problem I'm working on doesn't seem to make sense, I
should note that I'm not actually dealing with Client/Appointment,
which would obviously need a time component. I just thought this would
make a more clear boiled-down description of my problem for a post.
Point is, time is not an interesting feature to capture in my program -
too much detail, it simply isn't required for what I need.

Thanks for the post.
Remi.