Re: Show last records by date?

Thanks for your input Vincent!
I'm sorry if any of it was unclear. I was feeling a bit overwhelmed that day!

Basically I need a report for which clients to follow up with each day. It
shows me a week at a time, divided up by day. This report has been working
fine until last week, and all of a sudden it is not showing up right anymore!

I will clarify in your comments below:


"Vincent Johns" wrote:

I had to do a bit of guessing about your intentions, but here's what I
came up with.

The [Contacts].[Last Appointment] field seems useless. Maybe it's
connected somehow to [Events].[Next Action Date], but as it's used, it
has no effect on how records are displayed. (So, I deleted it for this
example.)

** You are right, this field has nothing to do with how the records are
displayed. It is purely for my information, so I can see the last time the
client was in.



It was not obvious to me which fields in your Tables are intended to be
unique.


** ClientsID (primary key in Contacts table), NotesID (primary key in Events
table), ID (primary key in Tracking table -- you are right, that was
confusing, and I have since changed it.) These are the unique fields in these
tables.


For the sake of brevity in my example here, I omitted some fields, such
as [Contacts].[Extension], which seemed to have no effect on which
records were returned by the Query, nor in what order they were sorted.
You can add them back into the definition of
[Q_DetailsForScheduledClients].

** Extension is just for the telephone #, so no, it has no effect on the
query.


Although it wasn't obvious, I assumed that [ClientID] was the primary
key of [Contacts] (but I would have preferred a name like [Contacts_ID],
suggesting the Table name), and that [NotesID] was the primary key of
[Tracking] (if so, I would have called it [Tracking_ID]).


** So noted, and will change them where needed.


If I guessed correctly about primary keys, either there's no need for
[Events].[NotesID], or there's none for [Tracking].[ClientID]. They
convey conflicting information. Is a record in [Tracking] attached to
one in [Contacts], or instead to one in [Events]? Based on the way your
[Query1] was defined I'm guessing that you want [Tracking] records
attached to a [Contacts] record, so I deleted the [Events].[NotesID] field.


**Tracking table is connected to Contacts table through ClientID. Events
table is also connected to Contacts table the same way.

Here are my example Tables.

[Contacts] Table Data*** View (omitting some fields):

ClientID First Last
---------- ----- ---------
-622026935 Harry Truman
938710486 *** Nixon
1263710568 Tom Jefferson

[Tracking] Table Data*** View:

NotesID Notes ClientID
----------- ----- ----------
-1489354944 Bb -622026935
-859240460 C# -622026935
1242603073 G 1263710568
1650189132 Eb -622026935

Since I believe that it usually makes no sense to expose raw foreign key
values to human beings, I almost always define a Lookup property for
such fields. In this case, the [ClientID] key points to a record in
[Contacts], so I set its Lookup property to display a name. (But be
aware that the value actually stored in this field is still a key value,
not the name you see here.) With Lookups specified, this same Table
looks like this:

[Tracking] Table Data*** View, with Lookup property set in [ClientID]
field:

NotesID Notes ClientID
----------- ----- ----------
-1489354944 Bb Harry
-859240460 C# Harry
1242603073 G Tom
1650189132 Eb Harry

**I don't have Lookups in my tables, as I have read in so many places that
that is a bad idea. Therefore I only use them in forms, where users need to
view them. For the report based on this query, I hide the ID anyway, and just
have the name fields show up.


The primary key, [Events_ID], in this next Table is probably
unnecessary. But some kinds of Queries depend on having a primary key
present, so I defined one here. As in the previous Table, [ClientID]
here has a Lookup property set.

[Events] Table Data*** View:

Events_ID Next Action Notes/ Next ClientID
Date Outcome Action
---------- ----------- ------- ------ --------
-114781609 3/5/2006 X C ***
-27642002 3/6/2006 Y B Harry
794639453 6/6/2006 F X Tom
1456897858 3/3/2006 Z A Harry


**This is correct -- this is basically what this table looks like. Except
the correct primary key for this table is NotesID.


Now for the Queries based on these Tables.

Your [Max Query] for these Tables wouldn't return any record, since the
maximum date (6/6/06) is outside the 1-week window you defined.

I assumed that you wanted, for each client, to know what the latest date
(within the next week) attached to his record is; this Query does that.
Tom is omitted by being outside the window, but *** and Harry are listed.



