A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access Query to only show most recent data



 
 
Thread Tools Display Modes
  #1  
Old October 11th, 2007, 05:13 PM posted to microsoft.public.access
AJCB
external usenet poster
 
Posts: 54
Default 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  
Old October 11th, 2007, 05:44 PM posted to microsoft.public.access
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default 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  
Old October 11th, 2007, 08:43 PM posted to microsoft.public.access
AJCB
external usenet poster
 
Posts: 54
Default 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  
Old October 12th, 2007, 07:33 AM posted to microsoft.public.access
Pieter Wijnen
external usenet poster
 
Posts: 1,354
Default 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  
Old October 12th, 2007, 09:38 PM posted to microsoft.public.access
AJCB
external usenet poster
 
Posts: 54
Default 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  
Old October 12th, 2007, 10:22 PM posted to microsoft.public.access
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default 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  
Old October 14th, 2007, 12:11 PM posted to microsoft.public.access
AJCB
external usenet poster
 
Posts: 54
Default 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  
Old October 14th, 2007, 12:26 PM posted to microsoft.public.access
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default 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  
Old October 15th, 2007, 08:59 AM posted to microsoft.public.access
AJCB
external usenet poster
 
Posts: 54
Default 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  
Old October 15th, 2007, 10:02 PM posted to microsoft.public.access
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.