Creating relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I posted yesterday with a query about sub forms, but haven't had a response
(probably didn't explain myself very well) but on reflection, I think my
problem starts with my table relationships and I'm stuck on how to resolve it.

I have 3 main tables:
Client details - Client ID, name address etc
Claim details - claim ID, date claim received, adviser name and others. I
have a one to many relationship based on client name between these two tables
and that seems fine
3rd table is claim pending details and should have a one to many
relationship with my claim details table as each claim can have more than one
pending reason. The pending reason is based on the adviser name, but this is
where I'm going wrong as I'm struggling to dreate a relationship between
claim details table and pending details table and be able to show the adviser
name against each record.

I did it via a query, but that's when I started to get problems with being
able to update my subform, so I'm not sure if its my relationships that are
wrong or how I'm trying to set up my forms.

Can someone give me a bit of a steer in the right direction. My previous
post was yesterday and is titled 'query in a subform'

Thanks for your help.

Pauline
 
I posted yesterday with a query about sub forms, but haven't had a response
(probably didn't explain myself very well) but on reflection, I think my
problem starts with my table relationships and I'm stuck on how to resolve it.
Click to expand...

Yep. Tables first - forms afterwards! said:
I have 3 main tables:
Client details - Client ID, name address etc
Claim details - claim ID, date claim received, adviser name and others. I
have a one to many relationship based on client name between these two tables
and that seems fine
Click to expand...

Surely not client *NAME*? ClientID would be the preferable link; names
are not unique. You might have two clients both named Phil Jones.
3rd table is claim pending details and should have a one to many
relationship with my claim details table as each claim can have more than one
pending reason. The pending reason is based on the adviser name, but this is
where I'm going wrong as I'm struggling to dreate a relationship between
claim details table and pending details table and be able to show the adviser
name against each record.
Click to expand...

Again, you should put a ClaimID field in the ClaimDetails table as a
foreign key.
I did it via a query, but that's when I started to get problems with being
able to update my subform, so I'm not sure if its my relationships that are
wrong or how I'm trying to set up my forms.
Click to expand...

Your relationships are indeed wrong if you're joining on names.


John W. Vinson[MVP]
 
Sorry yes I am joining on client ID with a combo box so I see the client
name, but it seems to go wrong when I try the same between claims details and
pending details - all I see is the Id number rather than the adviser name
(and it doesn't populate my existing records), but I can't see a difference
between the what 'm trying to do with the two tables, other than the fact
that the adviser name in the claim details table is a look up field in an
adviser details table.

Sorry if this is a real basic query - really struggling to learn all this as
I've only just moved on from using excel
 
Sorry yes I am joining on client ID with a combo box so I see the client
name, but it seems to go wrong when I try the same between claims details and
pending details - all I see is the Id number rather than the adviser name
(and it doesn't populate my existing records), but I can't see a difference
between the what 'm trying to do with the two tables, other than the fact
that the adviser name in the claim details table is a look up field in an
adviser details table.
Click to expand...

I suspect you're yet another victim of Microsoft's misdesigned
"Lookup" field type. The Lookup field *shows* you the adviser name -
but that CONCEALS what is actually stored in the ClaimDetails table,
which is to say the ClaimID. The table does not contain what it
appears to contain!

See http://www.mvps.org/access/lookupfields.htm for a critique of this
feature. It is NEVER necessary to use a Lookup Field. You can always
create a Combo Box on a form to store an ID while displaying text; it
is not necessary to use a lookup field in order to do so. You should
not be using table datasheets (with or without lookups) for data
entry; use Forms instead, they're a lot more powerful and
controllable.

John W. Vinson[MVP]
 
You're absolutely right, I was using look up fields to display the name.
I've corrected that and I've been trying to use the lawtrack sample database
to guide me on setting up my combo box on the form, but I'm still getting a
bit stuck.

If my claim ID field in my table is a numeric field, how do I get it to show
the adviser name text through the combo box - I keep getting an error.
 
Hi,

I'm reading offline and don't see your original post so my suggestions may
be way off base.

On a copy of your form delete the combobox that isn't working and create a
new one with wizards enabled. Step through the process with the wizard and
it should prove to be illuminating and helpful. For additional insight,
explore the Column Property in Access's VBA Help.

HTH
 
Hi, I think i understood your problem. As far as i think
you should remove adviser name attribute from claim
details table as it is not the property of that table. It
is not the property of claim pending details table as
well . but it the property of the relationship between
those two tables. so you need to create an associative
entity for the relation between these two tables and the
adviser name will be the attribute to this associative
entity. now for clarification let us see the
cardinalities between these tables. Each claim details
may have many pending reasons as you said is right. but
each pending reason can also be linked to many claim
details. so you can create a table for the associative
entity and put claim id and claim pending details id as
foreign keys for that table along with an attribute which
is the adviser name. so this table will be your main
table to consider. remember to make these foreign keys as
composite primary keys to that table.so i think this may
solve your problem. if you have any further details then
post your message again

regards
kalyan
(e-mail address removed)
 
You're absolutely right, I was using look up fields to display the name.
I've corrected that and I've been trying to use the lawtrack sample database
to guide me on setting up my combo box on the form, but I'm still getting a
bit stuck.

If my claim ID field in my table is a numeric field, how do I get it to show
the adviser name text through the combo box - I keep getting an error.
Click to expand...

The Combo Box should be based on a Query selecting the ID and the
name; the bound column should be the column containing the ID, and the
ColumnWidths property should be set so that the width of that column
is zero (it will be stored in the table but not shown to the user).

For example, the RowSource might be

SELECT ClaimID, AdviserName
FROM tablename
ORDER BY AdviserName;

ControlSource: ClaimID
Bound Column: 1
ColumnWidths: 0;1.25"
Enabled: No ' you don't want the user changing the ClaimID
Locked: Yes ' this keeps it from being greyed out


John W. Vinson[MVP]
 
Thank you all for your help - I'm on the right road now (I think!) One
further question for now though. Some of the fields in my tables have a
finite number of option values (only 4 or 5), so is it acceptable to use a
look up field for these -Access 2003 Inside Out seems to indicate yes, but
I'm not so sure reading these posts

Thanks

Pauline
 
Thank you all for your help - I'm on the right road now (I think!) One
further question for now though. Some of the fields in my tables have a
finite number of option values (only 4 or 5), so is it acceptable to use a
look up field for these -Access 2003 Inside Out seems to indicate yes, but
I'm not so sure reading these posts
Click to expand...

I would suggest

NEVER

under ANY circumstances using a Lookup field in a table.

It is *never* necessary. It is *never* beneficial.

It's perfectly appropriate to use a lookup *table* - a small,
five-row, one field table; it's perfectly appropriate to use combo
boxes on a Form, based on that table and bound to your field. But
using the Lookup in your table gets you only *one* benefit: it makes
it two mouseclicks faster to create a Combo Box on a form. In exchange
you get all the deficits described in

http://www.mvps.org/access/lookupfields.htm

John Viescas, author of _Inside Out_, is a brilliant guy who
understands Lookup fields and their limitations inside and out. He's
one of the few people I'd recommend using them because he's aware of
their problems and knows how to deal with them!

John W. Vinson[MVP]
 
Agree re John Viescas - his book is excellent, but I think I'm reading some
things that are a bit beyond my current level of knowledge!

I am really flying now and actually think I'm beginning to understand how
combo boxes work (I think that was the main issue to start with). Today I've
started building my forms for data entry. Most of the data entry fields are
either unique data or based on a few options so for these, I've set up combo
boxes bound to the look up tables and they are working great.

I have one data set which has around 50- 60 options - Do I still use a look
up table for inputting this field on new claims. Northwind database would
suggest yes (inputting customers to orders), but I don't want to assume
that's the best way of doing it.

I think I might have a problem with my many to many relationship and forms
from that - but I'll repost if I get stuck.

Thank for all your help
 
I have one data set which has around 50- 60 options - Do I still use a look
up table for inputting this field on new claims. Northwind database would
suggest yes (inputting customers to orders), but I don't want to assume
that's the best way of doing it.
Click to expand...

Absolutely. If you said 50000-60000 I'd say you need to use some other
tool but a combo is perfect for anything up to a few thousand choices.
Be sure to use the default Autocomplete feature, and to base the combo
on a sorted query; also, have the first (or only) visible column be
something meaningful to the user rather than a numeric ID, even if the
numeric ID is the bound column.
I think I might have a problem with my many to many relationship and forms
from that - but I'll repost if I get stuck.
Click to expand...

Well... the many to many relationship is a TABLE issue; only when you
get the table set up correctly should you worry about the form!

John W. Vinson[MVP]
 
Back
Top