Query in a Form

[Guest]

I have a client table linked to a call log table.

The goal is for the Form of each record to display the last contact date
with the client.

I'm trying to do this through a Query. However, I'm stuggling with what
criteria to use for it to apply to each record. (instead of using a
particular client ID)

Is there a universal criteria I can use so that every record's Form will
Query the last contact date for only that particular client.

thanks

 

[a.t.brooks]

Hi Austin
Create a query of the call log including the date
Apply a sort descending criteria on the date.
Go into SQL view of the query and past in "SELECT TOP 1" at the
beginning to give only the first record (i.e. last call logged).
You can then create a form based on this containing the date and then
drag and drop this form into your main form. Call it something like
last_date when you save it.
Finally right click the linking field on the main form select
properties. On the Event/After Update section, select [Event
Proceedure] from the list and click the browse button on the right.
This should open the VBA editor.
Write "Me.last_date.Requery"
Save and exit
Bobs your uncle. You may need to play around with the formatting to
make it look nice.
Tony

 

[Guest]

Hi

Assuming the the call log has the client ID field somewhere you can have a
subform linked (based on a query from the call log table) via the client ID
if the call log table has a time/date field. This will show the last call
to/from this client if the query has something like

Main form = cleint table
Subform = Call log

Good Luck

 

[Guest]

If you merely wish to show the last contact date per client and no other data
from the call log table you can do so with an unbound text box on a form
bound to the Clients table, calling the DMax function as its ControlSource
property, e.g.

=DMax("[Call Date]", "[Call Log]", "[ClientID] = " & [ClientID])

Ken Sheridan
Stafford, England

 

[Guest]

thank you very much for all your help. I finally was able to figure it out
and move on.