One-to-Many Join Looking for Last Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This seems like something that should be simple to do. Trying to query 2
tables with a one-to-many join. The first table is projects and the second
table is comments for the projects. (See example below.) The join is between
Project.ID. I have tried using Total:Last and Sort:Ascending in the comment
date field to return the last comment made for a project but this does not
return the last comment. How should I be setting this query up?

Project Table
Project.ID (Text)
Project.Info (Text)
Project.Completion Date (Date/Time)

Comments Table
Project.ID (Text)
Date (Date/Time)
Comment (Text)

Thanks!
 
Create a query to return a project with the max date
SELECT Max(Comments.[Date]) AS MaxDate , Comments.[Project.ID]
FROM Comments
GROUP BY Comments.[Project.ID]
'
I would change the name of the two fields (date it a reserved name for
access and Project.ID without the ., it can cause problem.

And now add the query to the query you created when you join the query to
the Comments table, project to project, date to maxdate
That way you'll get the last comment
 
mp23bubba said:
This seems like something that should be simple to do. Trying to query 2
tables with a one-to-many join. The first table is projects and the second
table is comments for the projects. (See example below.) The join is between
Project.ID. I have tried using Total:Last and Sort:Ascending in the comment
date field to return the last comment made for a project but this does not
return the last comment. How should I be setting this query up?

Project Table
Project.ID (Text)
Project.Info (Text)
Project.Completion Date (Date/Time)

Comments Table
Project.ID (Text)
Date (Date/Time)
Comment (Text)

Thanks!
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put this in the SQL view of a query:

SELECT C.ID, C.[date], C.comment
FROM Comments As C
WHERE c.[date] = (SELECT MAX([Date]) FROM Comments WHERE ID = C.ID)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQ2NIechKqOuFEgEQIfmQCgvimQL5tRnlXk0INtO8A3sYPQ7ncAoM3m
s5gQKulsxJewB9Uz0v33lVTh
=BHgB
-----END PGP SIGNATURE-----
 
This only returned comments with the latest date in the comments table. I am
trying to get the query to return the last comment for each projectid. Did I
do something wrong?

MGFoster said:
mp23bubba said:
This seems like something that should be simple to do. Trying to query 2
tables with a one-to-many join. The first table is projects and the second
table is comments for the projects. (See example below.) The join is between
Project.ID. I have tried using Total:Last and Sort:Ascending in the comment
date field to return the last comment made for a project but this does not
return the last comment. How should I be setting this query up?

Project Table
Project.ID (Text)
Project.Info (Text)
Project.Completion Date (Date/Time)

Comments Table
Project.ID (Text)
Date (Date/Time)
Comment (Text)

Thanks!
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put this in the SQL view of a query:

SELECT C.ID, C.[date], C.comment
FROM Comments As C
WHERE c.[date] = (SELECT MAX([Date]) FROM Comments WHERE ID = C.ID)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQ2NIechKqOuFEgEQIfmQCgvimQL5tRnlXk0INtO8A3sYPQ7ncAoM3m
s5gQKulsxJewB9Uz0v33lVTh
=BHgB
-----END PGP SIGNATURE-----
Click to expand...
 
I understand you didnt try what I suggested

mp23bubba said:
This only returned comments with the latest date in the comments table. I am
trying to get the query to return the last comment for each projectid. Did I
do something wrong?

MGFoster said:
mp23bubba said:
This seems like something that should be simple to do. Trying to query 2
tables with a one-to-many join. The first table is projects and the second
table is comments for the projects. (See example below.) The join is between
Project.ID. I have tried using Total:Last and Sort:Ascending in the comment
date field to return the last comment made for a project but this does not
return the last comment. How should I be setting this query up?

Project Table
Project.ID (Text)
Project.Info (Text)
Project.Completion Date (Date/Time)

Comments Table
Project.ID (Text)
Date (Date/Time)
Comment (Text)

Thanks!
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put this in the SQL view of a query:

SELECT C.ID, C.[date], C.comment
FROM Comments As C
WHERE c.[date] = (SELECT MAX([Date]) FROM Comments WHERE ID = C.ID)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQ2NIechKqOuFEgEQIfmQCgvimQL5tRnlXk0INtO8A3sYPQ7ncAoM3m
s5gQKulsxJewB9Uz0v33lVTh
=BHgB
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
Ok, I figured out your suggestion Ofer and it looks to be working. But now I
have a new issue... there are mulitple comments that have been inputed on the
same date which is causing multiple lines. Is there a way to concatenate the
comments into one field?

Thanks for your help!

Ofer said:
I understand you didnt try what I suggested

mp23bubba said:
This only returned comments with the latest date in the comments table. I am
trying to get the query to return the last comment for each projectid. Did I
do something wrong?

MGFoster said:
mp23bubba wrote:
This seems like something that should be simple to do. Trying to query 2
tables with a one-to-many join. The first table is projects and the second
table is comments for the projects. (See example below.) The join is between
Project.ID. I have tried using Total:Last and Sort:Ascending in the comment
date field to return the last comment made for a project but this does not
return the last comment. How should I be setting this query up?

Project Table
Project.ID (Text)
Project.Info (Text)
Project.Completion Date (Date/Time)

Comments Table
Project.ID (Text)
Date (Date/Time)
Comment (Text)

Thanks!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put this in the SQL view of a query:

SELECT C.ID, C.[date], C.comment
FROM Comments As C
WHERE c.[date] = (SELECT MAX([Date]) FROM Comments WHERE ID = C.ID)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQ2NIechKqOuFEgEQIfmQCgvimQL5tRnlXk0INtO8A3sYPQ7ncAoM3m
s5gQKulsxJewB9Uz0v33lVTh
=BHgB
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 
You can select only one of the comments for that date by asking for first or
last.
To display all the comments for that date in one field, you cant achieve by
using a query, there is code involved in it, and a Temp table that will hold
the data.

mp23bubba said:
Ok, I figured out your suggestion Ofer and it looks to be working. But now I
have a new issue... there are mulitple comments that have been inputed on the
same date which is causing multiple lines. Is there a way to concatenate the
comments into one field?

Thanks for your help!

Ofer said:
I understand you didnt try what I suggested

mp23bubba said:
This only returned comments with the latest date in the comments table. I am
trying to get the query to return the last comment for each projectid. Did I
do something wrong?

:

mp23bubba wrote:
This seems like something that should be simple to do. Trying to query 2
tables with a one-to-many join. The first table is projects and the second
table is comments for the projects. (See example below.) The join is between
Project.ID. I have tried using Total:Last and Sort:Ascending in the comment
date field to return the last comment made for a project but this does not
return the last comment. How should I be setting this query up?

Project Table
Project.ID (Text)
Project.Info (Text)
Project.Completion Date (Date/Time)

Comments Table
Project.ID (Text)
Date (Date/Time)
Comment (Text)

Thanks!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put this in the SQL view of a query:

SELECT C.ID, C.[date], C.comment
FROM Comments As C
WHERE c.[date] = (SELECT MAX([Date]) FROM Comments WHERE ID = C.ID)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQ2NIechKqOuFEgEQIfmQCgvimQL5tRnlXk0INtO8A3sYPQ7ncAoM3m
s5gQKulsxJewB9Uz0v33lVTh
=BHgB
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query for All 4
Another most-recent date query 0
Last Record 4
No Update After Specific Date 2
One-to-Many join act as a One-to-One join 2
Group By Last 4
Join Question? 2
Multiple Records being returned in Query 2

Back
Top