Returning only MAX(date) not all dates

D

Dana F. Brewer

I want to be able to list the most current date an item is checked out. As I
was working through the query I used max as an aggregate function but I still
can't get the simple result I want. I'd rather list the fields I have chosen
and somehow select just the most current checkout date. Any help would be
greatly appreciated. Here is my query:

SELECT tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS
CheckedOut, tblTerritoryCheckout.DateCheckedIn AS CheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber
FROM (((tblTerritory LEFT JOIN tblTerritoryCheckout ON
tblTerritory.TerritoryID=tblTerritoryCheckout.TerritoryID) LEFT JOIN
tblPublisher ON tblTerritoryCheckout.PublisherID=tblPublisher.PublisherID)
INNER JOIN tblTerritoryType ON
tblTerritory.TerritoryTypeID=tblTerritoryType.TerritoryTypeID) INNER JOIN
tblCongregation ON tblTerritory.CongregationID=tblCongregation.CongregationID
GROUP BY tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription], tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, tblTerritoryCheckout.DateCheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber, tblCongregation.CongregationName
HAVING (((tblTerritory.TerritoryTypeID) In (1,4)) AND
((tblTerritory.[Active?])=-1) AND ((tblCongregation.CongregationName) Like
[Enter the congregation name:] & "*"))
ORDER BY tblTerritory.TerritoryName, tblTerritory.TerritoryNumber;


....Dana :)
 
G

golfinray

My personal experience (may not be the best way) I find the max separately
and then combine the two queries. Works like a charm.
 
D

Dana F. Brewer

Well I tried selecting max in a separate query and I am returning the oldest
date in the database not the newest. I need the most current date. I'm not
even sure if max is the way to go. My date columns are formatted as dates and
are not just typed in to look like dates. Help!!!!!

SELECT tblTerritoryCheckout.CheckoutID, tblTerritoryCheckout.TerritoryID,
Max(tblTerritoryCheckout.DateCheckedOut) AS MaxOfDateCheckedOut,
tblTerritoryCheckout.DateCheckedIn, tblTerritoryCheckout.PublisherID
FROM tblTerritoryCheckout
GROUP BY tblTerritoryCheckout.CheckoutID, tblTerritoryCheckout.TerritoryID,
tblTerritoryCheckout.DateCheckedIn, tblTerritoryCheckout.PublisherID;

....returns the older date (can't say oldest yet because so far I only have
two dates to compare against)

golfinray said:
My personal experience (may not be the best way) I find the max separately
and then combine the two queries. Works like a charm.
--
Milton Purdy
ACCESS
State of Arkansas


Dana F. Brewer said:
I want to be able to list the most current date an item is checked out. As I
was working through the query I used max as an aggregate function but I still
can't get the simple result I want. I'd rather list the fields I have chosen
and somehow select just the most current checkout date. Any help would be
greatly appreciated. Here is my query:

SELECT tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS
CheckedOut, tblTerritoryCheckout.DateCheckedIn AS CheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber
FROM (((tblTerritory LEFT JOIN tblTerritoryCheckout ON
tblTerritory.TerritoryID=tblTerritoryCheckout.TerritoryID) LEFT JOIN
tblPublisher ON tblTerritoryCheckout.PublisherID=tblPublisher.PublisherID)
INNER JOIN tblTerritoryType ON
tblTerritory.TerritoryTypeID=tblTerritoryType.TerritoryTypeID) INNER JOIN
tblCongregation ON tblTerritory.CongregationID=tblCongregation.CongregationID
GROUP BY tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription], tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, tblTerritoryCheckout.DateCheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber, tblCongregation.CongregationName
HAVING (((tblTerritory.TerritoryTypeID) In (1,4)) AND
((tblTerritory.[Active?])=-1) AND ((tblCongregation.CongregationName) Like
[Enter the congregation name:] & "*"))
ORDER BY tblTerritory.TerritoryName, tblTerritory.TerritoryNumber;


...Dana :)
Click to expand...
Click to expand...
 
K

KARL DEWEY

My date columns are formatted as dates and are not just typed in to look
like dates.
Is it a Text field Formated as a Date or is it a DateTime datatype field?

--
Build a little, test a little.


Dana F. Brewer said:
Well I tried selecting max in a separate query and I am returning the oldest
date in the database not the newest. I need the most current date. I'm not
even sure if max is the way to go. My date columns are formatted as dates and
are not just typed in to look like dates. Help!!!!!

