Pulling the last date of multiple entries

[Amanda]

I have a table (tblFeedback) which holds all the feedback from showings that
have been done on houses I list. One house is listed mulitple times for each
different day and realtor that shows the property. This table has several
more columns, but the 3 I'm interested in are Street Number, Street Name, and
Sent (When that feedback was sent to the client).

For example:

Street Number| Street Name| Realtor |Contact Number| Message?| Feedback|
Sent| LastUpdated
124| Fake Street| Jane Doe|125-7854| 0| <Feedback>| 5/26/2009| 5/26/2009
487| Testing Lane| Joe Shmoe| 126-6584| 0| <Feedback>| 6/8/2009| 6/8/2009
487| Testing Lane| Jane Doe| 125-7854| 0|<Feedback>| 5/26/2009| 5/26/2009

For Fake Street, I would want the 5/26 date. For Testing Lane though, I
want the 6/8 date.

I want to make a query that pulls these 3 columns to tell me the last time I
sent an email to the client. I've tried this SQL:

SELECT tblFeedback.[Street Number], tblFeedback.[Street Name],
Max(tblFeedback.[Sent]) AS LastSent
FROM tblFeedback;

But I get the following error: You tried to execute a query that does not
include the specified expression 'Street Number' as part of an aggregate
function.

Clearly, I've done something wrong, can anyone point me in the right
direction?

 

[vanderghast]

You may try one of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm

Using the first method, with two queries, is probably the most intuitive.


Vanderghast, Access MVP

 

[Jeff Boyce]

Amanda

Your data structure is much like what you'd use ... if you were limited to a
spreadsheet!

Because the relationship appears to be one-to-many (one address can have
many ?[ShowingDates]?), if you want to get the best use of Access'
features/functions, you need another table that stores, at a minimum, only
two fields:

PropertyID (which address?)
ShowingDate

With this design, a simple query can find the most recent [ShowingDate] for
any particular [PropertyID].

Notice that I'm (also) not referring to the address. I suspect you are
describing properties, which have address information as an attribute.
There's no reason to store that (address info) over and over again. Just
once, in a [Property] table, with a [PropertyID] field. Then refer to it by
using the value of its [PropertyID].

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Amanda]

I appreciate the response, sir. I'm always looking to improve/re-discover my
Access skills, so I shall look into how I can keep the form format I have
now, while reducing the amount of redudency I have in my tables.

Amanda

Your data structure is much like what you'd use ... if you were limited to a
spreadsheet!

Because the relationship appears to be one-to-many (one address can have
many ?[ShowingDates]?), if you want to get the best use of Access'
features/functions, you need another table that stores, at a minimum, only
two fields:

PropertyID (which address?)
ShowingDate

With this design, a simple query can find the most recent [ShowingDate] for
any particular [PropertyID].

Notice that I'm (also) not referring to the address. I suspect you are
describing properties, which have address information as an attribute.
There's no reason to store that (address info) over and over again. Just
once, in a [Property] table, with a [PropertyID] field. Then refer to it by
using the value of its [PropertyID].

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table (tblFeedback) which holds all the feedback from showings
that
have been done on houses I list. One house is listed mulitple times for
each
different day and realtor that shows the property. This table has several
more columns, but the 3 I'm interested in are Street Number, Street Name,
and
Sent (When that feedback was sent to the client).

For example:

Street Number| Street Name| Realtor |Contact Number| Message?| Feedback|
Sent| LastUpdated
124| Fake Street| Jane Doe|125-7854| 0| <Feedback>| 5/26/2009| 5/26/2009
487| Testing Lane| Joe Shmoe| 126-6584| 0| <Feedback>| 6/8/2009| 6/8/2009
487| Testing Lane| Jane Doe| 125-7854| 0|<Feedback>| 5/26/2009| 5/26/2009

For Fake Street, I would want the 5/26 date. For Testing Lane though, I
want the 6/8 date.

I want to make a query that pulls these 3 columns to tell me the last time
I
sent an email to the client. I've tried this SQL:

