2010 need help in relationships

[PattyS]

these are the tables ans fields I have set up so far
this is a database for a PD Legal Dept


CaseNo (this is number is issued to each case from the PD, it is unique)00-000000 format text
CourtDocNO ( additional number identify with caseno)
DateReceived (date)
TimeReceived (time need separate from date)
TypeOfProcess (text)
NatureOfDoc (text)
SourceOfDoc (text)

AssignedOff_FirstName (text)
AssignedOff_LastName (text)
DateAssigned (date)
DateProofServiceDue (date)
DateProofServiceReturned (date)

PlaintiffFirstName (name of the plaintiff in this case)
PlaintiffLastName
DefendFirstName (defendant in this case)
DefendLastName
*there may be multipal plaintiffs and defendants in any one case


So I separated these into groups for Tables
But I need identifiers in the group of Assignments
AssignID maybe and autonumber and PK ?
but do I need to add CaseNo as in the first table/group?

and tblPersons for name of the third table
PersonID as PK and autonumber?
but do I need CaseNo added from the first table/group?

I am not a professional programmer and do not use "database" terminology
just want to create a normalized database but am confused
I have read many posts, tutorials, and books but when it comes down to actually applying the knowledge I draw a blank

Hope you can help and if possible, a step by step instruction

 

[Beetle]

We can probably provide more help if you can give us a more detailed description of how the different entities you're working with (Cases are an entity, Persons are an entity, etc.) relate to one another. For example, can a Case be assigned to more than one Person (either at the same time or over a period of time)? What about Police Officers and/or Prosecutors/Legal Counsel? Do you need to track information about them and if so how do they relate to your other entities?

 

[PattyS]

the caseno is unique and never repeated
although an officer might be assigned to more than one case
the plaintiffs and defendants would be unique to one case although...
a plaintiff or defendant might have another case with a different caseno
and the legal office wants at least 10 places where they can add more than
one plaintiff or defendant
hope this helps

 

[Beetle]

although an officer might be assigned to more than one case

Can a case be assigned to more than one officer?

 

[PattyS]

yes, that is what I meant
an officer might be assigned to more that one case but only one caseno per officer
and I understand that I will probably have to add fields to each group to make a
primary key and again the confusion, should these be auto number fields?
tblPerson
PersonID which is a auto number? and PK? to relate back to tblCase and CaseNo (PK)? or should I create a a new field CaseID as a auto number AND
PK ?

 

[Beetle]

OK, I still don't have a clear enough picture of your model to give you specifics, but it would appear that you have at least one or two many-to-many relationships. To handle this type of relationship you need a junction table. You can find discussion about that here and here. The second thread also has a small sample db you can download so you can see the basics of how something like this would be done.

As far as PK fields, yes each table should have a PK field. I use Autonumber in most cases. The PK field is not something the user should ever see or interact with, it is only there to uniquely identify each record and establish relationships.