Subtracting dates and adding number of fields

[dingleberry]

Sample of data:
PartID BatchSer DateTime ISEPart
48 38487 1/8/07 7:57 AM 233960
48 38488 1/8/07 8:14 AM 233960
48 38489 1/8/07 8:16 AM 233960
48 38490 1/8/07 8:18 AM 233960
48 38491 1/8/07 8:20 AM 233960
51 24128 1/8/07 3:57 PM 826014
51 24129 1/8/07 4:03 PM 826014
51 24129 1/8/07 4:21 PM 826014

What I'm trying to do is 1:
Count the number of 48's and 51's and put them into a new column and 2:
When 48 changes to 51 subtract the 48 timestamp from the 51 timestamp to get the amount of time that expired when that value changed. If the day changed when 48 changed to 51 then we disregard that and don't subtract the timestamp. Any help?

 

[Remou]

Does 48 change to 51 more than once a day?

 

[dingleberry]

Occasionally but not likely and probably not necessary to count for. What is more likely to happen is 48 will change to 51 and then change to 53 or 45 etc later in the day. It's pretty arbitrary.

 

[Remou]

You have not mentioned what happens when the change is to something other than 51. Here is something you may wish to play around with.

SELECT tblT.PartID
  , Format([DateTime],"Short Date") AS DateGroup
  , Max(tblT.DateTime) AS Last_48
  , Count(tblT.PartID) AS CountOfPartID 
  , DMin("[DateTime]","tblT","PartID=51 
       And Format([DateTime],'Short Date')=#" 
       & Format([DateTime],"mm/dd/yyyy") & "#") AS First_51 
  , Format(CDate(Nz([First_51],[Last_48]))-
       CDate([Last_48]),"hh:nn:ss") AS Diff
FROM tblT
GROUP BY tblT.PartID 
  , Format([DateTime],"Short Date")
  , DMin("[DateTime]","tblT","PartID=51 
       And Format([DateTime],'Short Date')=#" 
       & Format([DateTime],"mm/dd/yyyy") & "#")
HAVING tblT.PartID=48

 

[dingleberry]

So close... can I have the query only return values where the Diff does not equal 00:00:00 and can I have the query not just focus on 48 and 51 but instead look at the day and return anywhere where the PartID changes from anything to something else?

 

[Remou]

Sure you can, play about a bit.