Calculate against dates in rows in a query

[Costefran]

Can anyone please help

I am trying to calcuate between rows in a query and
1. not sure that this is possible
2. If it is possible I'm not sure how to do it

As sample of query results are shown below

Start Date End Date Clash Ref Clash or Not
Row 1 03/04/2010 24/12/2010 10 1
Row 2 09/04/2010 10/04/2010 11 1
Row 3 05/04/2010 06/04/2010 11 1

My calculation needs to go something like
If (Row 2 Clash Ref = Row 3 Clash Ref and Row 2 [Start Date] > Row 3 [Start Date] and Row 2 [Start Date]< Row 3 [End Date] then Count = 2,0)

I've been 'googling' but cannot seem to find anything so any help, as always would be appreciated

 

[Golom]

I followed you right up to

... then Count = 2,0)

Whet does that mean?

 

[Costefran]

Apologies as I din't explain very well

I want to have a result that produces 2 or 0 based on the formula so I could have a count field that equals 2 if the formula is satisifed then count = 2

For example



Start Date End Date Clash Ref Clash or Not Count
Row 1 03/04/2010 24/12/2010 10 1 0
Row 2 09/04/2010 10/04/2010 11 1 2
Row 3 05/04/2010 06/04/2010 11 1 2

So for Row 2 the formula would read

If (Row 2 Clash Ref = Row 3 Clash Ref and Row 2 [Start Date] > Row 3 [Start Date] and Row 2 [Start Date]< Row 3 [End Date] then Count = 2,0)

Hope this makes sense

 

[BitZero]

Here’s a divide and conquer approach, using a sequence of queries that each build on the previous one. Let’s call the original table tblClash, and I’m assuming the records can be uniquely identified by start date, end date and clash ref.

Create the following queries:

SELECT tblClash.*
FROM tblClash;

qryClash2 joins tblClash to qryClash , and returns pairs of start/end dates for records that clash:

SELECT tblClash.[Start Date], tblClash.[End Date], qryClash.[Start Date], qryClash.[End Date], tblClash.[Clash Ref]
FROM tblClash 
INNER JOIN qryClash ON (tblClash.[Clash Ref]=qryClash.[Clash Ref]) AND (tblClash.[Start Date]>qryClash.[Start Date]) AND (tblClash.[Start Date]<qryClash.[End Date])
ORDER BY tblClash.[Start Date];

qryClash3 breaks each record in qryClash2 into 2 records:

SELECT qryClash2.tblClash.[Start Date], qryClash2.tblClash.[End Date],  qryClash2.[Clash Ref]
FROM qryClash2
UNION ALL
SELECT qryClash2.qryClash.[Start Date], qryClash2.qryClash.[End Date], qryClash2.[Clash Ref]
FROM qryClash2;

qryClash4 does a group by, to count the number of clashes for a given start/end/ref:

SELECT qryClash3.[Start Date], qryClash3.[End Date], qryClash3.[Clash Ref], count(*) AS Cnt
FROM qryClash3
GROUP BY qryClash3.[Start Date], qryClash3.[End Date], qryClash3.[Clash Ref];

qryClash5 joins the original table with the counts in qryClash4, to give the desired result (I hope):

SELECT tblClash.[Start Date], tblClash.[End Date], tblClash.[Clash Ref], tblClash.[Clash or Not], IIf(IsNull(qryClash4.Cnt),0,Cnt*2) AS [Count]
FROM tblClash LEFT JOIN qryClash4 ON (tblClash.[Clash Ref] = qryClash4.[Clash Ref]) AND (tblClash.[End Date] = qryClash4.[End Date]) AND (tblClash.[Start Date] = qryClash4.[Start Date])
ORDER BY tblClash.[Clash Ref], tblClash.[Start Date];

I'm not sure what the field "Clash or Not" is used for.