calculations on query results

[nerd19]

im trying to calculate how long a tool was used in a certain machine. so what i have done is i have made a query that brings up the all the records for all the tools in a certain machine along with the dates that they were installed. so im trying to take the first date from the query results (i have the dates listed in ascending order so the newest is the first), and the second date and find what the difference of those two dates is and display it in a report or form.

thanks for the help

 

[nerd19]

Also the dates will depend on which tool you would like to have the usage for, so the query is dependent on the tool number. I have thought about doing this and i get an error about a WHERE clause.
=DateDiff("n",Max([TimeStamp])-1,Max([TimeStamp]))
I am using that code in a text box within a form. Is there anything wrong in doing it this way and i am not putting it in the correct spot?

 

[PHV]

What is the actual SQL code of your query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JeremyNYC]

Hmm. I thought I'd posted this, but this SQL should work, if you rename things:
SELECT tblToolUse.ToolID, Min(tblToolUse.DateUsed) AS MinOfDateUsed, Max(tblToolUse.DateUsed) AS MaxOfDateUsed
FROM tblToolUse
GROUP BY tblToolUse.ToolID;


Jeremy

---
Jeremy Wallace
ABCDataworks
You can find us on the web, and my e-mail is fairly easy to guess.

 

[nerd19]

heres the actual sql code from the query if it helps:

SELECT [BMs & Tools v2].BM, [BMs & Tools v2].Type, [BMs & Tools v2].Date, [BMs & Tools v2].Serial
FROM [BMs & Tools v2]
GROUP BY [BMs & Tools v2].BM, [BMs & Tools v2].Type, [BMs & Tools v2].Date, [BMs & Tools v2].Serial
ORDER BY [BMs & Tools v2].BM, [BMs & Tools v2].Type, [BMs & Tools v2].Date DESC;

but i will first try what jeremy suggested.
thanks

 

[nerd19]

the code that jeremy gave will only find the first date and the last date though correct? i need to find the first date (most current) and the second date (as in the second most current) not the intial date. i tried to do something like Max(bms & tools v2.date)-1, but that will only subtract one day from the most current listing instead of giving the second record

 

[PHV]

What are BM, Type and serial ?
In other words, what is a machine ? a tool ?
On which grouped fields should you find the latest and the second to latest Date ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

maybe...

SELECT
  BM
, Type
, DateDiff("n",Max([TimeStamp]),[b]Min[/b]([TimeStamp]))
, Serial

FROM [BMs & Tools v2]

Group By 
  BM
, Type
, Serial

Skip,
[sub]
[red][/red]
[/sub]

 

[JeremyNYC]

Ah. Sorry, I didn't get that when I first read your post. It sounds like you'll have to use a subselect (or a couple of queries) to get this.

To get the second date, you could do something like this (aircode):

SELECT 
   tblToolUse.ToolID, 
   MIN(tblToolUse.DateUsed) AS FirstDateUsed,
   MAX(tmpTbl1.DateUsed) AS SecondDateUsed
FROM 
  tblToolUse LEFT INNER JOIN
  (SELECT TOP 2 
      tblToolUse.ToolID, 
      tblToolUse.DateUsed
   FROM 
      tblToolUse
   GROUP BY 
      tblToolUse.ToolID
   ORDER BY 
      tblToolUse.DateUsed
   ) as tmpTbl1
GROUP BY 
   tblToolUse.ToolID
ORDER BY 
   tblToolUse.DateUsed

My guess is there's more than one error in that, but it gives you the basic idea. The subselect can be built as its own query, which is often an easier way to make sure you're getting the results you want.

Jeremy

---
Jeremy Wallace
ABCDataworks
You can find us on the web, and my e-mail is fairly easy to guess.

 

[dRahme]

Hi, you might be able to tweak this:

SELECT Distinct TOP 2 r1.*
FROM revisions AS r1 LEFT JOIN revisions AS r2 ON (r1.deliverable_no = r2.deliverable_no) AND (r1.issue_dt < r2.issue_dt)
WHERE r1.deliverable_no = "075B";

HTH

 

[nerd19]

thanks jeremy i got it to work off of a tweak of that. now i thought that since my query gives me a list of records that are sorted by the number of the machine, then by the type and finalling sorted in descending order by date, is it possible to do that for the first record of every type?

so here is an example of what the query gives:

machine type date serial
10 1 6-26-2007 a123
10 1 6-25-2007 a234
10 2 6-26-2007 b123
10 2 6-24-2007 b234
10 3 6-23-2007 c123
11 1 6-26-2007 d123

so then i would do a query for the machine number, and what i was wondering is could i go like FIRST([type_table].[type]="1") and so on for each type, and then do that date difference calculation? Hopefully that makes sense, thanks for the help