Finding Most recent records using query(please help!)

[logs22]

I hope someone out there can help me, here is my situation: I am making a databse to track client calls. Each client may be connected to multiple sales calls. I need a query that will scan through all of the records and pull the most recent call for each client. I tried using the group- max tool but this only worked if all the calls were homogenous. I need to first bring up all the most recent callls, then use criteria to sort through them. I have been aganonizing over this and have come up with some pretty complext solutions. There must be an easier way. Please Help!!!

 

[Jon K]

(I assume there is a date/time field that recorded when each call was made.)

Try this query (type/paste in the SQL View of a new query, using the correct table name and field names):-

SELECT *
FROM [TableName]
WHERE [DateTimeField] in (Select Max([DateTimeField]) from [TableName] as S where [Client]=[TableName].[Client])


(If there is no such a DateTimeField, you can use an AutoNumber field instead.)

 

[logs22]

Is there any way to accomplish this without SQL, I am not to familiar with it. I have tried the above suggestion and it has not been bringing up any records. It is also asking me for parameters which I am not looking for.

 

[Jon K]

Another way to do it is by using VBA code.


If you are asked for parameters when a query is run, it means Access can't find something stated in the query. Most likely it is caused by some inconsistencies in the spelling of field names.


I have attached a small DB. It contains two queries: one using the date/time field DateTimeOfCall, the other using an autonumber field RecordID. Both queries return the same three records for the three Clients in the table.

SELECT *
FROM [tblClientsCalls]
WHERE [DateTimeOfCall] In (Select Max([DateTimeOfCall]) from [tblClientsCalls] as S where [Client]=[tblClientsCalls].[Client])

SELECT *
FROM tblClientsCalls
WHERE [RecordID] In (Select Max([RecordID]) from [tblClientsCalls] as S where [Client]=[tblClientsCalls].[Client])


If you are familiar with the query grid, you can open the queries in Design View to see how they are displayed in the query grid.

Hope it helps.


(Note: The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.)

 

[logs22]

Does that code still work when the client name is not in the same table as the call date/time. The call date and time is in a table that contains all data regarding the call such as date, what was discussed, is client satisfied..etc.. the client name is in a table that has information about the client, ie. client name, business type, size, etc. There is also a table for the contact data. The client informat has a one to many relationship with the calls as well as the contacts. There can be multiple calls and contacts for each client. Not sure if any of this info is useful but i just can't seem to get anything working right. I really just need a query that searches through the last call. From there then i want to be able to screen for certain things like show all the clients that are satisfied. The problem is it is searching through all calls so if on a call 5 years ago the client was satisfied, even if they stated on the most recent call that they were not, the record is getting shown as a client that is satisfied. That is my dilemma. I would appreciate any assistance and could provide any more info that is needed. I think I am in a little over my head and i have to present this thing next week. The form and tables are perfect, it is the output reports and queries the feed them that are not working out for me. Thanks!!!!!

 

[Jon K]

So long as there is a Client ID field in the table containing the date/time field, the query works. You can then join the query with the client information table in a new query based on the Client ID fields and pull whatever client info that is needed.

 

[logs22]

John K Thank you!! Your code works perfectly and after two days of frustration I am finally getting somewhere.......

 

[dickohead]

Sorry to resurrect a dead thread, however...

I have the following set of data:

Exam1 Person1 Fail English 01/01/01
Exam2 Person1 Pass Maths 02/01/01
Exam3 Person1 Pass English 03/01/01
Exam4 Person2 Fail Maths 01/02/01
Exam5 Person2 Pass Maths 02/02/01

What I would like to generate is a "scorecard" for Person 1 that gives them their results, now at the moment person 1 is getting:

English Fail
English Pass
Maths Pass

So they are getting a result of both pass and fail for the same subject.

How do I get it to say "if there are two results for the same class, take the one with the later date" in an SQL statement?

I have been trying to adapt the above, but cannot get it to give me multiple records for Person 1, only the latest attempt.

 

[fibayne]

Hi Jon K....I had posted a similar thread and your solution here as worked a treat many many thanks, I too had sepnt a couple of days trying to get this one sorted cheers Fii

 

[Anchoress]

I came today to ask a very similar question, so I'll post it first here in this thread.

I am running Access2000 for a simple contact management DB with clients in the parent table and individual sales calls in the child.

I created a grouped multi-table query that showed the most recent contact date for each client, and it worked great, however when I tried to add more fields from the child table, the grouping disappeared and the query result showed all the records, not just the most recent.

Here are the fields in the query that currently works:

Salesperson (Parent table, text)
Company Name (Parent table, text)
Company Address (Parent table, text)
Notes (Parent table, memo)
MaxOfP/V Date (Child table, date)

Here are the fields I'd like to add and have failed to do so successfully:

P/V (Child, text)
By (person) (Child, text)
Purpose (Child, text)
Comp'd? (Child, y/n)
Ordered? (Child, currency)
Product gifted (Child, text)
F/U action (Child, text)
F/U date (Child, date)
Notes (Child, memo)

I've tried a few things around joining on various fields and fiddling with the joins, creating sub-queries based on these queries etc, but I'm fumbling in the dark. Any suggestions or directions to reading on the topic? Thanks in advance.

 

[Anchoress]

Anyone with any suggestions for me??

 

[choaspes]

Hello again!

You have all been so relentlessly kind and helpful whenever I've had an Access problem that I'm afraid you're stuck with me. Earlier on today I was having the same problem the first chap on this page had and it was quickly resolved by the advice he recieved:

(I assume there is a date/time field that recorded when each call was made.)

Try this query (type/paste in the SQL View of a new query, using the correct table name and field names):-

SELECT *
FROM [TableName]
WHERE [DateTimeField] in (Select Max([DateTimeField]) from [TableName] as S where [Client]=[TableName].[Client])

(If there is no such a DateTimeField, you can use an AutoNumber field instead.)

However, I now need to introduce one additional layer of complication, and I'm not proficient enough with SQL to deduce the solution. In the initial scenario we had "clients" and "calls" - and we wanted to return just the most recent call for each client. In my scenario each "call" can have one of several "outcomes", and I want to exclude results with one of those "outcomes".

Simply adding the outcome field to the query with a filtering criterion didn't work, as when a "call" with that "outcome" is the most recent record then the query returns no record for that client, rather than returning the most recent record with an outcome other than the one I want to exclude. I assume I need to embed the criteria somewhere within this bit of code:

"WHERE [DateTimeField] in (Select Max([DateTimeField]) from [TableName] as S where [Client]=[TableName].[Client])"

...but I know not how or where. Thanks in advance for any suggestions!

 

[choaspes]

I'm sorry to be bumping this thread, but I am lost without your guidance. I'd be very grateful if anyone can spare a minute or two to put me right.

Yours humbly.

 

[choaspes]

Last time I bump this I promise.

I am deperate, I am begging, imploring, on my knees. Please help me.