SubQueries

[Bruce]

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.

 

[KARL DEWEY]

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;

 

[KARL DEWEY]

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;

 

[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

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.

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

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.

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.

 

[John Spencer]

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]

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

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

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]

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

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

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,

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

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.

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.

 

[KARL DEWEY]

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.

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

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

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 Spencer]

You have added the table twice in the from clause with NO join. It should NOT
be in the FROM clause twice

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");

When you had the table in the FROM clause twice with no join it created a row
for every combination of the records. So if you had 26 UD793 records and 1000
records total, you were going to process 26,000 rows that had UD793.

As I said I would use something like the following instead of the above. Note
that I have assigned an "alias" to [Monthly Miles from Master FD 03] in the
main query. It shortens typing and it makes things clearer (for me) when I am
constructing queries. If it bothers you, then remove the M03 and replace it
with [Monthly Miles from Master FD 03] - except of course in the from clause
where you should just remove "as M03".

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


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

 

[Bruce]

John,

Did not work. Removed the second iteration of “FROM [Monthly Miles from
Master FD 03], [Monthly Miles from Master FD 03] AS Dupe
“ as Aliasing Monthly Miles from Master FD 03 as Dupe was causing the loop
problem. When I removed the second iteration I was getting Error 3072 (For
example, a table level validation rule that references a numeric data type
must have a numeric validation clause. If a string validation clause is used,
then this error will be returned).

I then created a table from Monthly Miles from Master FD 03 and named it
Test1. The query ran with this code: SELECT Test1.EquipmentID,
Test1.YearMonth, Test1.Mileage, (SELECT TOP 1 [Dupe].Mileage FROM [Test1] AS
[Dupe] WHERE [Dupe].EquipmentID = [Test1].EquipmentID AND [Dupe].YearMonth <
[Test1].YearMonth ORDER BY [Dupe].YearMonth DESC) AS PreviousMileage
FROM Test1
WHERE (((Test1.EquipmentID)="UD793"));.

It seems that a subquery will not work from another query and only from a
table.

Question is there a way to make a query look like a table to another query
or do I have to create a table each time I want to run this routine? I do not
remember having to make a table the last time I used a subquery, but that was
in Access 2003.

Any ideas??

--
Bruce

You have added the table twice in the from clause with NO join. It should NOT
be in the FROM clause twice

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");

When you had the table in the FROM clause twice with no join it created a row
for every combination of the records. So if you had 26 UD793 records and 1000
records total, you were going to process 26,000 rows that had UD793.

As I said I would use something like the following instead of the above. Note
that I have assigned an "alias" to [Monthly Miles from Master FD 03] in the
main query. It shortens typing and it makes things clearer (for me) when I am
constructing queries. If it bothers you, then remove the M03 and replace it
with [Monthly Miles from Master FD 03] - except of course in the from clause
where you should just remove "as M03".

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


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

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]

Karl,

Did not work. Removed the second iteration of “FROM [Monthly Miles from
Master FD 03], [Monthly Miles from Master FD 03] AS Dupe
“ as Aliasing Monthly Miles from Master FD 03 as Dupe was causing the loop
problem. When I removed the second iteration I was getting Error 3072 (For
example, a table level validation rule that references a numeric data type
must have a numeric validation clause. If a string validation clause is used,
then this error will be returned).

I then created a table from Monthly Miles from Master FD 03 and named it
Test1. The query ran with this code: SELECT Test1.EquipmentID,
Test1.YearMonth, Test1.Mileage, (SELECT TOP 1 [Dupe].Mileage FROM [Test1] AS
[Dupe] WHERE [Dupe].EquipmentID = [Test1].EquipmentID AND [Dupe].YearMonth <
[Test1].YearMonth ORDER BY [Dupe].YearMonth DESC) AS PreviousMileage
FROM Test1
WHERE (((Test1.EquipmentID)="UD793"));.

It seems that a subquery will not work from another query and only from a
table.

Question is there a way to make a query look like a table to another query
or do I have to create a table each time I want to run this routine? I do not
remember having to make a table the last time I used a subquery, but that was
in Access 2003.

Any ideas??

--
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.

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

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 Spencer]

Did you try my suggested alternative?

And you should be able to reference a query as the source for a subquery in
most cases. Since you have not posted the query
Monthly Miles from Master FD 03
I find it difficult to diagnose the problem. It may be that you should be
working directly from the tables involved in creating that query. On the
other hand you should be able to use a query as the source for a subquery.


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

 

[Bruce]

John,

Yes, I did try your alternative and it gave me the same error 3072. That is
when I tried the table route which worked first time.

Thanks for all of your help! It makes it easier when you can bounce ideas
around.

--
Bruce

Did you try my suggested alternative?

And you should be able to reference a query as the source for a subquery in
most cases. Since you have not posted the query
Monthly Miles from Master FD 03
I find it difficult to diagnose the problem. It may be that you should be
working directly from the tables involved in creating that query. On the
other hand you should be able to use a query as the source for a subquery.


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

John,

Did not work. Removed the second iteration of “FROM [Monthly Miles from
Master FD 03], [Monthly Miles from Master FD 03] AS Dupe
“ as Aliasing Monthly Miles from Master FD 03 as Dupe was causing the loop
problem. When I removed the second iteration I was getting Error 3072 (For
example, a table level validation rule that references a numeric data type
must have a numeric validation clause. If a string validation clause is used,
then this error will be returned).

I then created a table from Monthly Miles from Master FD 03 and named it
Test1. The query ran with this code: SELECT Test1.EquipmentID,
Test1.YearMonth, Test1.Mileage, (SELECT TOP 1 [Dupe].Mileage FROM [Test1] AS
[Dupe] WHERE [Dupe].EquipmentID = [Test1].EquipmentID AND [Dupe].YearMonth <
[Test1].YearMonth ORDER BY [Dupe].YearMonth DESC) AS PreviousMileage
FROM Test1
WHERE (((Test1.EquipmentID)="UD793"));.

It seems that a subquery will not work from another query and only from a
table.

Question is there a way to make a query look like a table to another query
or do I have to create a table each time I want to run this routine? I do not
remember having to make a table the last time I used a subquery, but that was
in Access 2003.

Any ideas??

.