SubQueries

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

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


"John Spencer" wrote:

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

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


"KARL DEWEY" wrote:

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

.

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 wrote:
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??

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


"John Spencer" wrote:

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 wrote:
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??

.