SELECT tblTerritoryCheckout.CheckoutID, tblTerritoryCheckout.TerritoryID,
Max(tblTerritoryCheckout.DateCheckedOut) AS MaxOfDateCheckedOut,
tblTerritoryCheckout.DateCheckedIn, tblTerritoryCheckout.PublisherID
FROM tblTerritoryCheckout
GROUP BY tblTerritoryCheckout.CheckoutID, tblTerritoryCheckout.TerritoryID,
tblTerritoryCheckout.DateCheckedIn, tblTerritoryCheckout.PublisherID;

...returns the older date (can't say oldest yet because so far I only have
two dates to compare against)

golfinray said:
My personal experience (may not be the best way) I find the max separately
and then combine the two queries. Works like a charm.
--
Milton Purdy
ACCESS
State of Arkansas


Dana F. Brewer said:
I want to be able to list the most current date an item is checked out. As I
was working through the query I used max as an aggregate function but I still
can't get the simple result I want. I'd rather list the fields I have chosen
and somehow select just the most current checkout date. Any help would be
greatly appreciated. Here is my query:

SELECT tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS
CheckedOut, tblTerritoryCheckout.DateCheckedIn AS CheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber
FROM (((tblTerritory LEFT JOIN tblTerritoryCheckout ON
tblTerritory.TerritoryID=tblTerritoryCheckout.TerritoryID) LEFT JOIN
tblPublisher ON tblTerritoryCheckout.PublisherID=tblPublisher.PublisherID)
INNER JOIN tblTerritoryType ON
tblTerritory.TerritoryTypeID=tblTerritoryType.TerritoryTypeID) INNER JOIN
tblCongregation ON tblTerritory.CongregationID=tblCongregation.CongregationID
GROUP BY tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription], tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, tblTerritoryCheckout.DateCheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber, tblCongregation.CongregationName
HAVING (((tblTerritory.TerritoryTypeID) In (1,4)) AND
((tblTerritory.[Active?])=-1) AND ((tblCongregation.CongregationName) Like
[Enter the congregation name:] & "*"))
ORDER BY tblTerritory.TerritoryName, tblTerritory.TerritoryNumber;


...Dana :)
Click to expand...
Click to expand...
Click to expand...
 
D

Dana F. Brewer

Thanks for fast response. It is a datetime data type field. I finally
figured out how to get the most recent date but I have no idea how to write a
subquery to then select this recent date in my main query. If i do select
tblterritorycheckout.territoryid, Last(tblterritorycheckout.datecheckedout)
FROM tblterritorycheckout GROUP BY tblterritorycheckout.territoryid I get the
correct date. Now, to get that syntax into the main query as a selection
criteria...?

KARL DEWEY said:
like dates.
Is it a Text field Formated as a Date or is it a DateTime datatype field?

--
Build a little, test a little.


Dana F. Brewer said:
Well I tried selecting max in a separate query and I am returning the oldest
date in the database not the newest. I need the most current date. I'm not
even sure if max is the way to go. My date columns are formatted as dates and
are not just typed in to look like dates. Help!!!!!

SELECT tblTerritoryCheckout.CheckoutID, tblTerritoryCheckout.TerritoryID,
Max(tblTerritoryCheckout.DateCheckedOut) AS MaxOfDateCheckedOut,
tblTerritoryCheckout.DateCheckedIn, tblTerritoryCheckout.PublisherID
FROM tblTerritoryCheckout
GROUP BY tblTerritoryCheckout.CheckoutID, tblTerritoryCheckout.TerritoryID,
tblTerritoryCheckout.DateCheckedIn, tblTerritoryCheckout.PublisherID;

...returns the older date (can't say oldest yet because so far I only have
two dates to compare against)

golfinray said:
My personal experience (may not be the best way) I find the max separately
and then combine the two queries. Works like a charm.
--
Milton Purdy
ACCESS
State of Arkansas


:

I want to be able to list the most current date an item is checked out. As I
was working through the query I used max as an aggregate function but I still
can't get the simple result I want. I'd rather list the fields I have chosen
and somehow select just the most current checkout date. Any help would be
greatly appreciated. Here is my query:

SELECT tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS
CheckedOut, tblTerritoryCheckout.DateCheckedIn AS CheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber
FROM (((tblTerritory LEFT JOIN tblTerritoryCheckout ON
tblTerritory.TerritoryID=tblTerritoryCheckout.TerritoryID) LEFT JOIN
tblPublisher ON tblTerritoryCheckout.PublisherID=tblPublisher.PublisherID)
INNER JOIN tblTerritoryType ON
tblTerritory.TerritoryTypeID=tblTerritoryType.TerritoryTypeID) INNER JOIN
tblCongregation ON tblTerritory.CongregationID=tblCongregation.CongregationID
GROUP BY tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription], tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, tblTerritoryCheckout.DateCheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber, tblCongregation.CongregationName
HAVING (((tblTerritory.TerritoryTypeID) In (1,4)) AND
((tblTerritory.[Active?])=-1) AND ((tblCongregation.CongregationName) Like
[Enter the congregation name:] & "*"))
ORDER BY tblTerritory.TerritoryName, tblTerritory.TerritoryNumber;


