Difference between 2 dates in same column

[paddydive]

Hi All,

I am new to Access and need your help... Here is how my table looks like


ProjectID Phase start Date End Date
---------------------------------------------------
Project1 Phase2 07/08/16 07/15/16
Project1 Phase1 06/20/16 06/29/16
Project1 Phase3 07/20/16 07/29/16
Project1 Phase4 08/23/16 09/19/16


I need to calculate the date difference between two phases.

For Example difference between End date of Phase 1 and start date of phase 2 for Project 1

 

[xenou]

You can use DLookup functions or even a sql join. It would be more flexible if you used 1, 2, 3, 4 for phases (numbers) instead of text - although even here you can parse out the number from the words (get 1 from "Phase1", 2 from "Phase2", etc.).

 

[paddydive]

You can use DLookup functions or even a sql join. It would be more flexible if you used 1, 2, 3, 4 for phases (numbers) instead of text - although even here you can parse out the number from the words (get 1 from "Phase1", 2 from "Phase2", etc.).

Thanks xenou for speedy reply.. but Phase I mentioned here is just for example, in actual table it has phase name so I can't change it.

As I said I am totally new to Access and not aware of joins and SQL's ... any help from you regarding this is appreciated.

 

[xenou]

Honestly, this is not a good fit for access then, and it will be even worse if you don't know anything about SQL or joins.

For the record, here a way to write the query. It assumes you put a table in your database called Dual that has one record in it. The name of the field and the value of the record is irrelevant but customarily you call the field Dummy and give the record the value of 'X'.

select (select [End Date] from MyTable where [ProjectID] = 'Project1' and [Phase] = 'Phase2')-(select [Start Date] from MyTable where [ProjectID] = 'Project1' and [Phase] = 'Phase1') as MyDateDifference from Dual

You could use DLookups but that's more appropriate in a form or report. It would work much the same and also require you to hard code the values for the phases into the function making it very inflexible and not very useful under most circumstances.

 

[paddydive]

Thank you very much Xenou.. appreciate it

A little more help.. can you guide me with best sites where I can learn Access and queries.

Also, for above if there are more many Projects under ProejctID column (like Project1, Project2 ...........) will the above query work ? If you could help me with this as well please... sorry for the trouble and thank you very much for all your help in advance.

 

[xenou]

I found a way to work this out dynamically. However, the sql is advanced and I think you are not ready for this kind of project. I'd advise you to work with your data in Excel until you have more training in how to use relational databases and SQL.

*ALSO* I am making big assumptions about the integrity of your data and another really big assumption that each phase starts after the next phase (so if phase 1 ends on July 31 and phase 2 starts on the same day, the query will not work!!!!).

This query will show you the next phase:

select 
	ProjectID, 
	Phase, 
	[Start Date], 
	[End Date], 
	(
		select [Phase] From MyTable 
			where ProjectID = t1.ProjectID and [Start Date] = 
					(select min([Start Date]) from MyTable 
						where ProjectID = t1.ProjectID and [Start Date] > t1.[Start Date])
	) as NextPhase
from
	MyTable t1

This is the same query but we calculate the date differences instead of just showing the name of the next phase:

select 
	ProjectID, 
	Phase, 
	[Start Date], 
	[End Date], 
	(
		select [Start Date]-t1.[End Date] From MyTable 
			where ProjectID = t1.ProjectID and [Start Date] = 
					(select min([Start Date]) from MyTable 
						where ProjectID = t1.ProjectID and [Start Date] > t1.[Start Date])
	) as MyDateDiff
from
	MyTable t1

 

[paddydive]

Thank you very much for your help....

You are correct each phase starts after the next phase.... I have read about the SQL and understood that your above query have sub queries in it.... but not sure how they actually work

Appreciate our help.