Returning only MAX(date) not all dates

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  :-)
0
Utf
2/1/2010 9:48:02 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
1176 Views

Similar Articles

[PageSpeed] 34

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" wrote:

> 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  :-)
0
Utf
2/1/2010 10:00:02 PM
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" wrote:

> 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" wrote:
> 
> > 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  :-)
0
Utf
2/1/2010 11:09:01 PM
>>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" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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  :-)
0
Utf
2/1/2010 11:46:01 PM
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" wrote:

> >>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" wrote:
> 
> > 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" wrote:
> > 
> > > 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" wrote:
> > > 
> > > > 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  :-)
0
Utf
2/1/2010 11:56:01 PM
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

Dana F. Brewer wrote:
>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  :-)

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
KenSheridan
2/2/2010 12:41:44 AM
Dana:

The LAST operator does not do what you might think.  Its not the 'last' in
date order.  It might appear to give you the correct dates, but that is
purely fortuitous.  You must use the MAX operator to return the latest date.
Hopefully the solution posted in my reply to your original post will do the
trick.

Ken Sheridan
Stafford, England

Dana F. Brewer wrote:
>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...? 
>
>> >>My date columns are formatted as dates and are not just typed in to look 
>> like dates.
>[quoted text clipped - 51 lines]
>> > > > 
>> > > > ...Dana  :-)

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
KenSheridan
2/2/2010 12:48:35 AM
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" wrote:

> 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
> 
> Dana F. Brewer wrote:
> >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  :-)
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
> 
> .
> 
0
Utf
2/2/2010 1:43:01 AM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

sum of dates
I have a list of sales with dates, like 23-Mar-00. I would like to add up how many sales were made in the year 2001. I know this is probably very easy but I am new at this. Thanks for your help. Brent ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ =SUMPRODUCT((YEAR(date_range)=2003)*1,sales_range) Jerry debtless wrote: > I have a list of sales with dates, like 23-Mar-00. I would like to add > up how many sales were made in the year 2001. I know this is proba...

