Query to subtract two fields from multiple records

[digitalshepard]

I have the following source data:

Item Revision Start End
332  B   9/2/11    9/15/11
332  C   9/21/11  9/22/11
332  E   11/2/12  11/29/12
 2A9  A   1/13/13  1/14/13
 2A9  C   1/16/13  1/18/13

I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):

Item Revision  Span
332  C      6
332  E      407
 2A9  C      2

I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?

 

[madefemere]

Hello,

1- A first query named Q_Start

SELECT (SELECT Count(Item)
FROM Table1 as T2
WHERE T2.Item=T1.Item and T2.Start<T1.Start) AS RankFinal, T1.Item, T1.start
FROM Table1 AS T1
WHERE ((((SELECT Count(Item)
FROM Table1 as T2
WHERE T2.Item=T1.Item and T2.Start<T1.Start))<>0))
ORDER BY T1.start;

will order records with the "start" field and for each Item, give a rank from 0 and exclude record having 0 as Rank. It has RankFinal, Item, Start as fields.

2- A second query named Q_Previous

SELECT (SELECT Count(Item)
FROM Table1 as T2
WHERE T2.Item=T1.Item and T2.End<T1.End)+1 AS RankPrev, T1.Item, T1.End
FROM Table1 AS T1
ORDER BY T1.End;

will order records with the "End" field for each Item, give a Rank from 1. It has RankPrev, Item, End as fields.

3- As result, a query that join theses previous queries with Rank and Item fields and the result field span = Start - End with the SQL code :

SELECT Q_Start.RankFinal, Q_Start.Item, Q_Start.start, Q_Previous.End, [start]-[End] AS Span
FROM Q_Start INNER JOIN Q_Previous ON (Q_Start.Item = Q_Previous.Item) AND (Q_Start.RankFinal = Q_Previous.RankPrev);

It is a beginning I think, without validation rules.

Have good continuation

 

[vbaInet]

1. Create a query based on your original table and include only the fields, Item, Revision and End.
2. Sort it by Item in ASC and Revision in DESC.
3. Save the query and close it. For the purpose of this exercise I'll call the query qrySource.
4. Use the following query:

SELECT A.[Item], A.[Revision],  
       (SELECT B.[End]
	FROM [COLOR="Red"]qrySource [/COLOR]AS B
	WHERE B.[Item] = A.[Item] AND
	      B.[Revision] < A.[Revision]
       ) AS PrevEnd,
       DateDiff("d", A.[Start], PrevEnd) AS Span
FROM [COLOR="Blue"]TableName [/COLOR]AS A

... TableName is the name of your original table as shown in your original post.

 

[digitalshepard]

Thanks vbaInet. That worked well, and simple.

 

[vbaInet]

Glad we all could help!