G
Guest
Helloooooo 
IN BRIEF: I built a query which returns the correct set of records. Then i
added a MIN and COUNT function but had to turn on the query builders "TOTAL"
row to get the functions to work. Which im not sure was the correct way of
doing things cause now every row seems to be "grouped by"...? Anyways,the MIN
and COUNT functions seem to return the proper results, its just one last
field ([notes]date) that im unable to retrieve.
IN DETAIL:
I have the following [tables] linked on these fields;
[Volunteers].pkVolunteerID
[Requests].fkVolunteerID
[Notes].fkVolunteerID
Each record in [Volunteers]table can have 0 to multple records in the
[notes]table but every record in the [notes]table must be attached to a
record in the [volunteers]table. (also: The [Requests] table can not have any
[notes]records attached to it. And just ONEorNONE [Volunteer]records attached
to it). The primary keys for these tables are;
[Volunteers].pkVolunteerID
[Requests].pkRequestsID
[Notes].pkNotesID
So i created a query called, "Volunteers_NotAssigned", which lists all the
[Volunteers]records that have a [Requests]record whose [Requests]EndDate
field is Null;
SELECT Volunteers.pkVolunteerID, Volunteers.FirstName, Volunteers.LastName
FROM Volunteers
WHERE (((Volunteers.[Do Not Place])=False) AND ((Volunteers.Retired)=False)
AND ((Exists (SELECT DISTINCT Requests.fkVolunteerID FROM Requests WHERE
(((Requests.fkVolunteerID) Is Not Null) AND ((Requests.EndDate) Is Null) AND
(Requests.fkVolunteerID = Volunteers.pkVolunteerID) ) ))=False));
...and it returns the proper records Then i wanted more information about
the returned [Volunteers]records. In particular, for every [Volunteers]record
returned in the query above i wanted to know how many [Notes]records they
have (even if its zero). And if they have a [notes]record, i also wanted to
know the number of days elapsed since the date of their most recent
[notes]record. So, i turned on the "totals" row in the query builder, and the
resulting query looks like this;
SELECT Volunteers.pkVolunteerID, Volunteers.FirstName, Volunteers.LastName,
Min(DateDiff("d",[Notes]![Date],Now())) AS DaysSinceLastNote,
Count(Notes.pkNoteID) AS NumberOfNotes
FROM Volunteers LEFT JOIN Notes ON Volunteers.pkVolunteerID =
Notes.fkVolunteerID
GROUP BY Volunteers.pkVolunteerID, Volunteers.FirstName, Volunteers.LastName
HAVING (((Volunteers.[Do Not Place])=False) AND ((Volunteers.Retired)=False)
AND ((Exists (SELECT DISTINCT Requests.fkVolunteerID FROM Requests WHERE
(((Requests.fkVolunteerID) Is Not Null) AND ((Requests.EndDate) Is Null) AND
(Requests.fkVolunteerID = Volunteers.pkVolunteerID) ) ))=False));
And that seems to work. And Now id like to get the actual value from the
[Notes]DATE field of the actual [Notes]record from the expression
"Min(DateDiff("d",[Notes]![Date],Now())) AS DaysSinceLastNote" - which would
be the **date of the most recent note***. But ive been unable to figure that
one out.
Am i going about this the right way?
Cheers,
WebDude!
IN BRIEF: I built a query which returns the correct set of records. Then i
added a MIN and COUNT function but had to turn on the query builders "TOTAL"
row to get the functions to work. Which im not sure was the correct way of
doing things cause now every row seems to be "grouped by"...? Anyways,the MIN
and COUNT functions seem to return the proper results, its just one last
field ([notes]date) that im unable to retrieve.
IN DETAIL:
I have the following [tables] linked on these fields;
[Volunteers].pkVolunteerID
[Requests].fkVolunteerID
[Notes].fkVolunteerID
Each record in [Volunteers]table can have 0 to multple records in the
[notes]table but every record in the [notes]table must be attached to a
record in the [volunteers]table. (also: The [Requests] table can not have any
[notes]records attached to it. And just ONEorNONE [Volunteer]records attached
to it). The primary keys for these tables are;
[Volunteers].pkVolunteerID
[Requests].pkRequestsID
[Notes].pkNotesID
So i created a query called, "Volunteers_NotAssigned", which lists all the
[Volunteers]records that have a [Requests]record whose [Requests]EndDate
field is Null;
SELECT Volunteers.pkVolunteerID, Volunteers.FirstName, Volunteers.LastName
FROM Volunteers
WHERE (((Volunteers.[Do Not Place])=False) AND ((Volunteers.Retired)=False)
AND ((Exists (SELECT DISTINCT Requests.fkVolunteerID FROM Requests WHERE
(((Requests.fkVolunteerID) Is Not Null) AND ((Requests.EndDate) Is Null) AND
(Requests.fkVolunteerID = Volunteers.pkVolunteerID) ) ))=False));
...and it returns the proper records
Then i wanted more information aboutthe returned [Volunteers]records. In particular, for every [Volunteers]record
returned in the query above i wanted to know how many [Notes]records they
have (even if its zero). And if they have a [notes]record, i also wanted to
know the number of days elapsed since the date of their most recent
[notes]record. So, i turned on the "totals" row in the query builder, and the
resulting query looks like this;
SELECT Volunteers.pkVolunteerID, Volunteers.FirstName, Volunteers.LastName,
Min(DateDiff("d",[Notes]![Date],Now())) AS DaysSinceLastNote,
Count(Notes.pkNoteID) AS NumberOfNotes
FROM Volunteers LEFT JOIN Notes ON Volunteers.pkVolunteerID =
Notes.fkVolunteerID
GROUP BY Volunteers.pkVolunteerID, Volunteers.FirstName, Volunteers.LastName
HAVING (((Volunteers.[Do Not Place])=False) AND ((Volunteers.Retired)=False)
AND ((Exists (SELECT DISTINCT Requests.fkVolunteerID FROM Requests WHERE
(((Requests.fkVolunteerID) Is Not Null) AND ((Requests.EndDate) Is Null) AND
(Requests.fkVolunteerID = Volunteers.pkVolunteerID) ) ))=False));
And that seems to work. And Now id like to get the actual value from the
[Notes]DATE field of the actual [Notes]record from the expression
"Min(DateDiff("d",[Notes]![Date],Now())) AS DaysSinceLastNote" - which would
be the **date of the most recent note***. But ive been unable to figure that
one out.
Am i going about this the right way?
Cheers,
WebDude!