Adding Estimated Start Date with Days

[JStan89]

Hello All,

I'm working on a query ("Target Date of Completion") that takes the initial date started (from Step 1 of date started) and adds the EC (Estimated Completion) which is just shown in days. This will give an EST (Estimated Start time) for the next step in date format, which I would need the new column. Is this possible? Also, As you can see, the piece parts all have a different amount of steps, so this calculation would need to know when it's a different part. I'd really appreciate the help in advance, I'm still very new to Access.

 

[smig]

as this is a calculated data you don't need another column on your table.
Calculate it "On the fly" when you need it.

As Access save dates as numbers you can use:
EndDate = StartDate + NumberOfDays

You can also use DateAdd() function:
EndDate = DateAdd("d", StartDate, NumberOfDays)

 

[JHB]

Run "Query2" in the attached database, look if it gives you what you want.

 

[JStan89]

Run "Query2" in the attached database, look if it gives you what you want.

Awesome work! Thank you so much!!

 

[E.Rowlands]

Run "Query2" in the attached database, look if it gives you what you want.

That is very skillful.

Just for my own education - in the "Date Stopped" Column you will have created, do you mind breaking down this bit of code: +IIf(IsNull([AmountDays]),0,[AmountDays])

Thanks.

 

[JStan89]

Run "Query2" in the attached database, look if it gives you what you want.

One more thing, the stopped column shows when each step is complete, any way I could show the start time for each given step. Basically move all the stopped column down one. Is there an easy way to do that?

 

[JStan89]

That is very skillful.

Just for my own education - in the "Date Stopped" Column you will have created, do you mind breaking down this bit of code: +IIf(IsNull([AmountDays]),0,[AmountDays])

Thanks.

Not to take any of JHB's Credit, but I had to research that function when I first saw it. Here's what it does...

The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

iif ( condition, value_if_true, value_if_false )

 

[JHB]

Is there an easy way to do that?

Yes don't add the [EC], (put the below in a new query)!

SELECT [Piece Parts NEW].StepID, [Piece Parts NEW].[Piece ID], [Piece Parts NEW].StepNumber, [Piece Parts NEW].EC,
(SELECT TOP 1 Dupe1.[date started]
FROM [Piece Parts NEW] AS Dupe1
WHERE Dupe1.[Piece ID] = [Piece Parts NEW].[Piece ID]) AS TheStartDate,
(SELECT sum(Dupe.EC)
FROM [Piece Parts NEW] AS Dupe
WHERE Dupe.[Piece ID] = [Piece Parts NEW].[Piece ID]
AND Dupe.StepNumber < [Piece Parts NEW].StepNumber) AS AmountDays,
[TheStartDate]+[ec]+IIf(IsNull([AmountDays]),0,[AmountDays]) AS Stopped,
[TheStartDate]+IIf(IsNull([AmountDays]),0,[AmountDays]) AS Start
FROM [Piece Parts NEW];