lookup query that can be used to update table

[Guest]

I have a table that contains client information used for marketing. I have a
second table which contains notes about calls made to each of the clients
that has date and note fields. The two tables are linked via a client
number. I would like to develop a query/form that I can search the table for
clients that have not been contacted or who has a note that is X days old.
Have this information come up in a form that I can then use to contact the
client and add notes back to the table.

I have been able to create a query and form that pull the selected
information from the table but I am not sure how have it allow me to enter
data and then update my table.

Thanks in advance for any help or suggestions you might have.

 

[Guest]

I'd suggest a main clients form with a notes subform. The main form would be
based on a modified version of your query so that it returns only one row per
client. This can be done by returning only columns from the Clients table in
the query, using only the notes table to determine which rows to return If
your query joins the Clients and Notes tables uncheck the 'show' checkbox for
the fields from the Notes table in query design view and set the query's
Unique Values property to Yes in its properties sheet; the latter is the
equivalent of SELECT DISTINCT in SQL view.

Another approach would be to use subqueries. In this example the query
would return clients who have not been contacted or who have not been
contacted for 3 weeks:

SELECT *
FROM Clients
WHERE NOT EXISTS
(SELECT *
FROM Notes
WHERE Notes.ClientNum = Clients.ClientNum)
OR DATE() –
(SELECT MAX(ContactDate)
FROM Notes
WHERE Notes.ClientNum = Clients.ClientNum) > 21
ORDER BY ClientName;

In the Clients form include a subform based in the Notes table and linked on
the ClientNum fields. You can then enter or edit notes in the subform for
the parent form's current client as necessary.

Ken Sheridan
Stafford, England

 

[Guest]

Ken,

That worked great. I had to paly with my query a little but it is doing
exactly what I wanted.

Thanks.