Relationship question

[Stingfish]

Im "attemping to create a DB that records data on case that we investigate at work on fraudulent application.

The person who is being investigated may have many people that assisted the client in making the application. Therefore addresses and names need to recorded for that case.

The idea of the DB is to be able to do searches by address, fileno or name/s which will then show all records that match or are related to a search . This helps with cases to see if theres any relationship or link with past cases. So if someone does a search by Address, the search should spit out all records that have ever been link to that address.

I have made a start on the DB but I think Ive hit a snag. Im not sure if I have my relationships set up correctly to handle my searches. I have creates similar searches before and they worked well but this DB structure seems a bit more difficult.

 

[Dreamboat]

The only thing I see that I don't like is that you've got the court outcome ID in the tblCaseDetails and you shouldn't. Put it in the tblCourtDates. After all, there *could* be more than one outcome per case, yes?

And Outcome Result in the tblCourtDates would be the same field, wouldn't it? You only need it in the one table.

 

[Stingfish]

Thanks for the tip. I'll take it onboard.

The only thing I see that I don't like is that you've got the court outcome ID in the tblCaseDetails and you shouldn't. Put it in the tblCourtDates. After all, there *could* be more than one outcome per case, yes?

And Outcome Result in the tblCourtDates would be the same field, wouldn't it? You only need it in the one table.

 

[Dreamboat]

Ouch! Sorry!

My "could" should be "couldn't" and my "yes?" should be "right?"

 

[Stingfish]

Ouch! Sorry!

My "could" should be "couldn't" and my "yes?" should be "right?"

.

I knew what you meant dude!

Just one more question. Would u use FileNo in the client table to link it to case details table using the client File No or would it be better to user the Autonumber as the primary key. We consider fileNo important in our organisation. I use to using auto's as the primary key.

 

[Dreamboat]

Personally, I would always use the autonumber as the primary key. Others might feel differently. And, you can call me dudette.

 

[Stingfish]

Im a autonumber primary key person as well and I just need some feed back before went any further with the DB.

thanks