Pulling the last date of multiple entries

  • Thread starter Thread starter Amanda
  • Start date Start date
A

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

Jeff Boyce said:
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 said:
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?
Click to expand...
Click to expand...
 
Thank you so much sir! That did it for me! Thank you! :D

Amanda

vanderghast said:
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


Amanda said:
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?
Click to expand...
Click to expand...
 
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 thank you sir!

Amanda

John Spencer MVP said:
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?
Click to expand...
Click to expand...
 
Back
Top