...Dana :)
Click to expand...
Click to expand...
Click to expand...
 
D

Dana F. Brewer

Thank you VERY much Ken. Your straightforward subquery syntax has helped me
a great deal. I will research more on how I can fix the 'restriction'
problem with the joins.

....Dana


KenSheridan via AccessMonster.com said:
Dana:

Firstly you are using a HAVING clause rather than a WHERE clause. This
results from creating the query in design view and putting the criteria in
'GROUP BY' columns rather than in separate instances of the same columns and
selecting 'WHERE' in the 'total' row. A HAVING clause operates on the
results after grouping and is used for things like 'Customers who've placed
orders totalling 10000 GBP or more'.

The subquery to restrict the results to the latest (MAX) date also goes in
the WHERE clause, but as a result of this you no longer need to group the
query, so it would be like this:

SELECT tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, tblTerritoryCheckout.DateCheckedOut,
tblTerritoryCheckout.DateCheckedIn AS CheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber
FROM (((tblTerritory LEFT JOIN tblTerritoryCheckout ON
tblTerritory.TerritoryID=tblTerritoryCheckout.TerritoryID) LEFT JOIN
tblPublisher ON tblTerritoryCheckout.PublisherID=tblPublisher.PublisherID)
INNER JOIN tblTerritoryType ON
tblTerritory.TerritoryTypeID=tblTerritoryType.TerritoryTypeID) INNER JOIN
tblCongregation ON tblTerritory.CongregationID=tblCongregation.CongregationID
WHERE (((tblTerritory.TerritoryTypeID) In (1,4)) AND
((tblTerritory.[Active?])=-1) AND ((tblCongregation.CongregationName) Like
[Enter the congregation name:] & "*"))
AND tblTerritoryCheckout.DateCheckedOut =
(SELECT MAX(DateCheckedOut)
FROM tblTerritoryCheckout AS TC2
WHERE TC2.TerritoryID = tblTerritoryCheckout.TerritoryID)
ORDER BY tblTerritory.TerritoryName, tblTerritory.TerritoryNumber;

Note how the second instance of the tblTerritoryCheckout table is given an
alias TC2 to distinguish it from the first instance of the table, allowing
the subquery to be correlated with the outer query on TerritoryID. I think
I'm right in correlating on this column, but its difficult to be absolutely
sure.

Another point arising from this is that you are restricting the results on a
column in a table (tblTerritoryCheckout) which is on the right side of a left
outer join. The whole point of an outer join of course is that it returns
rows from one side regardless of whether there is a match on the other, so
once you apply a criterion to the other side it undermines the basis of the
outer join and in effect turns it into an inner join.

Ken Sheridan
Stafford, England
I want to be able to list the most current date an item is checked out. As I
was working through the query I used max as an aggregate function but I still
can't get the simple result I want. I'd rather list the fields I have chosen
and somehow select just the most current checkout date. Any help would be
greatly appreciated. Here is my query:

SELECT tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS
CheckedOut, tblTerritoryCheckout.DateCheckedIn AS CheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber
FROM (((tblTerritory LEFT JOIN tblTerritoryCheckout ON
tblTerritory.TerritoryID=tblTerritoryCheckout.TerritoryID) LEFT JOIN
tblPublisher ON tblTerritoryCheckout.PublisherID=tblPublisher.PublisherID)
INNER JOIN tblTerritoryType ON
tblTerritory.TerritoryTypeID=tblTerritoryType.TerritoryTypeID) INNER JOIN
tblCongregation ON tblTerritory.CongregationID=tblCongregation.CongregationID
GROUP BY tblTerritory.TerritoryID, [TerritoryName] & " " &
[TerritoryDescription], tblTerritory.TerritoryTypeID,
tblTerritoryType.TerritoryType, tblTerritoryCheckout.DateCheckedIn,
tblTerritoryCheckout.PublisherID, tblPublisher.PublisherName,
tblTerritory.LastUpdate, tblTerritory.UserID, tblTerritory.Notes,
tblTerritory.[Active?], tblTerritory.TerritoryName,
tblTerritory.TerritoryNumber, tblCongregation.CongregationName
HAVING (((tblTerritory.TerritoryTypeID) In (1,4)) AND
((tblTerritory.[Active?])=-1) AND ((tblCongregation.CongregationName) Like
[Enter the congregation name:] & "*"))
ORDER BY tblTerritory.TerritoryName, tblTerritory.TerritoryNumber;

...Dana :)
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Top