Design Problem

[rodgeraj]

Here's a quick overview of where I'm at and where I need to be. I've got data that I need to cross reference and I just can't seem to come up with an easy way to do it...

This is my relationships structure:
[Edited: See first image below]

What I want to do is have my activities table tied with my contacts, but not all activities will require a contact. The connection I'm looking to make is to be set up the contact that first reported the issue so that we can track who is sending us "New Issues", but I get all the contacts listed on an issue.

[Edited: See second image below]

What do I need to create or what kind of connection do I need to make so that when there is a "New Issue" I can capture the contact of the new issue? I'd also like to be able to capture other instances where a contact might be involved, but not all of the actions will require a contact to me listed... and I'd really like to avoid creating a contacts field in my activity table because I'd prefer to not have blank entries and not all actions will require a contact and not all actions will be limited to just one contact on an issue either.

Thanks.

[Lysander]

Not 100% sure of your structure, but I'm assuming every issue has at least one activity. If you made a query linking issues to activies and filtered it just to 'New Issue' you could save this query as say qryNewIssue.

You could then link this query to Contacts with an outer join that would show all New Issues, with the activity, and any contact associated with this new issue, including those new issues with no contacts

[rodgeraj]

Not 100% sure of your structure, but I'm assuming every issue has at least one activity. If you made a query linking issues to activies and filtered it just to 'New Issue' you could save this query as say qryNewIssue.

You could then link this query to Contacts with an outer join that would show all New Issues, with the activity, and any contact associated with this new issue, including those new issues with no contacts

At this point, I'm not capturing when the provider is the original contact on a New Issue. I'm only capturing what providers are associated with an issue. I guess, what I'd like to do is have a way to capture the name of the contact when the IssueType = "New Issue".

So, as part of a form, if the tbl_Activity_Type.actype_desc = "New Issue", the form would ask for tbl_Contacts.cntct_Name.

I guess what I'd like to do is set up a table that has three fields.

Issue; Contact, and Activity Type...

And the Action Taken form would only add records to this table if the Actype was in a certain set of actions that would require the contact information. I can't think of a better way to normalize it and I'm not really familiar with Outer Joins.

Thanks.

[Scott Price]

At this point, I'm not capturing when the provider is the original contact on a New Issue. I'm only capturing what providers are associated with an issue. I guess, what I'd like to do is have a way to capture the name of the contact when the IssueType = "New Issue".

So, as part of a form, if the tbl_Activity_Type.actype_desc = "New Issue", the form would ask for tbl_Contacts.cntct_Name.

I guess what I'd like to do is set up a table that has three fields.

Issue; Contact, and Activity Type...

And the Action Taken form would only add records to this table if the Actype was in a certain set of actions that would require the contact information. I can't think of a better way to normalize it and I'm not really familiar with Outer Joins.

Thanks.

I would probably explore something like this: Add a field named JoinTypeID to your tbl_Join_CI, linked to a table giving the types of Joins: Initiated By, Handled By, Managed By, Closed By etc, etc...

Your select query then will be quite easy to write, something like this:

SELECT tbl_Contacts.cntctID, tblIssues.IssueID, tblJoin_CI.JoinTypeID FROM tbl_Contacts, tblIssues, tblJoin_CI  WHERE tblJoin_CI.JoinTypeID = 1

(or whatever the id is for the join type Initiated By)

Regards,
Scott