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 |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
SubQueries
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 |
#3
|
|||
|
|||
SubQueries
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 |
#4
|
|||
|
|||
SubQueries
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 |
#5
|
|||
|
|||
SubQueries
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 |
#6
|
|||
|
|||
SubQueries
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?? |
#7
|
|||
|
|||
SubQueries
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?? . |
#8
|
|||
|
|||
SubQueries
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?? . |
#9
|
|||
|
|||
SubQueries
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 |
#10
|
|||
|
|||
SubQueries
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?? . |
|
Thread Tools | |
Display Modes | |
|
|