Subquery whit 2 values and a max fonction

[Blacksun]

Hello I have 2 query:
Maxdate:
SELECT [table Receive].[client code], Max([table Receive].[Date]) AS [MaxOfDate]
FROM

• INNER JOIN [table Receive] ON
  • .[Number] = [table Receive].[Client Code]
    GROUP BY [table Receive].[client code]
    
    and the second
    SELECT maxdate.[Client Code], maxdate.[MaxOfDate], [table Receive].[Req Date], [table Receive].[Forecast Actual], [table Receive].Response, [table Receive].CRS
    FROM maxdate INNER JOIN [table Receive] ON (maxdate.[MaxOfDate] = [table Receive].[Date]) AND (maxdate.[Client Code] = [table Receive].[Client Code])
    ORDER BY maxdate.[Client Code];
    
    I want to merge them in only one query so i obtain all the info on one row.
    
    I try subquery but because i need two data id (client code and max date who is a fonction) its doesnt work. I try this also but the row command doent work in access:
    
    SELECT [table Receive].[Client Code], [table Receive].[Date], [table Receive].[Req Date], [table Receive].[Forecast Actual], [table Receive].Response, [table Receive].CRS
    FROM [table Receive]
    WHERE row([table Receive].[Client Code], [table Receive].[Date]) in
    (SELECT [A].[Client Code], Max([A].TTL Date]) AS [MaxOfDate]
    FROM [table Receive] as A
    GROUP BY [A].[Client Code])
    ORDER BY [table Receive].[Client Code];

 

[plog]

Can you provide 2 sets of data to demonstrate what you want from your query? Give me 2 sets:

A. Starting sample data from table Receive. Include field names and enough data to cover all cases.

B. Expected results based on data provided in A. Show me the data you want returned from your query when you feed it the data from A.

 

[Blacksun]

This is an excel sheet whit the data

 

[plog]

This SQL will do it:

SELECT [Sub1].[Client Code], [Sub1].[Date], [Sub1].[Req Date], [Sub1].[Forecast Actual], [Sub1].Response
FROM [table Receive] AS Sub1
INNER JOIN (SELECT [Client Code], MAX([Date]) AS MaxDate FROM [table Receive] GROUP BY [Client Code]) AS Sub2 ON Sub1.[Client Code]=[Sub2].[Client Code] AND Sub1.[Date]=Sub2.[MaxDate]

It would be a lot easier if you choose better names.

1. You shouldn't use spaces or special characters in table/field names. So instead of [table Receive], you should have [tableReceived] (actual, its a table, no need to name it that).

2. You shouldn't used reserved words (https://support.microsoft.com/en-us/kb/286335). Date has special meaning and instead you should prefix your field name for the date it represents. Like how you have Req Date (of course without the space).

 

[Blacksun]

Thanks you its work

For the name i know but its was not me who design the DB and the name are the one from the excel sheet they use.
I have made some change but i cannot change everything for now

 

[plog]

Does your birth certificate say 'Blacksun' on it?

Probably not. You chose that because its easier to use online. Real life=one thing, Access forum=another. Same applies to Excel and Databases. You choose the most effective names for the system you are in. There's no reason you can't have proper names just because an Excel exists with different bad names.