Presenting a many-to-one relationship in forms

[I Need Help]

Hi all,
I am creating a database for a claims management company. They want a system where one customer can have multiple claims assigned to them. So as far as i know this means i need one table for the customer and all their details (tblCustomer), and another for the case (tblCase). I need tblCase and tblCustomer to have a many to one relationship....with referential integrity?
So this all makes good sense.

then we come to the forms..

Ideally i would like to search to find a customer then have a very brief list of the claims they've had. with each claim being clickable for full info/ editing.
Don't know where to start with this bit.:banghead:
Would very much appreciate any help

Thanks for reading
Sam

 

[pbaldy]

Typically a form bound to the one table and a subform bound to the many table. Master/child links keep them in sync with each other. This will help with the clickable bit:

http://www.baldyweb.com/wherecondition.htm

 

[jdraw]

I'm not following how you were discussing Claims and then named a table --tblCase.

Did I miss something or did you change subjects.

If you have a Customer and a customer can have 0,1, or Many Claims, then I would suggest 2 tables

Customer--->Claims in a 1 to Many relationship

In Access this is often the base for a Form/Subform set up.

When your MainForm identifies a Customer, the subform shows all the Claims for that Customer.

Good luck.

OOPs == I see Paul responded at same time.

 

[I Need Help]

Thankyou for replies

@pbaldy
thankyou for reply. I follow up until the master/child bit. Is that the relationship between form and subform, without me changing anything, if so great
Oh and congrats on becoming moderator, just saw it.

@jdraw
thanks for reply. you were both very fast! sorry for not being clear. No I was talking about the same subject but for some reason switched lingo. I meant the same thing by claim and case. I must also congratulate you on the following line:
"When your MainForm identifies a Customer, the subform shows all the Claims for that Customer." - A nugget of gold for the noobs, I get it now .

@anyone
Quick general question about the forums as I'm new. If person A started a thread ages ago and person B made a nice contribution, would my reply/cry for help reach person B.
Assuming its an old thread and person B is busy and in the habit of unsubscribing.

Sam

 

[pbaldy]

The master/child links are properties of the subform control (ie they apply only to that form/subform). Thanks re moderator status!

As to responding to an old thread, your reply would reach person B if they were still subscribed (and requested email notifications). I can't speak for others, but I rarely unsubscribe from threads.

 

[I Need Help]

Many thanks for the link pbaldy, works really well.

 

[pbaldy]

Happy to help.

 

[I Need Help]

I kinda built on this question in another thread (245101) if it wouldn't be too much trouble could you have a look? Thanks for everything
Sam