Creating Query from many to many relationship

[Guest]

I have three tables realated through a many to many relationship and I am
having trouble creating a query. This is what I have

Table 1
Victim ID(PK)
Victim Last
Victim First
Case Number
Begin Date
End Date

Table 2
PerpID(PK)
Perp Last
Perp First

Table 3
Defendant ID(PK)
Defendant

Junction
VictimID(PK)(FK)
PerpID(PK)(FK)
DefandantID(PK)(FK)

I want to create a search that when entering a victim last name, the search
will return all the info from table one plus the Perp first and last from
Table 2 and the Defandant from Table 3. Any help would be appreciated.

 

[Tom Ellison]

Dear AL:

What is concerning me is that you say:

the Perp first and last from Table 2 and the Defandant from Table 3

When you say "the" do you mean the one and only? As in, for every row of
Table 1 there is at most one row in Table 2 and one row in Table 3? Or are
there several.

In any case, a JOIN is what you need in the query.

SELECT *
FROM [Table 1] T1
(INNER JOIN Junction J
ON J.[Victim ID] = T1.[Victim ID]
(INNER JOIN [Table 2] T2
ON T2.PerpID = J.PerpID
(INNER JOIN [Table 3] T3
ON T3.[Defendant ID] = J.[Defendant ID])))

Now, your table structure makes me wish I lived near you. Apparently, no
one is ever the victim of more than one crime in a lifetime. Thus, you can
put both Victim and Case in Table 1.

Sorry, a bit sarcastic. What I really hope I did is not to anger you, but
to get you to think about this. If a victim is the victim of 2 crimes, he
will have two rows in Table 1. Is this a table of victims, or a table of
cases? What if there are 2 victims for one case?

The point would be, have you considered the need to have a separate table
for cases and for victims? There would be a junction table between these
two as well. A victim could be the victim in more than one case. A case
could have more than one victim.

If you later search for all the cases where Jon Smith is a victim, you will
miss the ones where Jonathan Smith is the victim. Could be the same guy,
different spelling. Maybe that's not important. Perhaps you'll never need
it. But if Victim is not an entity, why track it at all?

Or is there already a separate Case table? I wouldn't think so, becasue
Begin Date and End Date would seem to be attributes of a Case, not of a
victim.

So, perhaps you can see why I become more and more confused as I study your
current table structure.

I hope this helps.

Tom Ellison