Query problem

[Axelbox]

Hi Guys,



I am not able to solve a very simple query problem. I attached photos about my question.
I marked the records with red on the photo, that I would like to see in this query.
In the line column there are 3 different data A07 , A08, S64. Every line data have more skill date ( ex A07 has 2022.07.04. 11:22:10 , 2022.07.04. 11:19:38 , 2022.02.09. 09:14:10 )

The marked records ( 3 records ) with red is the last date ( in skill date column )for every Line ( A07,A08,S64 ) that I would like to see ( ex. Line: A07 skill : C skill date : 2022.07.04. 11:22:10 )

How can I define the query that it can show only 3 records which marked with red?

Thank you very much your help in advance!

[Micron]

You will have to remove skill field from your totals query because including it as a group will create a group record for every skill. That gives the appearance that you are getting all the records. When you remove and save, create a new query and join skill from your totals query to the skill field in dbo_masterdata. You should get whatever the skill is for the max date.

[Axelbox]

You will have to remove skill field from your totals query because including it as a group will create a group record for every skill. That gives the appearance that you are getting all the records. When you remove and save, create a new query and join skill from your totals query to the skill field in dbo_masterdata. You should get whatever the skill is for the max date.

First of all, thanks your answer!

As you wrote I removed the skill field from totals query and saved, it is OK! I created a new query but how can I join the skill filed from total query to dbo.masterdata? In dbo.masterdata there is no skill field..

[Micron]

I meant to use skill_matrix_data; the field that you removed from the query, the 4th one from the left in your image. You might have to remove master data from the 1st query - depends on what the rest of the records are. At some point you might end up with "duplicate" records as before because you are including names as a group item. So the approach might turn out to be
- use matrix data without skill field in 1st (Totals) query. This is to get single records that satisfy the aggregate function you use (e.g. Max)
- in 2nd query, add 1st query and matrix table, join matrix skill to query skill. This should relate proper skill to the record provided by the Max of the 1st query.
- in 3rd query, add 2nd query and master data table. Join personal number to sap number; add name field to get name.

EDIT - or just do 1st two steps if that turns out to be OK, which means you'd leave the master table in the 2nd query