**That's Partly what I was looking for.... What I really need is to see, for
each client, the latest Next Action Date (within the week), ALONG with all
the other fields in the main query I quoted before (all Phone Number fields,
Email Address, Next Action, Notes/Outcome, and Tracking.Notes (which is
ill-named -- it should be Tracking.Status, and has been changed).

The results should look like what you have listed below in your
[Q_MaxDateForEachClient] query example.

Instead, I am getting results that look like your
[Q_DetailsForScheduledClients] query example. That is, Harry Truman is
showing up 3 times (for all of the Next Action Dates that he had this week)
instead of just once, for his LATEST "Next Action Date."

Also, another odd problem: when I "Group By" the Notes/Outcome and Next
Action fields, the data doesn't show up -- it shows little squares instead!
(The data will show up if I use the "Last" function instead, but of course,
that causes other problems.)

Any other ideas?
Thanks for your suggestions!
Rose.


[Q_MaxDateForEachClient] SQL:

SELECT Contacts.ClientID, Contacts.First,
Max(Events.[Next Action Date]) AS [MaxOfNext Action Date]
FROM Contacts INNER JOIN Events
ON Contacts.ClientID = Events.ClientID
GROUP BY Contacts.ClientID, Contacts.First
HAVING (((Max(Events.[Next Action Date]))
Between Date() And DateAdd("d",7,Date())));

[Q_MaxDateForEachClient] Query Data*** View:

ClientID First MaxOfNext Action Date
---------- ------ ---------------------
-622026935 Harry 3/6/2006
938710486 *** 3/5/2006

The last Query picks records in [Events] that match the selected Client
and Date values, and if any matching [Tracking] records exist, it lists
them as well (otherwise leaves the [Notes] field blank). In this case,
there are three records for Harry, since there are three [Notes] fields
linked to the Harry record, but none for ***.

[Q_DetailsForScheduledClients] SQL:

SELECT Contacts.First, Contacts.Last,
Events.[Next Action Date], Events.[Notes/Outcome],
Events.[Next Action], Tracking.Notes
FROM ((Contacts INNER JOIN Q_MaxDateForEachClient
ON Contacts.ClientID = Q_MaxDateForEachClient.ClientID)
INNER JOIN Events
ON (Contacts.ClientID = Events.ClientID)
AND (Q_MaxDateForEachClient.[MaxOfNext Action Date]
= Events.[Next Action Date]))
LEFT JOIN Tracking
ON Events.ClientID = Tracking.ClientID
ORDER BY Events.[Next Action Date], Contacts.First;

[Q_DetailsForScheduledClients] Query Data*** View:

First Last Next Action Notes/ Next Notes
Date Outcome Action
----- ------ ----------- ------- ------ -----
*** Nixon 3/5/2006 X C
Harry Truman 3/6/2006 Y B Eb
Harry Truman 3/6/2006 Y B C#
Harry Truman 3/6/2006 Y B Bb

-- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.

Rose H. wrote:

Hello,

I am having some trouble with a query. It is basically a query that is fed
to a report that shows what clients need to be followed up with in the next
week, by the Next Action date. It is based off of 3 tables, and has several
fields. The problem I am having is that it is not showing the last record for
each client. It is for some of them, but not for all! It seems to show
duplicates only when there are 2 different Next Action dates within the same
week. For example, I have a client that I am supposed to follow up with
tomorrow, so the Next Action Date is 3/1/06. However, the client actually
calls me today, and says to follow up with him next week. So the next follow
up record added has a Next Action Date of 3/6/06. Instead of now just showing
that new record, the query now STILL shows that the client needs to be
followed up with on 3/1/06 AND also on 3/6/06. So it is showing both records,
instead of just the last one. (Obviously this can get confusing, as I work
with a lot of clients daily, and can forget whether I have followed up with
them already or not -- this is the point of the query in the first place!) I
am using the Max function. The query is below:

SELECT Max(Events.[Next Action Date]) AS [MaxOfNext Action Date],
Contacts.First, Contacts.Last, Contacts.[Business Phone], Contacts.Extension,
Contacts.[Mobile Phone], Contacts.[Home Phone], Contacts.[Email Address],
Contacts.[Last Appointment Date], Last(Events.[Notes/Outcome]) AS
[LastOfNotes/Outcome], Last(Events.[Next Action]) AS [LastOfNext Action],
Tracking.Notes
FROM (Contacts INNER JOIN Events ON Contacts.ClientID = Events.ClientID)
INNER JOIN Tracking ON Contacts.ClientID = Tracking.ClientID
GROUP BY Contacts.First, Contacts.Last, Contacts.[Business Phone],
Contacts.Extension, Contacts.[Mobile Phone], Contacts.[Home Phone],
Contacts.[Email Address], Contacts.[Last Appointment Date], Tracking.Notes,
Events.NotesID, Contacts.ClientID
HAVING (((Max(Events.[Next Action Date])) Between Date() And
DateAdd("d",7,Date())))
ORDER BY Max(Events.[Next Action Date]);

As I was going through posts on here, trying to find the answer to the
problem, I saw several suggestions to make a Max query separately, with just
the ID and Date field that I wanted the Max of, and then join it to the other
tables in a new query. I tried that too. It is returning exactly the same
records as the one above, with exactly the same problem. This one is listed
below:

SELECT Contacts.ClientID, Events.NotesID, Contacts.First, Contacts.Last,
Contacts.[Business Phone], Contacts.Extension, Contacts.[Mobile Phone],
Contacts.[Home Phone], Contacts.[Email Address], Events.[Notes/Outcome],
Events.[Next Action], Tracking.Notes, [Max Query].[MaxOfNext Action Date]
FROM (Contacts INNER JOIN (Events INNER JOIN [Max Query] ON (Events.NotesID
= [Max Query].NotesID) AND (Events.[Next Action Date] = [Max
Query].[MaxOfNext Action Date])) ON Contacts.ClientID = Events.ClientID)
INNER JOIN Tracking ON Contacts.ClientID = Tracking.ClientID;

And here is the Max Query:
SELECT Events.NotesID, Max(Events.[Next Action Date]) AS [MaxOfNext Action
Date]
FROM Events
GROUP BY Events.NotesID
HAVING (((Max(Events.[Next Action Date])) Between Date() And
DateAdd("d",7,Date())))
ORDER BY Max(Events.[Next Action Date]);

Any ideas, anyone?
Thanks!
Rose.

.

Loading