brain melting on design and relationship

[Tall Mary]

Help please. 10 days ago I thought I was familar enough with Access with the
help of a nice Core Technology book to make a Client database for work. Now
my brain is melting and I am going in circles.
I have to make a database that will allow search by Client Name or Case # or
Discussion # and be able to view 1 Client and all related Cases and the
Cases only have 1 Discussion but may have multiple Cases.
Also want to see on a form the 1 Client's multiple Case#s in a List Control.

I made:
a Clients Table w/ PK as ClientID#
a Case Table w/ PK as Case#ID and foreign key ClientID# (so related to
Clients)
a Discussion Table w/ PK as DisID#and 2 foreign keys, ClientID# and CaseID#
(so I call see the One Client, Many Cases to the One Discussion).

Am I going in the right direction ? Please use baby Access language as I
now believe I know nothing about Access. Thank you.

 

[Bill Edwards]

Explain "and the Cases only have 1 discussion but may have multiple Cases."
????

Do you mean a case has one and only one discussion associated with it?
A single discussion can belong to multiple cases?
A case can have multiple other cases associated with it?

 

[KARL DEWEY]

If I have digested your information correctly I do not see a need for the
separate Discussion table. I get that there is only one discussion per case
and each case is related to only one client. If so, then make the
Discussion a memo field in the Case table. That is unless you want to record
and search on who had the discussion and when. In this case use a third
table with CaseID, EmpID, DiscussDate, Remarks. Create a compound key of
CaseID, EmpID, and DiscussDate fields and set to unique. The DiscussDate set
default to =Now().

You will have a one-to-many relation from client to case. Also you will
have a one-to-many relation from case to discussion.

Use form with subform and subform - Client - Case - Discussion.

 

[Tall Mary]

One Client can have many multiple Cases but there will only be 1 Discussion
for all the Cases.
Thanks for trying to help.

-----------------

 

[Tall Mary]

Not quite right. 1 Client may have many Cases but there will only be 1
Discussion for all the Cases. Does that make more sense ? Thank you for
trying to help.

 

[KARL DEWEY]

Ok.... Sounds like one discussion per client - then add memo field to client
table.

Unless you you want to record and search on who had the discussion and when.
In this case use a third table with ClientID, EmpID, DiscussDate, Remarks.
Create a compound key of ClientID, EmpID, and DiscussDate fields and set to
unique. The DiscussDate set default to =Now().

 

[Tall Mary]

OK, I will try that. I will let you know if it works. Sure hope so, I am
getting worn out trying to figure on this database.

 

[Bill Edwards]

So one client will only have one discussion:

Depending on the specific information in a discussion:

Have a tblClient and a tblCase. tblClient will have a field of appropriate
data type (memo ?) called Discussion

or

Have a tblClient, tblCase, tblDiscussion as follows
tblClient
ClientId PK

tblCase
CaseId PK
ClientId FK

tblDiscussion
DiscussionID PK
ClientID FK -> one to one relationship with tblClient

 

[Craig Hornish]

Hi,
I have some time to kill Friday if you would like to send me a copy of
your database I can check it out. Please zip, and include test data as it
is easier to show by example. (change information if needed)
It shouldn't really take much time to get you an example back - send to
(chornish at cap-associates dot com).
--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

 

[Tall Mary]

YES, that works fine now. So simply but when I have only worked with a few
DBs I just couldn't see the little problem in the design of this one. Plus so
you don't think I am a total airhead, I scan files while trying to create
this DB. So not much time inbetween scans to read a book or get help online.
Thank you.

 

[Tall Mary]

Sure that would be awesome.
Would you be able to help me create a search on the textboxes for Client's
Last Name, AVS # and Diagnosis.
I need to be able to search for either of those 3 things and have all the
fields come up in a form (Like my Clients Form is right now) especially
showing all the AVS # that are associated with a Client.

I would appreciate any help.