Showing multiple interactions for multiple contacts from a single company

[deekay]

Showing multiple interactions for multiple contacts from a single
company

I am trying to create a form that will show all the interactions with
all contacts for a particular company.

The database structure is like the following:
A company table that stores company specific information
A contacts table with all contacts details
An activities table with all recorded interactions with specific
contacts.

And they are related like 1 company to many contacts and 1 contact
with many activities.

Each activity logged has a specific log date, activity description,
user that logged etc.

At a single glance I would like a form that would show the people with
whom we have had the last few activities with at a company.

Something like:

Company
Contact1 | Contact2 | Contact3
Activity1 | |
Activity2 | Activity1 | Activity1
Activity3 | Activity2 |
Activity4 |


Where:
- Contact1 had the latest activity, contact2 the next latest etc...
- Also Activity1 to Activity5 under Contact1 is such that Activity1 is
the newest, Activity4 is the oldest.
-Every Activity in the same row occurs on the same date so Activity2
for Contact1 and Activity1 for Contact2 both occurred on the same 'log
date' but there was only Activity1 for Contact1 on that date (so there
should be nothing/blankrow under all the other contacts).

Does anyone have any idea how I could create this interface, please
your help would be really appreciated with as much detail as possible.

 

[Guest]

To simplify, I've left the company ID out of this; your initial query would
specify the company. So I have an Activities table that has fields Contact,
Activity and ActivityDate. (Yours will have Company and an index field, at
the least.)

Create a select query called "Recent 7 activities":

SELECT TOP 7 Activities.Contact, Activities.Activity, Activities.Date
FROM Activities
ORDER BY Activities.Date DESC;

Now you want a Crosstab query where the row labels are the dates in
descending order, the column labels are the various contacts, and the
"Totals" (really just the plain values in the table) are First (as opposed to
sum, count, average, etc.). This is easily done using the Crosstab wizard.
After the query is created, you go to design view and set the Sort order on
the Date column to Descending, and remove the extraneous "Totals" column.
The resulting SQL, for checking purposes, is:

TRANSFORM Last([Recent 7 Activities].Activity) AS LastOfActivity
SELECT [Recent 7 Activities].Date
FROM [Recent 7 Activities]
GROUP BY [Recent 7 Activities].Date
ORDER BY [Recent 7 Activities].Date DESC
PIVOT [Recent 7 Activities].Contact;

Yikes! This is one place where wizards are a good thing.

Below is a sample result for 7 most recent results from fake data. The
format here is a mess, but in Access it looks right.

Date Abner Bev Chuck
02/11/2007 Data request
02/07/2007 Data request Sales call
02/04/2007 Quote
01/27/2007 Quote Tech query
01/19/2007 Tech query

You can present this query result on a form as you wish; perhaps you would
use a continuous form wherein each version has a different Company. I don't
know how you would get the filtering by Company done, but feel sure you can.

Good luck.

Jim Beard