Combining queries

[Guest]

I have a 2 step query process. I select the data I'm interested in and put
it in temp DB, then I do a calculation on it. ( I want to get the difference
between the MAX and MIN of a record.) I'm sure I can combine the two steps.
How?

Thanks.

 

[Jason Lepack]

Given the almost zero information that you gave, I created this temp
table

tbl_values
v_id - Autonumber - PK
v_value - number

Then I made this query:
SELECT Max([v_value])-Min([v_value]) AS Total
FROM tbl_values;

I entered into the table:
v_id, v_value
1, 12
2, 212

And the query returned:
200

Cheers,
Jason Lepack

 

[Guest]

Excuse my ignorance, I'm a newbie. Here's the code:

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date INTO Tempusage
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineDesignator)=[Enter machine designator (ie CL01,
CM02, CLM3)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date DESC;


SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date INTO Tempusage
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineDesignator)=[Enter machine designator (ie CL01,
CM02, CLM3)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date DESC;

Given the almost zero information that you gave, I created this temp
table

tbl_values
v_id - Autonumber - PK
v_value - number

Then I made this query:
SELECT Max([v_value])-Min([v_value]) AS Total
FROM tbl_values;

I entered into the table:
v_id, v_value
1, 12
2, 212

And the query returned:
200

Cheers,
Jason Lepack

I have a 2 step query process. I select the data I'm interested in and put
it in temp DB, then I do a calculation on it. ( I want to get the difference
between the MAX and MIN of a record.) I'm sure I can combine the two steps.
How?

Thanks.

 

[Guest]

Brother, rough day. Here we go again:

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date INTO Tempusage
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineType)=[Enter machine type (ie Lathe, Mill, or Mill
Turn)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date DESC;

SELECT Tempusage.MachineDesignator, Tempusage.MachineName,
Tempusage.MachineType, Max([MachineUsage])-Min([MachineUsage]) AS [Usage]
FROM Tempusage
GROUP BY Tempusage.MachineDesignator, Tempusage.MachineName,
Tempusage.MachineType;

Given the almost zero information that you gave, I created this temp
table

tbl_values
v_id - Autonumber - PK
v_value - number

Then I made this query:
SELECT Max([v_value])-Min([v_value]) AS Total
FROM tbl_values;

I entered into the table:
v_id, v_value
1, 12
2, 212

And the query returned:
200

Cheers,
Jason Lepack

I have a 2 step query process. I select the data I'm interested in and put
it in temp DB, then I do a calculation on it. ( I want to get the difference
between the MAX and MIN of a record.) I'm sure I can combine the two steps.
How?

Thanks.

 

[Jason Lepack]

Phil,

I'm going to look at this when I get home tonight after 5:30 PM EST.

Cheers,
Jason Lepack

Brother, rough day. Here we go again:

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date INTO Tempusage
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineType)=[Enter machine type (ie Lathe, Mill, or Mill
Turn)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date DESC;

SELECT Tempusage.MachineDesignator, Tempusage.MachineName,
Tempusage.MachineType, Max([MachineUsage])-Min([MachineUsage]) AS [Usage]
FROM Tempusage
GROUP BY Tempusage.MachineDesignator, Tempusage.MachineName,
Tempusage.MachineType;

Given the almost zero information that you gave, I created this temp
table

tbl_values
v_id - Autonumber - PK
v_value - number

Then I made this query:
SELECT Max([v_value])-Min([v_value]) AS Total
FROM tbl_values;

I entered into the table:
v_id, v_value
1, 12
2, 212

And the query returned:
200

Cheers,
Jason Lepack

 

[John Spencer]

I may be mistaken, but wouldn't this query work for you.

SELECT MachineDesignator
, MachineName
, MachineType
, Max(MachineUsage) - Min(MachineUsage) as MachineUse
FROM Machines INNER JOIN Usage
ON Machines.MachineDesignator = Usage.MachineDesignator
WHERE Machines.MachineType=
[Enter machine type (ie Lathe, Mill, or Mill Turn)]
AND Usage.Date Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]
GROUP BY MachineDesignator, MachineName, MachineType
ORDER BY Machines.MachineDesignator


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Brother, rough day. Here we go again:

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.MachineUsage, Usage.Date INTO Tempusage
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineType)=[Enter machine type (ie Lathe, Mill, or
Mill
Turn)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)] And
[Last date of interest (MM/DD/YY)]))
ORDER BY Machines.MachineDesignator, Usage.Date DESC;

SELECT Tempusage.MachineDesignator, Tempusage.MachineName,
Tempusage.MachineType, Max([MachineUsage])-Min([MachineUsage]) AS [Usage]
FROM Tempusage
GROUP BY Tempusage.MachineDesignator, Tempusage.MachineName,
Tempusage.MachineType;

Given the almost zero information that you gave, I created this temp
table

tbl_values
v_id - Autonumber - PK
v_value - number

Then I made this query:
SELECT Max([v_value])-Min([v_value]) AS Total
FROM tbl_values;

I entered into the table:
v_id, v_value
1, 12
2, 212

And the query returned:
200

Cheers,
Jason Lepack

I have a 2 step query process. I select the data I'm interested in and
put
it in temp DB, then I do a calculation on it. ( I want to get the
difference
between the MAX and MIN of a record.) I'm sure I can combine the two
steps.
How?

Thanks.