Add a new column to query to populate data from another column

[net]

Hello,

I have an Access query that I use to pull work order data. When a WO is entered for a job (WO Create Date), there is a calculated date generated in a field called “Est Completion Date”. This date calculates from the date entered 60 days out. For example: Date Entered is 3/1/18. Est Completion Date will be 4/30/18.

Problem is, if there is another WO entered for the same site on 3/5/18, the Est Completion Date will be 5/4/18, etc.

How can I add another column in my query called “Proj Completion Date” to populate the last Est Competition Date entered?

I have uploaded a small sample table.

I appreciate your time and support. Thank you.

 

[arnelgp]

Use sub query ti calc it:

Select field1, (select top 1 max([wo create date]) from table1 as t1 where t1.pk=table1.pk)+60 as [proj completion date] from table1

 

[net]

Thank you arnelgp for your suggestion.

I don't need to calculate the 60 days. Just create another field that populates the last WO create date.

 

[arnelgp]

SELECT yourTable.[WO Create Date], (select T1.[WO Create Date]+60 From yourTable As T1 Where T1.[WO Create Date]=yourTable.[WO Create Date]) AS [Est Completion Date], (select Max(T1.[WO Create Date])+60 From yourTable As T1) AS [Proj Completion Date]
FROM yourTable;