Query Help with date and comments (1 Viewer)

[yvesdouville]

I rename everything in english and changed to short text.

I keep getting the :SELECT your Data Source popup.

I have attached my latest version with the Query under Ebhard Test

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

Eberhard

 

[Papa_Bear1]

Here's a possibility - maybe:

Query 1 (qryTest1) written as such:
SELECT T.[Partners Name] AS Partners, Max(T.[Comment Date]) AS Comment_Date
FROM [Any Table] AS T
GROUP BY T.[Partners Name];

With Query 2 written to use qryTest1 - to give your intended outcome:
SELECT Q.Partners, Q.Comment_Date, T.Comment
FROM qryTest1 AS Q INNER JOIN [Any Table] AS T ON (Q.[Comment_Date] = T.[Comment Date]) AND (Q.[Partners] = T.[Partners Name]);

Or - Combined:
SELECT Q.Partners, Q.Comment_Date, T.Comment
FROM
(
SELECT T.[Partners Name] AS Partners, Max(T.[Comment Date]) AS Comment_Date
FROM [Any Table] AS T
GROUP BY T.[Partners Name]
)
AS Q INNER JOIN [Any Table] AS T ON (Q.[Comment_Date] = T.[Comment Date]) AND (Q.[Partners] = T.[Partners Name])

 

[ebs17]

Last attempt - please do not install any new errors.

1) use a simple selection query, not a passthrough query
2) AnyTable (use in query) <> Any Table (name according to table definition)

If you avoided labels with special characters and spaces, you could give yourself all the square brackets and get a better overview.

Eberhard

 

[yvesdouville]

I came here hoping to fing help, not just make me feel like a no good person.

I provided the db, exactly for that pupose so i can learnn from more advanced people.

:-(

Last attempt - please do not install any new errors.

1) use a simple selection query, not a passthrough query
2) AnyTable (use in query) <> Any Table (name according to table definition)

If you avoided labels with special characters and spaces, you could give yourself all the square brackets and get a better overview.

Eberhard

 

[CJ_London]

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?

No - implication is your tables are not access tables but something else - which would appear not to be the case. But might have something to do with the name of your PK field. Fields should not use non alpha numeric characters and preferably no spaces - they can cause misleading error messages.

With regards the sql you posted, you need to use the names of your fields - so 'comment' needs to be changed to 'commentaire' for example

 

[CJ_London]

I've looked at your latest db. I don't know how you have done it but this is not a normal query - see the globe

Might have something to do with sharepoint, but it should look like the one above. The query properties have an incomplete ODBC connection string.

Take the code again and copy and paste into a new query - and correct for field and table names

 

[isladogs]

The query based on @ebs17 ideas needs modifying as your table is actually called 'Any Table' with a space.
Modified code

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

See Query1 attached

 

[yvesdouville]

Thank you very much for your time
I will check it out as soon as i have 5 minutes,

Will get back to you

Thank you again

The query based on @ebs17 ideas needs modifying as your table is actually called 'Any Table' with a space.
Modified code

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

See Query1 attached

 

[June7]

Why is Clients a memo (long text) field? Should be text type

TOP N can help to return all fields.

SELECT Commentaire.*
FROM Commentaire
WHERE [N°] IN
   (SELECT TOP 1 [N°]                           
   FROM Commentaire AS Dupe WHERE Dupe.[Clients] = Commentaire.[Clients]        
   ORDER BY Dupe.[Date du commentaire] DESC, Dupe.[N°] DESC) 
ORDER BY Clients, [Date du commentaire], [N°];

Some clients have multiple records with same date. Only one per client is retrieved.

That query involves Cartesian relationship of data and can perform slowly with large dataset.

The alternative already suggested would be:

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

This version will return multiple records with duplicate date for same client.