SubQueries

I am running Access 2007 SP2 on Windows XP Professional SP 3. I have a query
that I need to run a subquery to obtain a value in the previous record.
Basically I have a query that contains mileages grouped by EquipmentID and
month. I need to calculate miles driven in a given month. I need the last
mileage reading in the previous months record to run the calculation on the
month that has focus. I know that I have to alias the query, but that is
where my memory ends. I have done this before with help from this site and
hope to be as fortunate again.
--
Bruce

Try this --
SELECT EquipmentID, Odometer, TripDate, (SELECT YourTable.Odometer -
[XX].Odometer FROM YourTable AS [XX] WHERE Max([XX].Odometer)
YourTable.Odometer ORDER BY [XX].Odometer DEC) AS TripMileage
FROM YourTable
ORDER BY YourTable.Odometer;

--
Build a little, test a little.


"Bruce" wrote:

I am running Access 2007 SP2 on Windows XP Professional SP 3. I have a query
that I need to run a subquery to obtain a value in the previous record.
Basically I have a query that contains mileages grouped by EquipmentID and
month. I need to calculate miles driven in a given month. I need the last
mileage reading in the previous months record to run the calculation on the
month that has focus. I know that I have to alias the query, but that is
where my memory ends. I have done this before with help from this site and
hope to be as fortunate again.
--
Bruce

I left out the EquipmentID from the subquery --
SELECT EquipmentID, Odometer, TripDate, (SELECT YourTable.Odometer -
[XX].Odometer FROM YourTable AS [XX] WHERE [XX].EquipmentID =
YourTable.EquipmentID AND Max([XX].Odometer) YourTable.Odometer ORDER BY
[XX].Odometer DEC) AS TripMileage
FROM YourTable
ORDER BY YourTable.Odometer;

--
Build a little, test a little.


"Bruce" wrote:

I am running Access 2007 SP2 on Windows XP Professional SP 3. I have a query
that I need to run a subquery to obtain a value in the previous record.
Basically I have a query that contains mileages grouped by EquipmentID and
month. I need to calculate miles driven in a given month. I need the last
mileage reading in the previous months record to run the calculation on the
month that has focus. I know that I have to alias the query, but that is
where my memory ends. I have done this before with help from this site and
hope to be as fortunate again.
--
Bruce

Karl,

Thank you for your reply. I keep getting the following error message:

Syntax error (missing operator) in query expression ‘(Select Top 1
[Dupe].Mileage FROM [Monthly Miles Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth’.

This is the entire string:

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth] DESC AS PreviousMileage
FROM [Monthly Miles from Master FD 03];

I have tried several different configurations to no avail; can you tell me
what I am doing wrong??

--
Bruce


"KARL DEWEY" wrote:

I left out the EquipmentID from the subquery --
SELECT EquipmentID, Odometer, TripDate, (SELECT YourTable.Odometer -
[XX].Odometer FROM YourTable AS [XX] WHERE [XX].EquipmentID =
YourTable.EquipmentID AND Max([XX].Odometer) YourTable.Odometer ORDER BY
[XX].Odometer DEC) AS TripMileage
FROM YourTable
ORDER BY YourTable.Odometer;

--
Build a little, test a little.


"Bruce" wrote:

I am running Access 2007 SP2 on Windows XP Professional SP 3. I have a query
that I need to run a subquery to obtain a value in the previous record.
Basically I have a query that contains mileages grouped by EquipmentID and
month. I need to calculate miles driven in a given month. I need the last
mileage reading in the previous months record to run the calculation on the
month that has focus. I know that I have to alias the query, but that is
where my memory ends. I have done this before with help from this site and
hope to be as fortunate again.
--
Bruce

Karl,

I removed some brackets [] from the field names and it wanted to run but now
I am getting the following error message:

Unknown Access database engine error

Here is the current code:

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].EquipmentID = [Monthly Miles from Master FD 03].EquipmentID AND
[Dupe].YearMonth [Monthly Miles from Master FD 03].YearMonth ORDER BY
[Dupe].[YearMonth] DESC) AS PreviousMileage
FROM [Monthly Miles from Master FD 03];

I think it may be due to the fact that I have not provide an IIf statement
when a null condition exists (like there is no previous record, the one it is
on is the first record)

Bruce


"KARL DEWEY" wrote:

I left out the EquipmentID from the subquery --
SELECT EquipmentID, Odometer, TripDate, (SELECT YourTable.Odometer -
[XX].Odometer FROM YourTable AS [XX] WHERE [XX].EquipmentID =
YourTable.EquipmentID AND Max([XX].Odometer) YourTable.Odometer ORDER BY
[XX].Odometer DEC) AS TripMileage
FROM YourTable
ORDER BY YourTable.Odometer;

--
Build a little, test a little.


"Bruce" wrote:

I am running Access 2007 SP2 on Windows XP Professional SP 3. I have a query
that I need to run a subquery to obtain a value in the previous record.
Basically I have a query that contains mileages grouped by EquipmentID and
month. I need to calculate miles driven in a given month. I need the last
mileage reading in the previous months record to run the calculation on the
month that has focus. I know that I have to alias the query, but that is
where my memory ends. I have done this before with help from this site and
hope to be as fortunate again.
--
Bruce

