Help with relationship!

[Phonesa]

Hi, I'm hoping some of you migh have some suggestions. I have 3 tables:

DefendantInfo: DefendantID, Defendant Name
Address: AddressID, DefendantID, and Address (each defendant can have more than one address)
Citations: CitationNo, DefendantID, AddressID

Currently I've got my Address table set up so that the primary key is DefendantID + AddressID (to make sure they can have many addresses, but the addresses should be unique). Each citation can only have one defendant and one address. I'm trying to set up a relationship between the Address and Citations table and having some problems. Citations should only be allowed to enter the AddressID associated with their DefendantID.

Am I going to have to change my table design?

Hope I've explained my problem clearly ....

Any pointers would be helpful.

Doh! Edited again. Reason number one why I shouldn't post at 7 in the morning but this has been nagging me all night.

 

[WayneRyan]

Phonesa,

DefendantInfo:
DefendantID,
DefendantName

DefendantAddress:
DefendantID,
Address ID

Addresses:
AddressID,
AddressFields (Street, City, etc.)

I don't exactly understand your question.

Wayne

 

[Phonesa]

Sorry! I hit submit reply too early.

 

[Pat Hartman]

Rearrange the sequence of the fields in Citations so that you can establish referential integrity between the Address table and Citations. In the Address table, include DefendantID as part of the primary key even though it is not necessary for uniqueness. That will allow you to create a relationship from Citations to Address using BOTH ID fields.

Citations: CitationNo, AddressID , DefendantID

 

[Phonesa]

Thanks for the response. Maybe I'm not quite doing what you suggested because I get this message, "No unique field found for the referenced field of the primary table" when I try to put AddressID and Defendant ID in the relationship window.

Woops, I just reread what I originally posted. Sorry. My primary key currently is already DefendantID + Address. I don't have AddressID as part of the primary key because the defendants will end up having more than one AddressID with the same address.

My data would look like this with all 3 as part of the primary key:

AddressID DefID Address
1 ABC Park Place
2 ABC Park Place

I want to set it up so that the users can't enter Park Place again for Defendant ABC.

I am doing something wrong aren't I?

 

[Ringers]

You should start by defining your buisness rules.

a Defedent can have many Addresses 1:M(pk must go in many table)

a citiation has one defedent 1:1(the pk can go in either table)

a citation has one address 1:1(the pk can go in either table)

if this is correct you can make a realtionship model around these rules. So i reckon if you put your PK from defendent and address in citiation. With a citiation_id as PK your model should work. You might not have to link defedent to address.

 

[Phonesa]

Not sure I quite understand. I've attached a sample of what my database looks like. If you look at the Citations table, you'll see that Minnie Mouse currently has someone else's AddressID. I don't want that to happen. AddressID on a citation is not a require field btw. I think what I'll probably have to do is do validate the AddressID on the form when users enter it. If someone can give me a list of the commands I might need, I can look those up


I also included the Alias table which will track all the aliases they might have because people don't always give the correct information when a citation is given to them. Initially I was just giong to allow the users to change the DefendantID if they found a matching defendant on two different citations, but I don't think I want people changing the primary key. If someone has a better idea of how I should I should implement this, it would be appreciated.

 

[AncientOne]

Does this supply a solution?

 

[Phonesa]

Afraid not becase the users can still enter duplicate addresses for one person. I can't make the AddressID part of the key because it is not a required field.

If you haven't figured it out yet, I'm sort of new at using Access so bear with me. My form currently displays the correct addresses associated with each citation. If the user clicks on my address combo box, it displays all the address currently stored in my DefendantAddress table so users will still be able to enter any AddressID they want.

I'd like to set it up so my combo box selects all the addresses associated for that defendant.

I've added
strSQL = "select Addressid, Address, City, State, Zip from DefendantAddress where DefendantID =" & Me.txtDefendantID
Me.cbAddress.RowSource = strSQL
to my Form_current and cbAddress_BeforeUpdate events and now my address shows up blank and when I am in the combo box, I get the error "Data type mismatch in criteria expression."

 

[AncientOne]

Phonesa

If your defendantID is a text field, you have to enclose it in quotes:


strSQL = "SELECT Addressid, Address, City, State, Zip FROM DefendantAddress WHERE DefendantID ='" & Me.txtDefendantID & "'"

after the equals sign you need a single quote, then a double quote.At the end, two double quotes enclosing a single quote.

 

[Phonesa]

Ahhhhhh. Thanks, it worked!