Only last record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone tell me what I would require entering into a query so that only
the last record would remain in the database. ALl others would be deleted.
I am trying to record data collected by a subform which also is used to
create a report, however the report contains all the records rather than just
the last one only.
 
From your description, it sounds like you want to delete records. If you
are collection data/records, why are you deleting them?

Do you want to show (in your report) only the "last" record entered? If so,
how will Access know which record is the "last" one? Do you have a
date/time field in each record?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff

I have a date field and have used the"last" within the query to show only
the last withing the form that is also used. The report that comes from teh
same shows all the records. I only wan the last record for the report.

Rudy
 
If you are using the Totals query, and the "Last" aggregation, be aware that
Access doesn't treat records the same way you would. You'll need to use the
"Max" aggregation to find the latest/most recent date/time.

If you'll post your SQL statement, folks should be able to offer ideas why
the query isn't working as you want it to.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff:

As suggested, here is the SQL

SELECT [Flight Stats].[Prime No], Max([Flight Stats].Dates) AS MaxOfDates,
[Flight Stats].Landing, [Dates]+30 AS [Landing Due Date], [Dates]+31 AS
[Landing Overdue Date]
FROM [Flight Stats]
GROUP BY [Flight Stats].[Prime No], [Flight Stats].Landing, [Dates]+30,
[Dates]+31
HAVING ((([Flight Stats].Landing)=True))
ORDER BY Max([Flight Stats].Dates) DESC;
 
Assumption:
Prime No is not a primary key in Flight Stats

SELECT M.[Prime No]
, M.Dates
, M.Landing
, [Dates]+30 AS [Landing Due Date]
, [Dates]+31 AS [Landing Overdue Date]
FROM [Flight Stats] As M
WHERE M.[Dates] =
(SELECT Max(T.[Dates])
FROM [Flight Stats] as T
WHERE T.[Prime No] = M.[Prime No])
ORDER BY M.Dates DESC;


Jeff:

As suggested, here is the SQL

SELECT [Flight Stats].[Prime No], Max([Flight Stats].Dates) AS MaxOfDates,
[Flight Stats].Landing, [Dates]+30 AS [Landing Due Date], [Dates]+31 AS
[Landing Overdue Date]
FROM [Flight Stats]
GROUP BY [Flight Stats].[Prime No], [Flight Stats].Landing, [Dates]+30,
[Dates]+31
HAVING ((([Flight Stats].Landing)=True))
ORDER BY Max([Flight Stats].Dates) DESC;

Jeff Boyce said:
If you are using the Totals query, and the "Last" aggregation, be aware that
Access doesn't treat records the same way you would. You'll need to use the
"Max" aggregation to find the latest/most recent date/time.

If you'll post your SQL statement, folks should be able to offer ideas why
the query isn't working as you want it to.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Yes, works perfectly. Thanks!

Rudy

John Spencer said:
Assumption:
Prime No is not a primary key in Flight Stats

SELECT M.[Prime No]
, M.Dates
, M.Landing
, [Dates]+30 AS [Landing Due Date]
, [Dates]+31 AS [Landing Overdue Date]
FROM [Flight Stats] As M
WHERE M.[Dates] =
(SELECT Max(T.[Dates])
FROM [Flight Stats] as T
WHERE T.[Prime No] = M.[Prime No])
ORDER BY M.Dates DESC;


Jeff:

As suggested, here is the SQL

SELECT [Flight Stats].[Prime No], Max([Flight Stats].Dates) AS MaxOfDates,
[Flight Stats].Landing, [Dates]+30 AS [Landing Due Date], [Dates]+31 AS
[Landing Overdue Date]
FROM [Flight Stats]
GROUP BY [Flight Stats].[Prime No], [Flight Stats].Landing, [Dates]+30,
[Dates]+31
HAVING ((([Flight Stats].Landing)=True))
ORDER BY Max([Flight Stats].Dates) DESC;

Jeff Boyce said:
If you are using the Totals query, and the "Last" aggregation, be aware that
Access doesn't treat records the same way you would. You'll need to use the
"Max" aggregation to find the latest/most recent date/time.

If you'll post your SQL statement, folks should be able to offer ideas why
the query isn't working as you want it to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

I have a date field and have used the"last" within the query to show only
the last withing the form that is also used. The report that comes from
teh
same shows all the records. I only wan the last record for the report.

Rudy

:

From your description, it sounds like you want to delete records. If you
are collection data/records, why are you deleting them?

Do you want to show (in your report) only the "last" record entered? If
so,
how will Access know which record is the "last" one? Do you have a
date/time field in each record?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Can anyone tell me what I would require entering into a query so that
only
the last record would remain in the database. ALl others would be
deleted.
I am trying to record data collected by a subform which also is used to
create a report, however the report contains all the records rather
than
just
the last one only.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top