SELECT tblFeedback.[Street Number], tblFeedback.[Street Name],
Max(tblFeedback.[Sent]) AS LastSent
FROM tblFeedback;

But I get the following error: You tried to execute a query that does not
include the specified expression 'Street Number' as part of an aggregate
function.

Clearly, I've done something wrong, can anyone point me in the right
direction?

 

[Amanda]

Thank you so much sir! That did it for me! Thank you!

Amanda

You may try one of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm

Using the first method, with two queries, is probably the most intuitive.


Vanderghast, Access MVP

I have a table (tblFeedback) which holds all the feedback from showings
that
have been done on houses I list. One house is listed mulitple times for
each
different day and realtor that shows the property. This table has several
more columns, but the 3 I'm interested in are Street Number, Street Name,
and
Sent (When that feedback was sent to the client).

For example:

Street Number| Street Name| Realtor |Contact Number| Message?| Feedback|
Sent| LastUpdated
124| Fake Street| Jane Doe|125-7854| 0| <Feedback>| 5/26/2009| 5/26/2009
487| Testing Lane| Joe Shmoe| 126-6584| 0| <Feedback>| 6/8/2009| 6/8/2009
487| Testing Lane| Jane Doe| 125-7854| 0|<Feedback>| 5/26/2009| 5/26/2009

For Fake Street, I would want the 5/26 date. For Testing Lane though, I
want the 6/8 date.

I want to make a query that pulls these 3 columns to tell me the last time
I
sent an email to the client. I've tried this SQL:

SELECT tblFeedback.[Street Number], tblFeedback.[Street Name],
Max(tblFeedback.[Sent]) AS LastSent
FROM tblFeedback;

But I get the following error: You tried to execute a query that does not
include the specified expression 'Street Number' as part of an aggregate
function.

Clearly, I've done something wrong, can anyone point me in the right
direction?

 

[John Spencer MVP]

For that query it appears that all you need to do to fix the syntax error is
to add a GROUP BY clause and include the two fields that you are not applying
a domain function to.

SELECT tblFeedback.[Street Number], tblFeedback.[Street Name],
Max(tblFeedback.[Sent]) AS LastSent
FROM tblFeedback
GROUP BY tblFeedback.[Street Number], tblFeedback.[Street Name]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Amanda]

I thank you sir!

Amanda

For that query it appears that all you need to do to fix the syntax error is
to add a GROUP BY clause and include the two fields that you are not applying
a domain function to.

SELECT tblFeedback.[Street Number], tblFeedback.[Street Name],
Max(tblFeedback.[Sent]) AS LastSent
FROM tblFeedback
GROUP BY tblFeedback.[Street Number], tblFeedback.[Street Name]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table (tblFeedback) which holds all the feedback from showings that
have been done on houses I list. One house is listed mulitple times for each
different day and realtor that shows the property. This table has several
more columns, but the 3 I'm interested in are Street Number, Street Name, and
Sent (When that feedback was sent to the client).

For example:

Street Number| Street Name| Realtor |Contact Number| Message?| Feedback|
Sent| LastUpdated
124| Fake Street| Jane Doe|125-7854| 0| <Feedback>| 5/26/2009| 5/26/2009
487| Testing Lane| Joe Shmoe| 126-6584| 0| <Feedback>| 6/8/2009| 6/8/2009
487| Testing Lane| Jane Doe| 125-7854| 0|<Feedback>| 5/26/2009| 5/26/2009

For Fake Street, I would want the 5/26 date. For Testing Lane though, I
want the 6/8 date.

I want to make a query that pulls these 3 columns to tell me the last time I
sent an email to the client. I've tried this SQL:

SELECT tblFeedback.[Street Number], tblFeedback.[Street Name],
Max(tblFeedback.[Sent]) AS LastSent
FROM tblFeedback;

But I get the following error: You tried to execute a query that does not
include the specified expression 'Street Number' as part of an aggregate
function.

Clearly, I've done something wrong, can anyone point me in the right
direction?