Case Management Cross-Refence question

[Fuzzyluzzi]

I'm designing a Court Case Management database. I am currently using a many-many relationship for CaseID and ClientID. What I would like to do is similar to IMDB's Credited With option. For a conflict checker, I would like to see if any clients have been in any cases with any other clients before. My brain can't seem to figure out how to word the query.

Any help would be appreciated

[CJ_London]

Try using the query wizard to create a find duplicates query

[orange]

How about something like this, based on

to see if any clients have been in any cases with any other clients before.

There can be Many Clients
There can be many Cases
A Case can involve 1 or Many Clients
A Client can be involved in 1 or Many Cases

Basic model

Client --->ClientInvolvedInCase<---Case

Where

Client
ClientID PK
ClientFirstName
ClientLastName
...other client info

Case
CaseId PK
CaseName
CaseDescription
CaseDate
..other Case specific info

ClientInvolvedInCase (junction table)ClientCaseID PK
ClientId FK to Client table
CaseId FK to Case table
..other info specific to this Client and this Case

Note:

There are 2 approaches to the junction table.

1- as I have shown. A separate, single Primary Key (autonumber).
Foreign key of ClientID and foreign key of CaseId. ClientId and CaseID would be a unique composite index in this table. The purpose of the index is to prevent duplicates.

2 - also possible, but not my choice
Make a compound primary key of ClientID and CaseID. You would not need the composite index to prevent duplicates as that would be handled by the PK.

Also depending on your analysis, CaseDate may be part of your composite unique Index or Primary Key also depending on which approach you take.

There are lots of youtube video tutorials and articles related to table design; junction tables and the concepts and rationale for each.

Here's a tutorial I think you would benefit from but you have to work through it. It does include a solution.

Good luck.

[Fuzzyluzzi]

I already have the Database set up. The many to many relationships are working fine. I have a form for the cases that shows all the clients in it, and a form with the clients with all the cases they are in.

I'm not just looking for Duplicate entries, as it is expected that Clients can be in multiple cases. And I already have it programmed to give an error if someone tries to input a duplicate client name.

What I am trying to do is create a query that shows when two clients are in two or more cases together.
I was thinking of an Outer Join of CaseID on the Joining Table, but haven't gotten it to work to get the results I'm looking for.

Realized too late I posted this in the wrong sub-forum. Should have put it in Queries, but don't want to double post. If a mod could move it please.

[orange]

Your post is fine where it is in my view.

Why not attach a copy of your db - just enough records to show the issue? That way we can see exactly what you are dealing with, your data structures , your query(s) and some data in context.

[Fuzzyluzzi]

I found the original on AADConsulting.com's website. I've modified it to suit my needs. I was able to make a duplicates query easily enough to show me those that are in multiple cases. But what I'm looking for is those that in are multiple cases with another Contact. From what I've been googling, I think it is similar to an Intersect. I just don't know how to manipulate it into either a query to be used on forms or a report.

Contacts	Teams	Cases
ContactID	ContactID
	CaseID	CaseID

Here is my DataBase.
and the Original.

Thanks.

[June7]

As far as I can see, an INTERSECT would just be an INNER JOIN of two tables. http://www.sqlguides.com/sql_intersect.php

I don't think this will help in your situation. I think what you want may not be possible with query alone and will need VBA.

How do you want the output to look?

ClientA
Case1 ClientB ClientC
Case2 ClientC ClientD

ClientB
Case1 ClientA ClientC

ClientC
Case1 ClientA ClientB
Case2 ClientA ClientD

ClientD
Case2 ClientA ClientC

Should ClientB and ClientD be excluded because each in only one case?

ClientA
Case1 ClientC
Case2 ClientC

ClientC
Case1 ClientA
Case2 ClientA

[Fuzzyluzzi]

removing the ones with only 1 case would be ideal, but you got the idea perfectly. even if I had it as a subform of the contact listing all the other contacts they are associated with through cases would work too.

I've been playing with creating a subform that had all of their cases, then a subform of that that showed everyone, but it was plagued with only showing one case at a time. I couldn't get it to show all the cases in one data sheet, which would make it easy to spot duplicates.

[orange]

If your tables are designed and your test data applicable to your set up, then you should be able to get the data you need from a query before designing forms/subforms etc.
I'm not clear on exactly what you are trying to retrieve nor where Teams and Contacts have entered your post. It is always better to work from a clear complete description of your issue/opportunity without any Access/database jargon. Making up some analogy or example "to simplify things" rarely works - and masks a lack of clear definition/understanding of the issue/opportunity.

I did give you a draft design and related info based on your original post.

Your data is also suspect, in my view, given that in Case 2, Barnwell, Siva is identified twice (Defendant and Attorney) -I don't think this is realistic in a specific case--but you know your situation better that readers do.

You have a Role value (Lead2) in Case 1 that does not exist in your Role table??

I would not use Lookup fields at the table field level as your design does.

Start with a clear, complete statement of the issue/opportunity; develop a data model; use sample data to test/vet your model; adjust as necessary; then build your database from your latest model/blueprint.

[Fuzzyluzzi]

I would not use Lookup fields at the table field level as your design does.

I didn't design the original. But thank you for finding this. I'm going to adjust it to normal.

[Fuzzyluzzi]

After going back into this. I believe I can get the required results if I can just make it so that I can see all the Contacts that are in any Case with the highlighted Contact.

[June7]

So maybe like:

SELECT * FROM Cases WHERE CaseID IN (SELECT CaseID FROM Cases WHERE ContactID = [enter contact ID here]);