If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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"); |
#12
|
|||
|
|||
SubQueries
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"); . |
#13
|
|||
|
|||
SubQueries
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?? . |
#14
|
|||
|
|||
SubQueries
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?? |
#15
|
|||
|
|||
SubQueries
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?? . |
|
Thread Tools | |
Display Modes | |
|
|