I would try something along the following lines

To fix the syntax try removing the parenthesis Before the ORDER BY in the
sub-query and adding a closing parenthesis after DESC.

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage
, (SELECT TOP 1 [Dupe].Mileage
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID]
AND [Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]
ORDER BY [Dupe].[YearMonth] DESC) AS PreviousMileage

FROM [Monthly Miles from Master FD 03];

That may still error. I would try something like the following.

SELECT [Monthly Miles from Master FD 03].EquipmentID
, [Monthly Miles from Master FD 03].YearMonth
, [Monthly Miles from Master FD 03].Mileage

, (SELECT Max([Dupe].Mileage)
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] =
[Monthly Miles from Master FD 03].[EquipmentID]
AND Dupe.Mileage [Monthly Miles from Master FD 03].[Mileage]) AS
PreviousMileage

FROM [Monthly Miles from Master FD 03];

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bruce wrote:
Karl,

Thank you for your reply. I keep getting the following error message:

Syntax error (missing operator) in query expression ‘(Select Top 1
[Dupe].Mileage FROM [Monthly Miles Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth’.

This is the entire string:

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth] DESC AS PreviousMileage
FROM [Monthly Miles from Master FD 03];

I have tried several different configurations to no avail; can you tell me
what I am doing wrong??

John,

I removed all brackets [] from the field names and it wanted to run but now
I am getting the following error message:

Unknown Access database engine error

I dont get this error until the query has run for a period of time and I get
it just when I thought it would display the results. Could it be from the
fact that I am not checking for nulls?

--
Bruce


"John Spencer" wrote:

I would try something along the following lines

To fix the syntax try removing the parenthesis Before the ORDER BY in the
sub-query and adding a closing parenthesis after DESC.

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage
, (SELECT TOP 1 [Dupe].Mileage
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID]
AND [Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]
ORDER BY [Dupe].[YearMonth] DESC) AS PreviousMileage

FROM [Monthly Miles from Master FD 03];

That may still error. I would try something like the following.

SELECT [Monthly Miles from Master FD 03].EquipmentID
, [Monthly Miles from Master FD 03].YearMonth
, [Monthly Miles from Master FD 03].Mileage

, (SELECT Max([Dupe].Mileage)
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] =
[Monthly Miles from Master FD 03].[EquipmentID]
AND Dupe.Mileage [Monthly Miles from Master FD 03].[Mileage]) AS
PreviousMileage

FROM [Monthly Miles from Master FD 03];

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bruce wrote:
Karl,

Thank you for your reply. I keep getting the following error message:

Syntax error (missing operator) in query expression ‘(Select Top 1
[Dupe].Mileage FROM [Monthly Miles Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth’.

This is the entire string:

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth] DESC AS PreviousMileage
FROM [Monthly Miles from Master FD 03];

I have tried several different configurations to no avail; can you tell me
what I am doing wrong??

.

John,

I got the query to run. However the query appears to be stuck in a loop. I
limited the selection to UD793, there should have been 26 records. There were
many interations of 26 records, I suspect there would have been at least 650
records. How do I code the query so it only runs once and does not keep
accessing the same records? Here is code that I used to get the query to run:
(I had to add the table and alias in the properties window)

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].EquipmentID = [Monthly Miles from Master FD 03].EquipmentID AND
[Dupe].YearMonth [Monthly Miles from Master FD 03].YearMonth ORDER BY
[Dupe].[YearMonth] DESC) AS PreviousMileage
FROM [Monthly Miles from Master FD 03], [Monthly Miles from Master FD 03] AS
Dupe
WHERE (([Monthly Miles from Master FD 03].EquipmentID)="UD793");
--
Bruce


"John Spencer" wrote:

I would try something along the following lines

To fix the syntax try removing the parenthesis Before the ORDER BY in the
sub-query and adding a closing parenthesis after DESC.

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage
, (SELECT TOP 1 [Dupe].Mileage
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID]
AND [Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]
ORDER BY [Dupe].[YearMonth] DESC) AS PreviousMileage

FROM [Monthly Miles from Master FD 03];

That may still error. I would try something like the following.

SELECT [Monthly Miles from Master FD 03].EquipmentID
, [Monthly Miles from Master FD 03].YearMonth
, [Monthly Miles from Master FD 03].Mileage

, (SELECT Max([Dupe].Mileage)
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] =
[Monthly Miles from Master FD 03].[EquipmentID]
AND Dupe.Mileage [Monthly Miles from Master FD 03].[Mileage]) AS
PreviousMileage

FROM [Monthly Miles from Master FD 03];

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bruce wrote:
Karl,

Thank you for your reply. I keep getting the following error message:

