Access Query to only show most recent data

[AJCB]

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

 

[Ken Sheridan]

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]

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

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

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

 

[Pieter Wijnen]

You have to exchange [Date/Time] & [Date Time] for your actual Date Field
from your table

Pieter

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

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

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

 

[AJCB]

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?

You have to exchange [Date/Time] & [Date Time] for your actual Date Field
from your table

Pieter

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

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

:

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

 

[Ken Sheridan]

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

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?

You have to exchange [Date/Time] & [Date Time] for your actual Date Field
from your table

Pieter

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


:

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

:

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

 

[AJCB]

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

 

[Douglas J. Steele]

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

(no private e-mails, please)

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

 

[AJCB]

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?

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

(no private e-mails, please)

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

 

[Douglas J. Steele]

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

(no private e-mails, please)

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?

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

(no private e-mails, please)

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

 

[aero-spaces]

This has really helped me out a lot. I have one question for a slight
Modification. Here's what I have so far:

SELECT S1.[Part Number], S1.[Qty Shipped], S1.[Date Shipped], S1.[Shipped To]
FROM Shipping AS S1
WHERE (((S1.[Date Shipped])=(SELECT MAX([Date Shipped])
FROM Shipping AS S2
WHERE S2.[Part Number] = S1.[Part Number])));

Now, let's say that I shipped 3 of one part number and 1 more of the SAME
part number on the SAME day. In my results view for the query above, I would
see:

Part Number Qty Shipped
12345 3
12345 1

I would like to SUM those together to get:

Part Number Qty Shipped
12345 4

I tried adding SUM(S1.[Qty Shipped]), but that yielded an error message.
Here's what I tried:

SELECT S1.[Part Number], SUM(S1.[Qty Shipped]), S1.[Date Shipped],
S1.[Shipped To]
FROM Shipping AS S1
WHERE (((S1.[Date Shipped])=(SELECT MAX([Date Shipped])
FROM Shipping AS S2
WHERE S2.[Part Number] = S1.[Part Number])));

I am still an SQL noob, but learning quickly. Can someone point out my
error(s) please?

Thanks!

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

(no private e-mails, please)

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?

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

(no private e-mails, please)



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

 

[Douglas J. Steele]

Whenever you use an aggregate function (Sum, Min, Max, etc.), you need to
include each non-aggregated field in a GROUP BY clause:

SELECT S1.[Part Number], SUM(S1.[Qty Shipped]), S1.[Date Shipped],
S1.[Shipped To]
FROM Shipping AS S1
WHERE (((S1.[Date Shipped])=(SELECT MAX([Date Shipped])
FROM Shipping AS S2
WHERE S2.[Part Number] = S1.[Part Number])))
GROUP BY S1.[Part Number], S1.[Date Shipped], S1.[Shipped To]




--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This has really helped me out a lot. I have one question for a slight
Modification. Here's what I have so far:

SELECT S1.[Part Number], S1.[Qty Shipped], S1.[Date Shipped], S1.[Shipped
To]
FROM Shipping AS S1
WHERE (((S1.[Date Shipped])=(SELECT MAX([Date Shipped])
FROM Shipping AS S2
WHERE S2.[Part Number] = S1.[Part Number])));

Now, let's say that I shipped 3 of one part number and 1 more of the SAME
part number on the SAME day. In my results view for the query above, I
would
see:

Part Number Qty Shipped
12345 3
12345 1

I would like to SUM those together to get:

Part Number Qty Shipped
12345 4

I tried adding SUM(S1.[Qty Shipped]), but that yielded an error message.
Here's what I tried:

SELECT S1.[Part Number], SUM(S1.[Qty Shipped]), S1.[Date Shipped],
S1.[Shipped To]
FROM Shipping AS S1
WHERE (((S1.[Date Shipped])=(SELECT MAX([Date Shipped])
FROM Shipping AS S2
WHERE S2.[Part Number] = S1.[Part Number])));

I am still an SQL noob, but learning quickly. Can someone point out my
error(s) please?

Thanks!

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

(no private e-mails, please)

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?

:

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

(no private e-mails, please)



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