Show last records by date?

[Guest]

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.

 

[Vincent Johns]

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.)

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

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].

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]).

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.

Here are my example Tables.

[Contacts] Table Datasheet View (omitting some fields):

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

[Tracking] Table Datasheet 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 Datasheet View, with Lookup property set in [ClientID]
field:

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

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 Datasheet View:

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

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 Dick and Harry are listed.

[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 Datasheet View:

ClientID First MaxOfNext Action Date
---------- ------ ---------------------
-622026935 Harry 3/6/2006
938710486 Dick 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 Dick.

[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 Datasheet View:

First Last Next Action Notes/ Next Notes
Date Outcome Action
----- ------ ----------- ------- ------ -----
Dick 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 <[email protected]>
Please feel free to quote anything I say here.

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.

 

[Guest]

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:

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 Datasheet View (omitting some fields):

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

[Tracking] Table Datasheet 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 Datasheet 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 Datasheet View:

Events_ID Next Action Notes/ Next ClientID
Date Outcome Action
---------- ----------- ------- ------ --------
-114781609 3/5/2006 X C Dick
-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 Dick 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 Datasheet View:

ClientID First MaxOfNext Action Date
---------- ------ ---------------------
-622026935 Harry 3/6/2006
938710486 Dick 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 Dick.

[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 Datasheet View:

First Last Next Action Notes/ Next Notes
Date Outcome Action
----- ------ ----------- ------- ------ -----
Dick 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 <[email protected]>
Please feel free to quote anything I say here.

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.

 

[Vincent Johns]

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 datasheet 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 datasheet
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 Datasheet, 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 <[email protected]>
Please feel free to quote anything I say here.

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:

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 Datasheet View (omitting some fields):

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

[Tracking] Table Datasheet 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 Datasheet 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 Datasheet View:

Events_ID Next Action Notes/ Next ClientID
Date Outcome Action
---------- ----------- ------- ------ --------
-114781609 3/5/2006 X C Dick
-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 Dick 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 Datasheet View:

ClientID First MaxOfNext Action Date
---------- ------ ---------------------
-622026935 Harry 3/6/2006
938710486 Dick 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 Dick.

[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 Datasheet View:

First Last Next Action Notes/ Next Notes
Date Outcome Action
----- ------ ----------- ------- ------ -----
Dick 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 <[email protected]>
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.

 

[Guest]

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.

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 datasheet 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 datasheet
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 Datasheet, 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 <[email protected]>
Please feel free to quote anything I say here.

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:

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 Datasheet View (omitting some fields):

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

[Tracking] Table Datasheet 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 Datasheet 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 Datasheet View:

Events_ID Next Action Notes/ Next ClientID
Date Outcome Action
---------- ----------- ------- ------ --------
-114781609 3/5/2006 X C Dick
-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 Dick 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 Datasheet View:

ClientID First MaxOfNext Action Date
---------- ------ ---------------------
-622026935 Harry 3/6/2006
938710486 Dick 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 Dick.

[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 Datasheet View:

First Last Next Action Notes/ Next Notes
Date Outcome Action
----- ------ ----------- ------- ------ -----
Dick Nixon 3/5/2006 X C

 

[Vincent Johns]

You're most welcome! (Now, have you backed up your database file?)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

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.

:

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 datasheet 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 datasheet
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 Datasheet, 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 <[email protected]>
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:

:


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 Datasheet View (omitting some fields):

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

[Tracking] Table Datasheet 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 Datasheet 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 Datasheet View:

Events_ID Next Action Notes/ Next ClientID
Date Outcome Action
---------- ----------- ------- ------ --------
-114781609 3/5/2006 X C Dick
-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 Dick 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 Datasheet View:

ClientID First MaxOfNext Action Date
---------- ------ ---------------------
-622026935 Harry 3/6/2006
938710486 Dick 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 Dick.

[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 Datasheet View:

First Last Next Action Notes/ Next Notes
Date Outcome Action
----- ------ ----------- ------- ------ -----
Dick Nixon 3/5/2006 X C