Syntax error (missing operator) in query expression ‘(Select Top 1
[Dupe].Mileage FROM [Monthly Miles Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth’.

This is the entire string:

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth] DESC AS PreviousMileage
FROM [Monthly Miles from Master FD 03];

I have tried several different configurations to no avail; can you tell me
what I am doing wrong??

.

Karl,

I got the query to run. However the query appears to be stuck in a loop. I
limited the selection to UD793, there should have been 26 records. There were
many interations of 26 records, I suspect there would have been at least 650
records. How do I code the query so it only runs once and does not keep
accessing the same records? Here is code that I used to get the query to run:
(I had to add the table and alias in the properties window)

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].EquipmentID = [Monthly Miles from Master FD 03].EquipmentID AND
[Dupe].YearMonth [Monthly Miles from Master FD 03].YearMonth ORDER BY
[Dupe].[YearMonth] DESC) AS PreviousMileage
FROM [Monthly Miles from Master FD 03], [Monthly Miles from Master FD 03] AS
Dupe
WHERE (([Monthly Miles from Master FD 03].EquipmentID)="UD793");
--
Bruce


"KARL DEWEY" wrote:

I left out the EquipmentID from the subquery --
SELECT EquipmentID, Odometer, TripDate, (SELECT YourTable.Odometer -
[XX].Odometer FROM YourTable AS [XX] WHERE [XX].EquipmentID =
YourTable.EquipmentID AND Max([XX].Odometer) YourTable.Odometer ORDER BY
[XX].Odometer DEC) AS TripMileage
FROM YourTable
ORDER BY YourTable.Odometer;

--
Build a little, test a little.


"Bruce" wrote:

I am running Access 2007 SP2 on Windows XP Professional SP 3. I have a query
that I need to run a subquery to obtain a value in the previous record.
Basically I have a query that contains mileages grouped by EquipmentID and
month. I need to calculate miles driven in a given month. I need the last
mileage reading in the previous months record to run the calculation on the
month that has focus. I know that I have to alias the query, but that is
where my memory ends. I have done this before with help from this site and
hope to be as fortunate again.
--
Bruce

Try this --
SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].EquipmentID = [Monthly Miles from Master FD 03].EquipmentID AND
[Dupe].YearMonth [Monthly Miles from Master FD 03].YearMonth ORDER BY
[Dupe].[YearMonth] DESC) AS PreviousMileage
FROM [Monthly Miles from Master FD 03]
WHERE (([Monthly Miles from Master FD 03].EquipmentID)="UD793");

--
Build a little, test a little.


"Bruce" wrote:

John,

I got the query to run. However the query appears to be stuck in a loop. I
limited the selection to UD793, there should have been 26 records. There were
many interations of 26 records, I suspect there would have been at least 650
records. How do I code the query so it only runs once and does not keep
accessing the same records? Here is code that I used to get the query to run:
(I had to add the table and alias in the properties window)

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].EquipmentID = [Monthly Miles from Master FD 03].EquipmentID AND
[Dupe].YearMonth [Monthly Miles from Master FD 03].YearMonth ORDER BY
[Dupe].[YearMonth] DESC) AS PreviousMileage
FROM [Monthly Miles from Master FD 03], [Monthly Miles from Master FD 03] AS
Dupe
WHERE (([Monthly Miles from Master FD 03].EquipmentID)="UD793");
--
Bruce


"John Spencer" wrote:

I would try something along the following lines

To fix the syntax try removing the parenthesis Before the ORDER BY in the
sub-query and adding a closing parenthesis after DESC.

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage
, (SELECT TOP 1 [Dupe].Mileage
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID]
AND [Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]
ORDER BY [Dupe].[YearMonth] DESC) AS PreviousMileage

FROM [Monthly Miles from Master FD 03];

That may still error. I would try something like the following.

SELECT [Monthly Miles from Master FD 03].EquipmentID
, [Monthly Miles from Master FD 03].YearMonth
, [Monthly Miles from Master FD 03].Mileage

, (SELECT Max([Dupe].Mileage)
FROM [Monthly Miles from Master FD 03] AS [Dupe]
WHERE [Dupe].[EquipmentID] =
[Monthly Miles from Master FD 03].[EquipmentID]
AND Dupe.Mileage [Monthly Miles from Master FD 03].[Mileage]) AS
PreviousMileage

FROM [Monthly Miles from Master FD 03];

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bruce wrote:
Karl,

Thank you for your reply. I keep getting the following error message:

Syntax error (missing operator) in query expression ‘(Select Top 1
[Dupe].Mileage FROM [Monthly Miles Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth’.

This is the entire string:

SELECT [Monthly Miles from Master FD 03].EquipmentID, [Monthly Miles from
Master FD 03].YearMonth, [Monthly Miles from Master FD 03].Mileage, (SELECT
TOP 1 [Dupe].Mileage FROM [Monthly Miles from Master FD 03] AS [Dupe] WHERE
[Dupe].[EquipmentID] = [Monthly Miles from Master FD 03].[EquipmentID] AND
[Dupe].[YearMonth] [Monthly Miles from Master FD 03].[YearMonth]) ORDER BY
[Dupe].[YearMonth] DESC AS PreviousMileage
FROM [Monthly Miles from Master FD 03];

I have tried several different configurations to no avail; can you tell me
what I am doing wrong??

.