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
|
|||
|
|||
Access Query to only show most recent data
I have an Access table with the following fields:
Pallet ID - eg. 11234$33 Location - eg. NW Date/Time - eg. 10/10/2007 18:00:00 What I would like to do is have a query which shows me the latest record for each Pallet ID. Can this be done using Expression Builder as I have not learned SQL yet? Cheers AJ |
#2
|
|||
|
|||
Access Query to only show most recent data
Normally this would be done by means of a subquery, for which you'll need to
write the SQL for the subquery at least. Its pretty straightforward, however, and the query would look like this (I've assumed the table is called Pallets), so you might like to give it a try: SELECT * FROM Pallets AS P1 WHERE [Date/Time] = (SELECT MAX([Date Time] FROM Pallets AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]); The way it works is that the subquery finds the row with the latest (MAX) date/time for the Pallet ID which matches the outer query's current row. Note how the two instances of the Pallets table are distinguished by giving them aliases P1 and P2. This type of subquery is known as a 'correlated subquery'. The rows returned by the outer query are restricted to those where the date/time matches that returned by the subquery, so you get the rows with the latest date for each pallet. By changing the name of the table from pallets to whatever the name of your table actually is you should be able to simply paste the above SQL into a new query in SQL view and then open it. It is possible to do it without writing any SQL, but you'll need two queries. First create a 'totals' query in query design view which GROUPs BY Pallet ID and returns the MAX Date/Time. Save this query and then create another query in which you INNER JOIN (which is the default join type when you create a query in design view) the original table to this query on The Pallet ID AND Date/Time columns. In this final query include columns only from the table, not the first query; that's purely there to identify the latest date/time values per pallet. The rows returned will be those where there is a match between Pallet ID and Date/Time between the table and the first query. Ken Sheridan Stafford, England "AJCB" wrote: I have an Access table with the following fields: Pallet ID - eg. 11234$33 Location - eg. NW Date/Time - eg. 10/10/2007 18:00:00 What I would like to do is have a query which shows me the latest record for each Pallet ID. Can this be done using Expression Builder as I have not learned SQL yet? Cheers AJ |
#3
|
|||
|
|||
Access Query to only show most recent data
I tried to put the string below into an SQL query.
It was fine until I went to save it, the it said that there was an expression missing from: WHERE [Date/Time] = (SELECT MAX([Date Time] Should there be something after the '='? "Ken Sheridan" wrote: Normally this would be done by means of a subquery, for which you'll need to write the SQL for the subquery at least. Its pretty straightforward, however, and the query would look like this (I've assumed the table is called Pallets), so you might like to give it a try: SELECT * FROM Pallets AS P1 WHERE [Date/Time] = (SELECT MAX([Date Time] FROM Pallets AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]); The way it works is that the subquery finds the row with the latest (MAX) date/time for the Pallet ID which matches the outer query's current row. Note how the two instances of the Pallets table are distinguished by giving them aliases P1 and P2. This type of subquery is known as a 'correlated subquery'. The rows returned by the outer query are restricted to those where the date/time matches that returned by the subquery, so you get the rows with the latest date for each pallet. By changing the name of the table from pallets to whatever the name of your table actually is you should be able to simply paste the above SQL into a new query in SQL view and then open it. It is possible to do it without writing any SQL, but you'll need two queries. First create a 'totals' query in query design view which GROUPs BY Pallet ID and returns the MAX Date/Time. Save this query and then create another query in which you INNER JOIN (which is the default join type when you create a query in design view) the original table to this query on The Pallet ID AND Date/Time columns. In this final query include columns only from the table, not the first query; that's purely there to identify the latest date/time values per pallet. The rows returned will be those where there is a match between Pallet ID and Date/Time between the table and the first query. Ken Sheridan Stafford, England "AJCB" wrote: I have an Access table with the following fields: Pallet ID - eg. 11234$33 Location - eg. NW Date/Time - eg. 10/10/2007 18:00:00 What I would like to do is have a query which shows me the latest record for each Pallet ID. Can this be done using Expression Builder as I have not learned SQL yet? Cheers AJ |
#4
|
|||
|
|||
Access Query to only show most recent data
You have to exchange [Date/Time] & [Date Time] for your actual Date Field
from your table Pieter "AJCB" wrote in message ... I tried to put the string below into an SQL query. It was fine until I went to save it, the it said that there was an expression missing from: WHERE [Date/Time] = (SELECT MAX([Date Time] Should there be something after the '='? "Ken Sheridan" wrote: Normally this would be done by means of a subquery, for which you'll need to write the SQL for the subquery at least. Its pretty straightforward, however, and the query would look like this (I've assumed the table is called Pallets), so you might like to give it a try: SELECT * FROM Pallets AS P1 WHERE [Date/Time] = (SELECT MAX([Date Time] FROM Pallets AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]); The way it works is that the subquery finds the row with the latest (MAX) date/time for the Pallet ID which matches the outer query's current row. Note how the two instances of the Pallets table are distinguished by giving them aliases P1 and P2. This type of subquery is known as a 'correlated subquery'. The rows returned by the outer query are restricted to those where the date/time matches that returned by the subquery, so you get the rows with the latest date for each pallet. By changing the name of the table from pallets to whatever the name of your table actually is you should be able to simply paste the above SQL into a new query in SQL view and then open it. It is possible to do it without writing any SQL, but you'll need two queries. First create a 'totals' query in query design view which GROUPs BY Pallet ID and returns the MAX Date/Time. Save this query and then create another query in which you INNER JOIN (which is the default join type when you create a query in design view) the original table to this query on The Pallet ID AND Date/Time columns. In this final query include columns only from the table, not the first query; that's purely there to identify the latest date/time values per pallet. The rows returned will be those where there is a match between Pallet ID and Date/Time between the table and the first query. Ken Sheridan Stafford, England "AJCB" wrote: I have an Access table with the following fields: Pallet ID - eg. 11234$33 Location - eg. NW Date/Time - eg. 10/10/2007 18:00:00 What I would like to do is have a query which shows me the latest record for each Pallet ID. Can this be done using Expression Builder as I have not learned SQL yet? Cheers AJ |
#5
|
|||
|
|||
Access Query to only show most recent data
I have put the following string in:
SELECT [Job Variable Data].[Pallet ID], [Job Variable Data].[Date/Time], [Job Variable Data].Location, [Job Variable Data].[User ID] FROM [Job Variable Data] AS P1 WHERE [Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]); This string shows the table 'Job Variable Data' with the fields: Pallet ID, Date/Time, Location and User ID When I go to save it, it come up with the following error: Missing ),], or Item in query expression'[Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]);'. Any ideas what I have done wrong as again, I have never used SQL? "Pieter Wijnen" wrote: You have to exchange [Date/Time] & [Date Time] for your actual Date Field from your table Pieter "AJCB" wrote in message ... I tried to put the string below into an SQL query. It was fine until I went to save it, the it said that there was an expression missing from: WHERE [Date/Time] = (SELECT MAX([Date Time] Should there be something after the '='? "Ken Sheridan" wrote: Normally this would be done by means of a subquery, for which you'll need to write the SQL for the subquery at least. Its pretty straightforward, however, and the query would look like this (I've assumed the table is called Pallets), so you might like to give it a try: SELECT * FROM Pallets AS P1 WHERE [Date/Time] = (SELECT MAX([Date Time] FROM Pallets AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]); The way it works is that the subquery finds the row with the latest (MAX) date/time for the Pallet ID which matches the outer query's current row. Note how the two instances of the Pallets table are distinguished by giving them aliases P1 and P2. This type of subquery is known as a 'correlated subquery'. The rows returned by the outer query are restricted to those where the date/time matches that returned by the subquery, so you get the rows with the latest date for each pallet. By changing the name of the table from pallets to whatever the name of your table actually is you should be able to simply paste the above SQL into a new query in SQL view and then open it. It is possible to do it without writing any SQL, but you'll need two queries. First create a 'totals' query in query design view which GROUPs BY Pallet ID and returns the MAX Date/Time. Save this query and then create another query in which you INNER JOIN (which is the default join type when you create a query in design view) the original table to this query on The Pallet ID AND Date/Time columns. In this final query include columns only from the table, not the first query; that's purely there to identify the latest date/time values per pallet. The rows returned will be those where there is a match between Pallet ID and Date/Time between the table and the first query. Ken Sheridan Stafford, England "AJCB" wrote: I have an Access table with the following fields: Pallet ID - eg. 11234$33 Location - eg. NW Date/Time - eg. 10/10/2007 18:00:00 What I would like to do is have a query which shows me the latest record for each Pallet ID. Can this be done using Expression Builder as I have not learned SQL yet? Cheers AJ |
#6
|
|||
|
|||
Access Query to only show most recent data
Apologies for the delay in responding, but I've been on the road all day.
The problem is of my making, as I omitted a closing parenthesis. It should be: MAX([Job Variable Data].[Date/Time]) Ken Sheridan Stafford, England "AJCB" wrote: I have put the following string in: SELECT [Job Variable Data].[Pallet ID], [Job Variable Data].[Date/Time], [Job Variable Data].Location, [Job Variable Data].[User ID] FROM [Job Variable Data] AS P1 WHERE [Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]); This string shows the table 'Job Variable Data' with the fields: Pallet ID, Date/Time, Location and User ID When I go to save it, it come up with the following error: Missing ),], or Item in query expression'[Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]);'. Any ideas what I have done wrong as again, I have never used SQL? "Pieter Wijnen" wrote: You have to exchange [Date/Time] & [Date Time] for your actual Date Field from your table Pieter "AJCB" wrote in message ... I tried to put the string below into an SQL query. It was fine until I went to save it, the it said that there was an expression missing from: WHERE [Date/Time] = (SELECT MAX([Date Time] Should there be something after the '='? "Ken Sheridan" wrote: Normally this would be done by means of a subquery, for which you'll need to write the SQL for the subquery at least. Its pretty straightforward, however, and the query would look like this (I've assumed the table is called Pallets), so you might like to give it a try: SELECT * FROM Pallets AS P1 WHERE [Date/Time] = (SELECT MAX([Date Time] FROM Pallets AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]); The way it works is that the subquery finds the row with the latest (MAX) date/time for the Pallet ID which matches the outer query's current row. Note how the two instances of the Pallets table are distinguished by giving them aliases P1 and P2. This type of subquery is known as a 'correlated subquery'. The rows returned by the outer query are restricted to those where the date/time matches that returned by the subquery, so you get the rows with the latest date for each pallet. By changing the name of the table from pallets to whatever the name of your table actually is you should be able to simply paste the above SQL into a new query in SQL view and then open it. It is possible to do it without writing any SQL, but you'll need two queries. First create a 'totals' query in query design view which GROUPs BY Pallet ID and returns the MAX Date/Time. Save this query and then create another query in which you INNER JOIN (which is the default join type when you create a query in design view) the original table to this query on The Pallet ID AND Date/Time columns. In this final query include columns only from the table, not the first query; that's purely there to identify the latest date/time values per pallet. The rows returned will be those where there is a match between Pallet ID and Date/Time between the table and the first query. Ken Sheridan Stafford, England "AJCB" wrote: I have an Access table with the following fields: Pallet ID - eg. 11234$33 Location - eg. NW Date/Time - eg. 10/10/2007 18:00:00 What I would like to do is have a query which shows me the latest record for each Pallet ID. Can this be done using Expression Builder as I have not learned SQL yet? Cheers AJ |
#7
|
|||
|
|||
Access Query to only show most recent data
I have made this correction and now it says: Syntax error . in query expression '[Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]); Any more suggestions? AJ |
#8
|
|||
|
|||
Access Query to only show most recent data
You're missing the periods between the table aliases and the field names:
(SELECT MAX([Job Variable Data].[Date/Time]) FROM [Job Variable Data] AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "AJCB" wrote in message ... I have made this correction and now it says: Syntax error . in query expression '[Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]); Any more suggestions? AJ |
#9
|
|||
|
|||
Access Query to only show most recent data
Hi.
I have made this amendment and the query now lets me save it. However. It is asking me to input the data for the fields as though it is not finding them? Does this have something to do with the P1 and P2? "Douglas J. Steele" wrote: You're missing the periods between the table aliases and the field names: (SELECT MAX([Job Variable Data].[Date/Time]) FROM [Job Variable Data] AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "AJCB" wrote in message ... I have made this correction and now it says: Syntax error . in query expression '[Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]); Any more suggestions? AJ |
#10
|
|||
|
|||
Access Query to only show most recent data
What's the exact SQL you're trying to run? Have you double-checked to ensure
that the table and field names in the query are all typed correctly? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "AJCB" wrote in message ... Hi. I have made this amendment and the query now lets me save it. However. It is asking me to input the data for the fields as though it is not finding them? Does this have something to do with the P1 and P2? "Douglas J. Steele" wrote: You're missing the periods between the table aliases and the field names: (SELECT MAX([Job Variable Data].[Date/Time]) FROM [Job Variable Data] AS P2 WHERE P2.[Pallet ID] = P1.[Pallet ID]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "AJCB" wrote in message ... I have made this correction and now it says: Syntax error . in query expression '[Job Variable Data].[Date/Time] = (SELECT MAX([Job Variable Data].[Date/Time] FROM [Job Variable Data] AS P2 WHERE P2 [Pallet ID] = P1 [Pallet ID]); Any more suggestions? AJ |
|
Thread Tools | |
Display Modes | |
|
|