'Last' function

[cyd00]

I am a novice so I will try to explain this the best I can. Please forgive me if I use "database language" incorrectly.

I have set up a table that tracks calls for our sales staff. For example when a sales person calls a potential client they click on a button in a form (related to a table called "contacts") that takes them to another form (related to a table called "calls") where they can enter the date and subject of the call and notes regarding the conversation.

This "calls" table is related to the main table "contacts", the key control is an autonumber assignment.

The problems I am having are:

I would like to add a field to the "contacts" table form that will show the last call date, so the sales staff wont have to go into the calls table to see when the prospect was last contacted.

I would also like to be able to run a query that will, amongst other information, pull the last call date.

I know there is a "Last" function, I'm just not sure how to use it.

I have already set up the query at this point but when I run it, it pulls up multiple entries for the same prospect (1 for each call entry).

Can someone help?

Thanks!

Cyd

 

[Pat Hartman]

You want the Max() function rather than Last(). Max() refers to magnitude and Last() refers to record order.

Select ContactID, Max(CallDate) As LastCall
From YourCalls
Group by ContactID;

It is not necessary to store the last call date in the contacts table. You can also use DMax() to obtain the LastCall if you need a function instead of a query.

 

[cyd00]

Thank you the DMax function worked wonders for the "Last Call" field on the form.

I am still having trouble with the query, however. I have multiple queries that can be run by the sales people. The queries will usually prompt for some sort of information.

For example: I have created a zip code query that will prompt the user to enter a zip code and then will return all contacts with that zip code. This works perfectly fine but I cant seem to get the last call date to appear in the query. I tried to use the "select" criteria you suggested but I received an error that stated it could only return one record and that I should modify it.

In the Zip Code Query I have many fields from the "Contacts" table. I added the "Calls" table and added the "CallDate" field to the query. I hit the subtotals button, set the total to 'where'. In the criteria section I added the following:

(Select ContactID, Max(CallDate) As LastCall From Calls
Group by ContactID)

When I try to run it this is the error message I receive:

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

What am I doing wrong??