Return only most recent date records

  • Thread starter Thread starter DataGuy
  • Start date Start date
D

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!
 
DataGuy said:
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])
Click to expand...

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

hth
PerL
 
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);
 
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.



Ken Snell said:
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>



DataGuy said:
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!
Click to expand...
Click to expand...
 
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!


Per Larsen said:
DataGuy said:
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])
Click to expand...

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!
Click to expand...
Click to expand...
 
DataGuy said:
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.
Click to expand...

"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!


DataGuy said:
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])
Click to expand...

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!
Click to expand...
Click to expand...
Click to expand...
 
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>

DataGuy said:
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.



Ken Snell said:
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>



DataGuy said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top