SQL Statement - Check the earliest date as per group of records - If true , return custom text in a custom column

[Luthius]

Guys I have the following problem.
I need to identify if the date of the record is the earliest date of the group.
The idea is NOT return an aggregation table, the idea is return all the records but in a custom column I want to identify if the date of the record is the earliest of the group. If true, then the record of the column will receive a text like "Most Updated record", otherwise, "Discontinued".

Below a simplification of the table where GroupID means foreign key

MyTable

• Id
• GroupID
• RecordDate
• Observations

 

[Joe4]

You can either do this with a set of two queries, or with a subquery within a query.
I will describe how to do two query one, as you can use the Query Builder for that and do not need to write your own SQL code.

First, create an aggregate query that has two fields: GroupID and RecordDate.
Group by the GroupID field, and choose the "Min" option for the RecordDate, to return the earliest date for each query.

Now, create a new query, doing a left-join from your original table to your aggregate query, joining on the GroupID field.
You can return whatever fields from your original table that you like, and then add a calculated field in this format:
IIF(Table.RecordDate = Query.MinRecordDate,"Most Updated","Discontinued")

Obviously, you will need to change Table.RecordDate with the reference to your RecordDate field from the table, and Query.MinRecordDate with the reference of your minimum record date from the aggregate query. If you are sure what they are supposed to look like, just select the appropriate fields from the Expression Builder, and Access will fill in the details.

 

[Luthius]

Thank you for taking your time to help me.

I understood but is there a way to that with a single SQL statement instead to store an aggregate query into my database? As you mentioned, something using subqueries?

Thank you

 

[Luthius]

I'm trying to merge both statements but it is NOT working. It is driving me crazy... ?

My Idea is a Sub-query....

SELECT MyTable.GroupId,
       MyTable.RecordDate,
       MyTable.Remarks,
       Switch([RecordDate]=[MinOfRecordDate], "Most Updated", [RecordDate]<>[MinOfRecordDate], "Discontinued") AS CustomColumn
FROM MyTable
LEFT JOIN
  (SELECT MyTable.GroupId,
          Min(MyTable.RecordDate) AS MinOfRecordDate
   FROM MyTable
   GROUP BY MyTable.GroupId) MyTable ON MyQuery.GroupId = MyTable.GroupId;

 

[jackd]

Here is a mock up that may be helpful.

Table TLuth

TLuth TLuth

id	groupdid	RecordDate	observations
1​	1​	02-Mar-21​	xxx
2​	1​	13-Feb-98​	rdds
3​	2​	27-Oct-17​	abc
4​	3​	27-Oct-17​	4rtgtg
5​	2​	19-Mar-06​	qwe
6​	1​	12-Aug-19​	tgt
7​	2​	28-Dec-19​	ref
8​	1​	31-Oct-98​	makd
9​	3​	07-Mar-21​	wefrf

SELECT TLuth.groupdid
, Min(TLuth.RecordDate) AS MinOfRecordDate
FROM TLuth
GROUP BY TLuth.groupdid;

Result:

Query2 Query2

groupdid	MinOfRecordDate
1​	13-Feb-98​
2​	19-Mar-06​
3​	27-Oct-17​

 

[Luthius]

Thank you but my request is ONE singe query.

"is there a way to do that with a single SQL statement instead to store an aggregate query into my database? As you mentioned, something using subqueries?"

My Idea is a Sub-query....

SELECT MyTable.GroupId,
       MyTable.RecordDate,
       MyTable.Remarks,
       Switch([RecordDate]=[MinOfRecordDate], "Most Updated", [RecordDate]<>[MinOfRecordDate], "Discontinued") AS CustomColumn
FROM MyTable
LEFT JOIN
  (SELECT MyTable.GroupId,
          Min(MyTable.RecordDate) AS MinOfRecordDate
   FROM MyTable
   GROUP BY MyTable.GroupId) MyTable ON MyQuery.GroupId = MyTable.GroupId;

 

[Joe4]

Here is how you can do it all in one table:

SELECT MyTable.GroupID, MyTable.RecordDate, MyTable.Remarks, IIf([MyTable].[RecordDate]=[x].[MinOfRecordDate],"Most Updated","Discontinued") AS CustomColumn
FROM MyTable LEFT JOIN
(SELECT MyTable.GroupID, Min(MyTable.RecordDate) AS MinOfRecordDate
FROM MyTable
GROUP BY MyTable.GroupID) AS x
ON MyTable.GroupID = x.GroupID;

 

[Luthius]

SELECT
MyDataSource.*,
IIf([MyDataSource].[Planned Date] = [x].[MinOfRecordDate], "To schedule", "Highlight to Cancel") AS CustomColumn
FROM MyDataSource
LEFT JOIN (SELECT
MyDataSource.[Maintenance item],
MIN(MyDataSource.[Planned Date]) AS MinOfRecordDate
FROM MyDataSource
GROUP BY MyDataSource.[Maintenance item]) AS x
 ON MyDataSource.[Maintenance item] = x.[Maintenance item];

 

[Joe4]

OK, so did my query work?
It looks like you didn't originally give us the real table and field names, and just substituted them into the SQL code I gave you.
Is that correct?

 

[Luthius]

Your solution is perfect. Exactly what I was looking for. I just paste the final result of my query based on your solution.

Mr jackd thank you as well for your support and precious time.

This is the best forum!!!!