Query Help with date and comments (1 Viewer)

[yvesdouville]

Hello,

I have this table that has 3 fields: Partners name, Comment Date, and Comment

I want to query all the partners with the latest comment.

Only 1 per partners

Been trying for a while without result. Anyone can help?

I can upload the db if you need

Thank you

Yves

 

[ebs17]

SELECT
   A.[Partners name],
   A.[Comment Date],
   A.Comment
FROM
   AnyTable AS A
      INNER JOIN
         (
            SELECT
               [Partners name],
               MAX([Comment Date]) AS MaxDate
            FROM
               AnyTable
            GROUP BY
               [Partners name]
         ) AS B
         ON A.[Partners name] = B.[Partners name]
            AND
         A.[Comment Date] = B.MaxDate

Eberhard

 

[yvesdouville]

OK, this is way out of my league.

Table is Commentaire
Query is Commentaire Requete

Can you help me install the sql in there.

Thank you in advance

 

[HiTechCoach]

How are you using the data?

If it will be on a report, you can easily do this with a subreport. The subreport's record source would get the Top 1 comment by date sorted descending by date.

 

[yvesdouville]

at the end, i need to be able to send the data into an excel sheet
3 columns, only one entry per partner. the latest information

How are you using the data?

If it will be on a report, you can easily do this with a subreport. The subreport's record source would get the Top 1 comment by date sorted descending by date.

 

[HiTechCoach]

If you are needing to export the data to excel, then I agree with Eberhard (ebs17). Use a subquery to filter the records work reallyt well.

This may help: Subquery basics

 

[yvesdouville]

Can you help me make it happen with the database that i sent?

I woud'nt know where to start doing this. I'm not advanced enough.

Sorry

If you are needing to export the data to excel, then I agree with Eberhard (ebs17). Use a subquery to filter the records work reallyt well.

This may help: Subquery basics

 

[ebs17]

open the query in the design view
switch to the sql view
replace the full instruction with the new one
adapt the labels for fields and tables to the ones you use
save
switch to the data view

Eberhard

 

[CJ_London]

if you only need the partner name and comment (no date) you can use

SELECT DISTINCT
   [Partners Name],
   (SELECT TOP 1 Comment FROM Commentaire C WHERE [Partners Name]=Commentaire.[Partners Name] ORDER BY [Comment Date] Desc) AS LastComment
FROM Commentaire

 

[yvesdouville]

Good morning,
I entered the SQL and chaged the labels
But when i run it this prompt come up

I tried several things but it's not working.

What am i doing wrong.

Here is the SQL i did:

SELECT
A.[Clients],
A.[Date du commentaire],
A.Commentaire
FROM
AnyTable AS A
INNER JOIN
(
SELECT
[Clients],
MAX([Date du commentaire]) AS MaxDate
FROM
AnyTable
GROUP BY
[Clients]
) AS B
ON A.[Clients] = B.[Clients]
AND
A.[Comment Date] = B.MaxDate

 

[yvesdouville]

if you only need the partner name and comment (no date) you can use

SELECT DISTINCT
   [Partners Name],
   (SELECT TOP 1 Comment FROM Commentaire C WHERE [Partners Name]=Commentaire.[Partners Name] ORDER BY [Comment Date] Desc) AS LastComment
FROM Commentaire

Thank you for your reply, very interestig, but i do need to have the date as i need to nnow when is the last time i called the customer

Trying to figure out what i'm doing wrong with the other SQL

 

[ebs17]

Your original table is named "AnyTable"?

In addition to copying, you could also try to understand something about the SQL statement.
Alternatively, you would have to be very humble with your wishes.

Eberhard

 

[CJ_London]

do you really have a table called 'anytable'?

 

[yvesdouville]

do you really have a table called 'anytable'?

No, i saw my error. I changed it, but still not working

Here is a snapshot of what i have so far. I do understand a little as i did some dB course a long time ago. But it's not my profession

 

[CJ_London]

what does 'not working' mean? This is Eberhard's solution, so I'll leave him to work with you on it

 

[yvesdouville]

what does 'not working' mean? This is Eberhard's solution, so I'll leave him to work with you on it

OK thank you

 

[yvesdouville]

I tried your version and i end up with this screen (A) when i run it. Same as the other SQL from Eberhard

B is the code
C is the table

Is it because i use a french version of Acces?

if you only need the partner name and comment (no date) you can use

SELECT DISTINCT
   [Partners Name],
   (SELECT TOP 1 Comment FROM Commentaire C WHERE [Partners Name]=Commentaire.[Partners Name] ORDER BY [Comment Date] Desc) AS LastComment
FROM Commentaire

 

[ebs17]

One should work precisely: Comment Date or Date du Commentaire => last line
The jet engine does not understand a mishmash.

As well as: Clients is defined as a memo (long text). Why? Memo creates a lot of problems, so when grouping and linking.
Fields that come into question for such uses should definitely be able to use text. I would be interested in the special reason why you really need memo.

Eberhard

 

[yvesdouville]

One should work precisely: Comment Date or Date du Commentaire => last line
The jet engine does not understand a mishmash.

As well as: Clients is defined as a memo (long text). Why? Memo creates a lot of problems, so when grouping and linking.
Fields that come into question for such uses should definitely be able to use text. I would be interested in the special reason why you really need memo.

Eberhard

I don't need Memo. I have Long text on my end. What should i use instead?

 

[ebs17]

long text = memo (until about 64.000 characters)
short text = text (max. 255 characters)

Eberhard