Last Record

Last Record

Post by PR » Fri, 10 Dec 2004 04:15:50


I have a table which contains the following

Customer Number:
Customer Date:
Customer Item:

I am tiring to build a query that returns the last date a customer brought
and item and what that item was.

I have tried to use group by 'Customer Number' with last 'Customer Date'
and last 'Customer Item'. I get the last date but not the last item? I have
tried to use max, but get the same.

Can anyone help.

Paul R
 
 
 

Last Record

Post by Michel Wal » Fri, 10 Dec 2004 04:27:19

Hi,



have you tried one of the four solutions presented at
http://www.yqcomputer.com/



Hoping it may help,
Vanderghast, Access MVP

 
 
 

Last Record

Post by Keit » Fri, 16 Sep 2005 04:11:47

Is it possible to find the value of a field in the previous record entered?
I have a database which I update, but the value of one field is dependant on
the previous record.

So if Field 1 of Record1 is 7, then Field 1 of the next record could be 8 or
0 depending on the value of field 2, if field 2 of record 2 is a 0 then
Field 1 becomes 0, however if Field 2 is 1 then Field 1 of record 2 would be
8

Field 1 Field 2
1 7
1 8
1 9
0 0
0 0
1 1
1 2
1 3

etc
Hope this makes sense and someone can help?

Thanks
 
 
 

Last Record

Post by Allen Brow » Fri, 16 Sep 2005 10:42:37

Answered in m.p.a.reports
 
 
 

Last Record

Post by QmFyYXR0b2 » Fri, 09 Dec 2005 17:32:02

In database, I have three tables. THe first one has the audit findings, the
second one has the responses from the clients, and the third one has the
follow-up work on th implementation of the recommendation. In the last table
there may be multiple records tied up to that same finding and to that same
client response. I want to generate a query which will show the audit
finding, the management response, an only the LAST audit follow-up entry. I
have designed the below query, but it does not work and I do not get the last
record in the table "follow-up entries for findings".

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title of
Issue], [Risk Severity Code], Recommendation, [Responsible Department],
[Management Action Plan], [Target Completion Date], [Revised Target Date],
[Actual Completion Date], [Follow-up Status], [Change History], [Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], Last([tbl
Follow-up Entries for Findings].[Management's Status Description]) AS
[LastOfManagement's Status Description], Last([tbl Follow-up Entries for
Findings].[Auditor Comments]) AS [LastOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT JOIN [tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management Responses].[Comment Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code], [tbl
Comments].Recommendation, [tbl Management Responses].[Responsible
Department], [tbl Management Responses].[Management Action Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="fa-lbr-04-03"))
ORDER BY [tbl Comments].[Order of appearance];

Any clue?
 
 
 

Last Record

Post by Jeff Boyc » Fri, 09 Dec 2005 22:47:14

efine "Last". Are you quite certain that Access uses your definition?

One approach might be to first use a query to obtain the "last" record for
each audit from the follow-up table (you'll need to decide how to define
"last" -- I assume you mean the most-recent-date). Once you have that query
returning a single row per audit, you could join your other tables and that
query together in a new query.

--
Regards

Jeff Boyce
<Office/Access MVP>

"Barattolo_67" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
the
table
same
I
last
[Management
Completion
Management
[tbl
Table
Management
Name],
Completion
Management

 
 
 

Last Record

Post by QmFyYXR0b2 » Sat, 10 Dec 2005 03:15:02

eff,

Thanks for your reply. Yes, last means the one with the most recent
follow-up date. What you are suggesting, I have already tried and it did not
work. I have tried to set up a first query that pulls out the record with the
"Max" date in the follow-up entries (there is actually a 'follow-up date
field'). This appears to work, but when I join it back with the other tables,
then the grouping by max does not work no more, and I get all the follow-up
entries again.

Ideas?

"Jeff Boyce" ha scritto:

 
 
 

Last Record

Post by Jeff Boyc » Sat, 10 Dec 2005 22:05:04

rom your description, I can't tell if you are trying to make a single query
do all that, or if you have two separate queries. It sounded a little like
you are using a single query.

My earlier suggestion was to create the "Max" query, close it, create a new
query, add the table and the Max query to the new one, joined on the date
field in each (and what ever other fields need to match up).

Regards

Jeff Boyce
<Office/Access MVP>

"Barattolo_67" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
not
the
tables,
follow-up
for
query
that
findings,
the
entry.
the
Department],
Date],
Name],
[tbl
Last([tbl
for
JOIN
status
Responses].[Comment
for
[tbl

 
 
 

Last Record

Post by QmFyYXR0b2 » Sun, 11 Dec 2005 00:28:03

eff,

One otr two queries does not matter, as far as I get the desired results.
However, right now I am using a single query.

"Jeff Boyce" wrote:

 
 
 

Last Record

Post by Haggr via » Sat, 10 Feb 2007 10:02:15

I would like to find the that record of a job "[jobNumber] using [FileNumber]
a "Auto Number" field and all the other info, or atleast the info I need,
with that record. Ex. Sometime I need the last record for a "job" where a
field [Code] is "2" or a the last record for a "job" where the field [Date]
=>Date. All of these fields are in the same table. I can do it in Query grid
design, but it take a few sub queries,sometime alot, to do it. I know you
guys you probably write a SQL. Thanks

--
Message posted via http://www.yqcomputer.com/
 
 
 

Last Record

Post by Smarti » Sat, 10 Feb 2007 11:39:16


Have you tried the TOP predicate?

--
Smartin
 
 
 

Last Record

Post by Haggr via » Sat, 10 Feb 2007 11:43:08

meant to say "last record"



--
Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Last Record

Post by Haggr via » Sat, 10 Feb 2007 13:11:49

I need to find the "Last" record of a job "[JobNumber] using [FileNumber],
a "Auto Number" field, ("I think?") and all the other info, in that record,
at that time.

Ex. Sometime I need the last record for a "job" [JobNumber] where a
field [Code] is "2",
Or the last record for a "job" [JobNumber] where the field [Date]
=>Date()
.
All of these fields are in the same table.
I can do this in Query grid
design, but it take a few sub queries,sometime alot, to do it. Hoping there
is a better way.




--
Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Last Record

Post by SmVycnkgV2 » Sun, 11 Feb 2007 07:39:01

Show us a query that works close to what you want now.

Also remember that "last" has little meaning UNLESS you have something to
sort on in the table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.