If then between two dates
I am trying to distribute revenue evenly between two dates. I have in one cell the beginning of the project. I have in another cell the end of the project. One my spreadsheet, I am referencing the month to determine if it falls betwen the project dates. I thought it was an if(and statement but it doesn't return the answer. Any suggestions would be greatly appreciated. Try this... A1 = project start date B1 = project end date D1 = some date To test whether the date in D1 falls within the start and end dates (inclusive) for the project: =IF(AND(A1<=D1,B1>=D...

Inconsistent date display
I have partial responsibility for a website. On a particular page dates must be displayed. The data is drawn from a backend access database using vbscript. <%=(Recordset1.Fields.Item("News_Date").Value)%> On some computers the date displays in US format, mm/dd/yy and on others I see dd/mm/yy. In all cases, Regional Settings are set to English(Australia) and Location=Australia. Short Date shows as 29-Dec-09. In unrelated access databases, dates show correctly if the texbox format is blank or set to ShortDate. Any ideas on - why the display is inconsistent o...

Inserting Date/Time of Last Update
Is there a way to insert a date and time in a cell(s) that will only be updated if there is a change to the worksheet. I'm looking to have the date/time of the last update so that it is easy to tell how current the worksheet is. Thanks -- Lionel B. Dyck <>< AIM ID: lbdyck Yahoo IM: lbdyck Homepage http://www.lbdsoftware.com/ Blog: http://randommgmt.blogspot.com/ Lionel You could put some code in a worksheet_Change() event like so (uses A1) Private Sub Worksheet_Change(ByVal Target As Range) Range("A1").Value = Now() End Sub To implement right click the sheet t...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Pivot table calculated field: multiply Sum of FieldA with Max of FieldB
Hello usenetters, My question concerns Excel 2003. A user asked me about a calculated field in a pivot table. Some of the fields are DT_NOW, GW_WCR, GW_LOB. The formula of the calculated field should depend on the date (DT_NOW): Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91 Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28 She added a field to the underlying data called ROT_FACTOR, filled with 91 until 30th june and filled with 28 starting 1st july. The current formula is =IF(ROT_FACTOR>300000;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) The cutoff value 300000 is an arbitrary value base...

Help with Dates
I need help with a date function. I want to be able to put a date in one cell and have another cell tell me what number week it falls in. For example if I were to input 2/12 in cell A1 then the cell next to it would tell me that 2/12 is the 6th week of the year. I've looked through the date functions and can find similar functions but nothing that will return the number of the week. As each date is actually stored as a sequential number based off of 1/1/1900 I am sure there is a way to figure out the number of the week. If anyone out there can help I would appreciated it. Thanks Stev...

Conditional formating dates
I have a range of dates in a column and I would like it if I could highlight any date that is more that 3 years old. So if the date reads 02/10/2004 this should be highlighted red. I would really appriciate a bit of help. CF formula is: =DATEDIF(A2,TODAY(),"y")>=3 -- Best Regards, Luke M "Teeny" <Teeny@discussions.microsoft.com> wrote in message news:CA22E7DF-C343-4059-88B9-1A2C538867B1@microsoft.com... >I have a range of dates in a column and I would like it if I could >highlight > any date that is more that 3 years old. > ...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

Convert date to number
I am wanting to convert date using the today() function to the date only for a sum. e.g. A1 =Today() B1= 300 C1= B1/A1 The cell that divides would be where the date conversion is made. Hope that makes sense Any help is appreciated Andrew Hi Andrew, The date is a number already. Just format A1 as General and you'll see the date serial number. What do you want to achieve? If you need the date number without the year: =A1-DATE(YEAR(A1),1,1)+1, Format as General or number (Excel will format it automatically as date, which is not what you need) If you just want to show the date withou...

Find Cell with Today's Date
Many of the cells in a worksheet have dates, only one of which is Today's date. I would like to find which cell has today's date, and then set that cell as the active cell. Try this tester for column A Sub Find_Todays_Date() Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Sheet1").Range("A:A") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByR...

calculating with dates
want to calculate the difference between two dates and necesarry to see the result separated by years / months / days but dont know how?? for example: 20/05/2005 - 10/03/1990 = 15 years/ 2months/ 11days thanks for your attention!! PS using excel 2000 See: http://www.cpearson.com/excel/datedif.htm Biff "DzEK" <aaaa@aaaa.hr> wrote in message news:d9v240$75p$1@ss405.t-com.hr... > want to calculate the difference between two dates > and necesarry to see the result separated by years / months / days > but dont know how?? > > for example: 20/05/2005 - 10/03/1...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Excel 2000 date does not display date correctly
I want to enter birthdays into a cell. I use the "format cell" for style 5/10/89 (for example). When I actually enter the birth date in this fashion 52952 and press the arrow/enter key I receive the following date: 12/21/44. I thought when I formatted the cell with the date option, I could enter the birth date in this fashion 52952 and the slashes (/) would be entered automatically. How can I resolve this odd date entry? No, sorry, you can't do that. Some kind of separators between days, months, years is required. Excel has no way of interpreting an entry like 1111...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...

Due Date grace period
It will be great to have the option to change the Due Date grace period in RM20101. The DUEGRPER is in RM10301 but not in RM20101. Also, it will be beneficial to be able to exclude some documents from being assessed finance charges. Aside from entering a range of Customer in the Assess Finance Charge window, having a range for documents will be a great option... maybe something like in the Write Off documents window where the documents can be previewed and unchecked for some docs. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with t...

Returning Multiple Values in a Single Cell
Hello, I posted this same question a couple of weeks ago but forgot to mention a key point. My question was as follows: I have read a few posts regarding this subject but am still somewhat lost. I have a two sheet workbook (Excel 2000). On the first sheet is a list of projects that my department will complete during the year. A description of each project is given, a start date, an end date, as well as which crew will be completing the work (Crew 1 or Crew 2). What I am trying to do on the second sheet is to be able to type in any date, and have the description of the project on that da...

date problem
I want to be able to type 110503 and have it show up as 11/05/03 so I selected that date format. Problem is when I type 110503 I get 7/19/06. Is there any way to do this? Excel counts the number of days from its base date - in your case I assume that you are using the 1904 date system - and formats it to look like a date. Try entering today's date and then re-formatting it as General - you should see 36756. Excel therefore takes your entry of 110503 as day 110503 from the start date of 1/1/1904 which is 19 July 2206 in your selected format it shows up as 1/19/06 HTH Sandy -- to ...

Returns immediate function
Hi all, I know that the function CeRapiInitEx returns immediately. No wait for processing. I want to implement such a function. Returns immediate. no wait. How do I implement such a function? Thanks Ko You might create a UI thread that do the work, launch it in the "non-wait" function and return immediatly to the caller. When the thread done it's works, you can post an application message to return the results.... Greetings, Gaetano Sferra "PPC DEV" <ppc_dev@yahoo.com> ha scritto nel messaggio news:033101c3b388$a7098910$a301280a@phx.gbl... > Hi all...

Lookup Values, return multiple.
I need help please. The formula I require has to in Worksheet A :- [B1] lookup DATA in Worksheet A [A1] SP001 in worksheet B [A] [B] [C] SP001 PL001 16 SP001 PL002 5 SP001 DR001 10 CR001 PL001 2 Search for all Text String starting with "PL" in Worksheet B [B] only for SP001 in [A] and return with values from Worksheet B [C] 16 and 5 I need the values to be seperated and not summed. Tx. Appreciate assistance. Try this... In the formulas: Rng1 refers to worksheet B $A$2:$A$5 Rng2 refers ...

Date is being changed to a number (problem)
I have 4 columns of data which I want to add together to make one column of data. 3 of the columns are text and one is a date. The formula I have used is.. ="text here "&C21&" more words here "&D21&","&" client ID "&E21&" went to "&F21 so it reads text as a sentence. The problem is that "&F21 is a date, and it displays it as a number, not a date. The formatting doesnt get carried across. The formatting of both columns are formated as dates. Can anyone help to make it display as a real date ...