Trying to retrieve by last modified date

[blizkij]

I have a database that has a reference number (job number) and another table that has the activity log for that job. Is there a way i can run a query where it will only extract the latest activity log entry for that job instead of the entire activity history?

thanks

 

[LonnieJohnson]

Are they in there by date? Do you have an autonumber, or unique key? You could use the Last aggregate function if you are sure that the last record was the last one entered.

Just a couple of suggestions. ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[blizkij]

It is by date and with a unique key. I'll try what you suggested. thanks very much for your help

 

[blizkij]

I tried as you suggested and it returned the most recent record, however it was only for one job. I probably didn't explain the problem properly the first time so i'll try again:

I have a table with about 30,000 job numbers. Another table holds the activity logs for each job. On average, there are about 5 or 6 activity logs for each job number. What I'd like to do would be pull out the latest activity log for every job number. Does this make sense?

 

[tlbroadbent]

Break the requirement down to component parts. First, you must identify the latest activity date for each job. The following query will do that. Change names to match your table and columns.

Select JobNumber, Max(ActivityDate) As MaxDate
From ActivityLog
Group By JobNumber;

Now you want to get job information to go with that date.

Select j.*, a.MaxDate
From JobsTable As j Inner Join
(Select JobNumber, Max(ActivityDate) As MaxDate
From ActivityLog
Group By JobNumber) As a
On j.JobNumber=a.JobNumber

If you also need additional information from the ActivityLog, you could do something like the following query.

Select
j.JobNumber, j.Col2, j.Col3, ..., j.ColN,
a.Col2, a.Col3, ..., a.ColN
From JobsTable As j Inner Join ActivityLog As a
On j.JobNumber=a.JobNumber
Where j.ActivityDate = (Select Max(ActivityDate) From ActivityLog Where JobNumber=j.JobNumber)

Hopefully, you'll be able to use these examples to get started. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[blizkij]

Hi,

Thanks for your suggestion. I tried the MAX function, however this still produced the full activity log for each job. I've attached the sql that i'm working with. What I need to pull out is the last_mod_dt from the ahd_act_log table.

SELECT tbl_outstanding_problems_work.Name, tbl_outstanding_problems_work.Open, tbl_outstanding_problems_work.group_name, tbl_outstanding_problems_work.ref_num, tbl_outstanding_problems_work.full_name, tbl_outstanding_problems_work.status_full_name, cvrtfromunixtime([slaclock]) AS sla, tbl_outstanding_problems_work.time_spent, tbl_outstanding_problems_work.over_two, tbl_outstanding_problems_work.over_three, tbl_outstanding_problems_work.over_four, tbl_outstanding_problems_work.over_five, tbl_outstanding_problems_work.over_ten, tbl_outstanding_problems_work.over_fifteen, tbl_outstanding_problems_work.over_twenty, tbl_outstanding_problems_work.over_twentyfive, tbl_outstanding_problems_work.over_thirty, tbl_outstanding_problems_work.days_overdue, tbl_outstanding_problems_work.sym, tbl_outstanding_problems_work.Tier, tbl_outstanding_problems_work.Level, AHD_act_log.last_mod_dt, AHD_act_log.description
FROM AHD_act_log INNER JOIN tbl_outstanding_problems_work ON AHD_act_log.call_req_id = tbl_outstanding_problems_work.persid
WHERE (((tbl_outstanding_problems_work.over_two)<>0));


I don't know if you'll be able to help me without me going into more detail. thanks for your suggestions so far

 

[tlbroadbent]

If I understand you correctly, the following should work. It is a variation of the 3rd example I posted previously.

SELECT
p.Name, p.Open, p.group_name, p.ref_num, p.full_name,
p.status_full_name, cvrtfromunixtime([slaclock]) AS sla,
p.time_spent, p.over_two, p.over_three, p.over_four,
p.over_five, p.over_ten, p.over_fifteen, p.over_twenty,
p.over_twentyfive, p.over_thirty, p.days_overdue,
p.sym, p.Tier, p.Level, a.last_mod_dt, a.description
FROM AHD_act_log As a
INNER JOIN tbl_outstanding_problems_work As p
ON a.call_req_id = p.persid
WHERE p.over_two<>0
AND a.last_mod_dt=(Select max(last_mod_dt) From AHD_act_log Where call_req_id=p.persid);

NOTE: I used aliases to shorten the query and aid readability. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.