Re: Show last records by date?

Yayyyy, its working, it's working!! Thanks so much! I actually had it
returning the correct results Friday, but I couldn't get it to show the data
in the next action fields -- just the 2 little squares. Splitting off the Max
Query and not grouping on those other fields seems to fix the problem, though
darned if I know why! Anyway, it really seems to be doing what it's supposed
to now, and what a relief! Now that report can make my life easier like it's
supposed to, instead of harder!
Thanks Vincent!
Rose.

"Vincent Johns" wrote:

There is another problem with the [Contacts].[Last Appointment] field --
the semantics seems wrong. Specifically, it appears to me to be not
something that is characteristic of a human being (the subject of a
record in [Contacts]), but rather of an [Events] entity, or something
similar.

You seem to record only scheduled activities in [Events], not any facts,
but perhaps that wouldn't be a bad thing. Or you might put actual
visits into a separate Table. But attaching a date to a record for a
human being means that you must continually update just that field, for
no other reason than that the person showed up for a meeting. And then
you lose all historical information (such as when the previous meeting
occurred). Perhaps it's not a huge issue; maybe that information isn't
vital for you to track. But in that, case, maybe you don't even need
that one field, never mind a record of all the times a person in
[Contacts] came to a meeting.

Concerning Lookup properties, I have read some of the rationale cited in
messages on this newsgroup for avoiding them, and few of the arguments
seemed to me to be compelling (at least as regards foreign keys), except
for the one about making it confusing to a reader whether he's looking
at an actual stored value or instead at a translated key value. Since
my foreign-key names normally end in "_ID", I think that usually makes
it kind of clear. Of course, in a case where the key value actually
means something, there's no need to hide it, so I don't use Lookup
properties there.

The Lookup property will mostly benefit you, if you choose to use it, as
you try to debug your Tables by looking at them in data*** view. Your
users will likely always use Forms for input and Reports for output, and
you can base those on Queries that display just what you want them to
display (making any Lookup properties kind of immaterial). Since my
message dealt with debugging Tables and Queries, and employed data***
views, I figured putting Lookup properties on the foreign keys was kind
of mandatory if I wanted the datasheets to be reasonably legible.

When you said, "The results should look like what you have listed below
in your [Q_MaxDateForEachClient] query example," bear in mind that that
Query's only purpose was to associate with each [Clients] record having
a date within your window the last such date. I had no intention of
adding any fields there.

In the other Query, [Q_DetailsForScheduledClients], the reason that
Harry Truman is showing up 3 times has nothing to do with the number of
Next Action Dates that he had this week. It's because he has 3
[Tracking] records attached to him. He'll always appear 3 times in the
Query Data***, regardless of any dates appearing in [Events], as long
as any one such date is within your selection window. He'll always
appear either zero or three times.

Concerning your little squares... I certainly didn't get any of those,
as I suppose you saw. But then I also didn't use "Group By", since the
only aggregate funtion that I apparently needed I took care of in
[Q_MaxDateForEachClient]. Little squares might easily show up if the
data are Boolean -- but I couldn't determine from your message what kind
of data are in your Tables, and I may have guessed wrong. You can
probably determine to what extent by examining my examples. If the data
ARE Boolean, you can change the format for displaying them, if you don't
like little boxes.

I suggest avoiding the "Last" function unless you really want done what
it does. (I suppose I could say that about just about any other
function, too.)

When you said that what you really need is to see the latest Next Action
Date (within the week), ALONG with all the other fields in the main
query (all Phone Number fields, &c.), that's why I suggested that, once
you've gotten [Q_DetailsForScheduledClients] to produce the set of
records you want, you should simply add those other fields to it. The
main reason that [Q_DetailsForScheduledClients] exists is to allow you
to tack additional fields onto the output of [Q_MaxDateForEachClient],
without cluttering up [Q_MaxDateForEachClient]. So I suggest that you
be sure that [Q_MaxDateForEachClient] is giving you the correct dates,
for the correct members of [Contacts], and then have
[Q_DetailsForScheduledClients] produce the proper details for each of those.

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


Rose H. wrote:

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
.