Returning the last of a number of entries

[thehamlet]

I have a table that keeps track of the scheduled site visit date and attending engineer.

Schedule ref(auto number and unique key)
Job ref
Schedule date
Attending eng
Reschedule reason

There will be multiple entries for some jobs when the visit has been rescheduled. I need to run a query that returns the "last" entry for each job (the one with the highest shedule ref number)

Can anyone help?

Regards,

Eric

 

[Abby N]

Assuming the [Job ref] field is a number field and indicates the group from which you want to get the latest date, this criteria on your [Schedule date] field should do it.

DMax("[Scedule date]","YourTableName","[Job ref] = " & [Job ref])

If my assumptions are incorrect please correct me and we'll work out a new criteria. Good luck.

~Abby

 

[thehamlet]

Thanks for that Abby, it worked "sort of".
But only gave me the very last entry in the table. Perhaps I should expand, here are the fields/data types:

Table name = Schedule Date

Schedule ref -(auto number and primary key)
Job ref - (text)
Schedule date - (date/time)
Attending eng - (text)
Reschedule reason - (text)

The last schedule date may be before the previous ones as we may bring a job forward, so the only identifier will be the "schedule ref" field which should be greater than the previous ones.

When I run the query I want it to show all the records except those with duplicates (rescheduled jobs with the same "job ref") where I want the most recent entry ( the one with the greatest "schedule ref" field).

Does this make sense?

Eric

[This message has been edited by thehamlet (edited 08-21-2001).]

 

[Abby N]

Opps! It makes perfect sense. I missread what field you wanted the max of in your original post. This criteria in the [Scedule ref] field should do it.

DMax("[Scedule ref]","Schedule Date","[Job ref] = " & [Job ref])

Sorry about that.

~Abby

 

[thehamlet]

Abby

Got an error first with

DMax("[Schedule Ref]","Schedule Date","[Job ref] = " & [Job ref])

saying there was a missing operator in query expression. If I insert "'s around the last [job ref] I get a result but it still is the last entry in the table

Getting there

Eric

 

[The_Doc_Man]

This is not as simple a problem as it sounds. I tried to do an interrogative logic analysis of the required query to answer the question, but it involved at least two queries. Finding the "last" item is actually easy - do an "ORDER BY X DESCENDING" and then select the first entry. But that doesn't actually help that much.

The easiest way I can think of to handle this is to add a status code to each event record, where possible code values signify: Completed, Scheduled, Rescheduled, Cancelled, SNAFU'd, etc.

If you do this, then to reschedule a job, you just flag the scheduled job as rescheduled and make a new entry for the (newly) rescheduled job. Not only that, but you can copy several elements of the old record over to your new record.

Then your query is simply to find all jobs where code = Scheduled. And THAT is a totally easy query.

Not to mention that this gives you all sorts of historical info about who most often needs rescheduling, who closes jobs most often, and stuff like that.

 

[Abby N]

Opps again! <<5.73 MB of vulgarities deleted>> This is what I get for cutting back on caffeine. Here you go. I assure you this will work. (The old one would have if [Job ref] were a number instead of text.)

DMax("[Schedule ref]","Schedule Date","[Job ref] = '" & [Job ref] & "'")

Alternatively, you could create a summation query with just 2 fields, [Schedule ref] and [Job ref]. Total [Schedule ref] as 'Max' and [Job ref] as 'Group by'. This will give you a list of the highest [Schedule ref] for each [Job ref]. Then go back to your query and add the summation query like you would any other table or query. Join [Schedule ref] from your main table to [MaxOfSchedule ref] in the newly added query. Your query will now only return records where the joined fields are equal, which is just what you want. Hope this helps.

As to Doc's interrogative logic analysis, I'd like to point out that this does contain 2 queries. Eric's original query and the DMax function. Regarding the addition of the new field, it would definitely make the query simpler. But, will also mean a change in your reports, forms, and the way in which your users work with the database. It also increases your reliance on users to keep that information up to date. So, it's a trade off. Good luck!

~Abby

[This message has been edited by Abby N (edited 08-21-2001).]

 

[thehamlet]

Thanks for the help. I've decided to approach this in another way. The schedule table is a small part of a much bigger DB. I already have a table holding all my other job details so will add a field referencing the schedule tables last entry and update as reschedules take place. This should allow me to track the latest schedule info.

Eric


Disregard the above. I was typing it as you sent the last post Abby.
The last query did the job, many thanks you just saved me updating 2,000+ records

[This message has been edited by thehamlet (edited 08-21-2001).]