problem Calculating Overlap between projects

[greid]

I'm able to calculate how long a Work Order (WO) took, and I can pull the First-FirstDate and Last-LastDate to get the total length of the Project, but how do I show the 11 day overlap between the two WOs?

OK, not code, but I wanted the text to line up.

[FONT=Courier New]PROJECT    Work Order   FirstDate   LastDate     DurrDys 
1355       11-038660    8/12/2010   10/11/2010     60 
1355       11-043359    9/30/2010    3/14/2012    531
-------------------------------------------------------------------------------
1355                    8/12/2010    3/14/2012    580[/FONT]

 

[BBQ Kittens]

without seeing any of your table structure im going to guess, but one prob is that youll have to identify which records youll want to compare if you just want to view the total days overlapped. So if your interested in viewing which work orders overlap a particular work order, you can set up a query to calculate the difference between the work order of interest's 'LastDate' with that of the other work order's 'FirstDate'. This will give the overlap. I'd set the criteria of the overlap to be >0 to exclude negatives and zeros. In order to select the work order your interested in and see the results, Id set up a form with a combo box to select the work order. Once selected, this is retrieved by the query and the overlapping work orders and calc'd. Just requery the form results and view.

 

[greid]

Problem is that this is for a report that goes to VP who'll never see my little access db and just want a short and comprehensive why. I need to demonstrate that these WOs do overlap and should not have their durations added up, but should span from 1 start date for the Project and 1 end date for the Project. Suddenly, employees time will not look wasteful. Most of the time, it will be only 2 WO per project. On some occations, there will be more meaning that they middle are essentially swallowed up by the first and last.

Here is the sql

SELECT UFMAX_AE_P_PRO_E.PROJECT, UFMAX_AE_P_PRO_E.PROPOSAL, First(UFMAX_AE_P_STA_E.STATUS_DATE) AS FirstDate, Last(UFMAX_AE_P_STA_E.STATUS_DATE) AS LastDate, DateDiff("h",[FirstDate],[LastDate]) AS DurrHrs, DateDiff("d",[FirstDate],[LastDate]) AS DurrDys, DateDiff("w",[FirstDate],[LastDate]) AS DurrWks
FROM UFMAX_AE_P_PRO_E LEFT JOIN UFMAX_AE_P_STA_E ON UFMAX_AE_P_PRO_E.PROPOSAL = UFMAX_AE_P_STA_E.PROPOSAL
GROUP BY UFMAX_AE_P_PRO_E.PROJECT, UFMAX_AE_P_PRO_E.PROPOSAL
HAVING (((UFMAX_AE_P_PRO_E.PROJECT) In ("1355")))
ORDER BY UFMAX_AE_P_PRO_E.PROPOSAL;

 

[BBQ Kittens]

i would need to see your database in order to design a query to do what you want, but from your posted sql, i would suggest using 'Group By' Min and Max instead of First() and Last() because those functions retrieve the first and last record but not necessarily the first and late date. If you enter them out of order then you will have issues.

 

[greid]

So, how do I show you my database? Draw tables and relationships?

 

[BBQ Kittens]

you can attach the database by clicking 'Go Advanced' in stead of 'Post Quick Reply' a simply gutted version with only the tables is fine. So you could import the tables to a new database and attach that. Though if i remember correctly, you may need 5 or 10 posts in the forums before you can attach?? maybe not

 

[greid]

I could get in trouble for just the sql I sent. I'll diagram it out.