Return only most recent date records

[DataGuy]

Hello!

I have a table that I use to track files being transferred to & from our
company. When a new file arrives, it's 'Transfer_Date' & 'Part_#' is
logged.

I need to figure out a way to create a query to return only the most recent
record for each part number. I thought if I could maybe do something like
this:

MIN=(Now - [CAD LOG]![Transfer Date])

But I cannot get it to return anything. It doesn't error out, but I get no
results, so I don't know what to fix.

Any suggestions are appreciated.

Thank you!

 

[Per Larsen]

Hello!

I have a table that I use to track files being transferred to & from our
company. When a new file arrives, it's 'Transfer_Date' & 'Part_#' is
logged.

I need to figure out a way to create a query to return only the most recent
record for each part number. I thought if I could maybe do something like
this:

MIN=(Now - [CAD LOG]![Transfer Date])

select top 1 *
from <table>
order by [Transfer Date] desc

hth
PerL

 

[Ken Snell [MVP]]

This is one way to do it:

SELECT [CAD LOG].*
FROM [CAD LOG]
WHERE [CAD LOG].TransferDate =
(SELECT Max(C.TransferDate)
FROM [CAD LOG] AS C
WHERE C.PartNumber = [CAD LOG].PartNumber);

 

[DataGuy]

Hi Ken,

I'm trying to enter this in 'Build...' for my query. Can you check my copy?

SELECT [CAD LOG].* FROM [CAD LOG] WHERE [CAD LOG]![Transfer Date]=(SELECT
Max( [CAD LOG]![Transfer Date] ) FROM [CAD LOG] AS C WHERE C.Part #= [CAD
LOG]![PART #] );

My 2 fields are:

[CAD LOG]![Transfer Date]
[CAD LOG]![PART #]

Thank you.

This is one way to do it:

SELECT [CAD LOG].*
FROM [CAD LOG]
WHERE [CAD LOG].TransferDate =
(SELECT Max(C.TransferDate)
FROM [CAD LOG] AS C
WHERE C.PartNumber = [CAD LOG].PartNumber);
--

Ken Snell
<MS ACCESS MVP>

Hello!

I have a table that I use to track files being transferred to & from our
company. When a new file arrives, it's 'Transfer_Date' & 'Part_#' is
logged.

I need to figure out a way to create a query to return only the most
recent record for each part number. I thought if I could maybe do
something like this:

MIN=(Now - [CAD LOG]![Transfer Date])

But I cannot get it to return anything. It doesn't error out, but I get
no results, so I don't know what to fix.

Any suggestions are appreciated.

Thank you!

 

[DataGuy]

Per,

Can you explain the predicate 'TOP 1*'? Don't I need to tell the SELECT
statement which field to pick the TOP from?

This is my first use of 'select', so I'm on the learning curve here.

Thank you!

Hello!

I have a table that I use to track files being transferred to & from our
company. When a new file arrives, it's 'Transfer_Date' & 'Part_#' is
logged.

I need to figure out a way to create a query to return only the most
recent record for each part number. I thought if I could maybe do
something like this:

MIN=(Now - [CAD LOG]![Transfer Date])

select top 1 *
from <table>
order by [Transfer Date] desc

hth
PerL

But I cannot get it to return anything. It doesn't error out, but I get
no results, so I don't know what to fix.

Any suggestions are appreciated.

Thank you!

 

[Per Larsen]

Per,

Can you explain the predicate 'TOP 1*'? Don't I need to tell the SELECT
statement which field to pick the TOP from?

This is my first use of 'select', so I'm on the learning curve here.

"SELECT TOP N" says return the 'N' top-most rows of the query, the asterisk (*)
says return all columns (you don't have to use all of them, anyway, I just used
it because I don't know the name of your columns).

If returning the topmost N rows of a query shall be of any interest to you, they
have to be ordered, and if you order them descending by the date, you'll get the
newest one at the top of the query (row number one).

Now, when saying 'select top 1 *', the query will return the first row according
to the ordering specified, therefore:

select top 1 *
from [CAD LOG]
order by [Transfer Date] desc

will return all columns of the 'newest' record.

Hth PerL

Thank you!

Hello!

I have a table that I use to track files being transferred to & from our
company. When a new file arrives, it's 'Transfer_Date' & 'Part_#' is
logged.

I need to figure out a way to create a query to return only the most
recent record for each part number. I thought if I could maybe do
something like this:

MIN=(Now - [CAD LOG]![Transfer Date])

select top 1 *
from <table>
order by [Transfer Date] desc

hth
PerL

But I cannot get it to return anything. It doesn't error out, but I get
no results, so I don't know what to fix.

Any suggestions are appreciated.

Thank you!

 

[Ken Snell [MVP]]

Slight tweaking....


SELECT [CAD LOG].* FROM [CAD LOG] WHERE [CAD LOG].[Transfer Date]=(SELECT
Max( C.[Transfer Date] ) FROM [CAD LOG] AS C WHERE C.[Part #]= [CAD
LOG].[PART #] );


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I'm trying to enter this in 'Build...' for my query. Can you check my
copy?

SELECT [CAD LOG].* FROM [CAD LOG] WHERE [CAD LOG]![Transfer Date]=(SELECT
Max( [CAD LOG]![Transfer Date] ) FROM [CAD LOG] AS C WHERE C.Part #= [CAD
LOG]![PART #] );

My 2 fields are:

[CAD LOG]![Transfer Date]
[CAD LOG]![PART #]

Thank you.

This is one way to do it:

SELECT [CAD LOG].*
FROM [CAD LOG]
WHERE [CAD LOG].TransferDate =
(SELECT Max(C.TransferDate)
FROM [CAD LOG] AS C
WHERE C.PartNumber = [CAD LOG].PartNumber);
--

Ken Snell
<MS ACCESS MVP>

Hello!

I have a table that I use to track files being transferred to & from our
company. When a new file arrives, it's 'Transfer_Date' & 'Part_#' is
logged.

I need to figure out a way to create a query to return only the most
recent record for each part number. I thought if I could maybe do
something like this:

MIN=(Now - [CAD LOG]![Transfer Date])

But I cannot get it to return anything. It doesn't error out, but I get
no results, so I don't know what to fix.

Any suggestions are appreciated.

Thank you!