Tables Won't Link

[Guest]

Hi,
I have about three tables that I need to link so that the information they
hold ends up in my database form. Right now two "Main" tables are slightly
identical, but the new one is an update of the old one. I created the new
table to hold more fields than the old one. The old table however has more
client names than the new one does. I need the information from the new
table to be either integrated somehow, or for the link I created between them
to work. The third table corresponds to my subform. The problem with this
one is that I need the client name in the Subform to be the same as the
client name in the Main form. Right now there is no client name in the
"ClientName" field on the Subform. Instead it's just empty. However, the
ClientName appears on the Main Form with no problem. I checked my "Link
child and master fields, and the field names I need are there. What next?

eleganthippy2005

 

[Joseph Meehan]

Hi,
I have about three tables that I need to link so that the information
they hold ends up in my database form. Right now two "Main" tables
are slightly identical, but the new one is an update of the old one.
I created the new table to hold more fields than the old one. The
old table however has more client names than the new one does. I
need the information from the new table to be either integrated
somehow, or for the link I created between them to work. The third
table corresponds to my subform. The problem with this one is that I
need the client name in the Subform to be the same as the client name
in the Main form. Right now there is no client name in the
"ClientName" field on the Subform. Instead it's just empty. However,
the ClientName appears on the Main Form with no problem. I checked
my "Link child and master fields, and the field names I need are
there. What next?

Unless there is another field that can link them, you will have to
manually fill in the missing information.

Frankly, I suggest standing back and asking yourself if your current
table design is really what you want. I can't say for sure, but it sounds
like you need to redesign your table design.

 

[Guest]

Yep, my table design is what I want. I really want to use the new table I
created which is a combo of the old Main form and the Subform info all in
one. When I try to do this, weird things happen. I don't mind inputting
extra info., but are the tables really linked? In the future, am I going to
have to input information in everytable? Right now, they are linked by
ClientName.

 

[John Vinson]

Yep, my table design is what I want. I really want to use the new table I
created which is a combo of the old Main form and the Subform info all in
one. When I try to do this, weird things happen. I don't mind inputting
extra info., but are the tables really linked? In the future, am I going to
have to input information in everytable? Right now, they are linked by
ClientName.

Two concerns here:

- It sounds like you're storing the same information redundantly in
both tables. This might be necessary temporarily as part of a
transition, but it is NOT what you want in the long run. What are the
actual fields in these tables? Are you in fact storing the same data
in two (or more) tables, other than a unique linking field?

- ClientName is a VERY VERY BAD idea for linking. A primary key (or
foreign key, linked to a primary key) should be unique, stable, and
short; names tend to fail on all three. I have three friends named
Fred Brown; it's quite plausible that you might have two clients who
happen to have the same name! And what happens when someone changes
their name?


John W. Vinson[MVP]

 

[Guest]

I do want to consolidate the tables with redundant info. However, I am
unsure what to use as a primary key. Whenever I try to use something other
than ClientName or IDNumber I get an error. What should I do. It says
something about having Null values and that not being allowed for a primary
key. Confused

 

[Guest]

Elegant,

When Access says that you have Null values it is saying that you are trying
to use a field for the primary key that has missing data. The primary key,
by definition, must be a field that contains all unique values. If any value
in the table is blank for a candidate primary key then you cannot turn that
column in the table into a primary key.

Let's say you want to make the social security number the primary key. You
have a million unique SSNs but two of the records (people) do not have SSNs
(i.e., no values -- they are blank). Access won't let you turn the SSNs into
primary keys.

If you just absolutely need have to use a field with null values as the
primary key then consider deleting those records from the tables or moving
them to another table until the missing data become available.

LDN

 

[John Vinson]

I do want to consolidate the tables with redundant info. However, I am
unsure what to use as a primary key. Whenever I try to use something other
than ClientName or IDNumber I get an error. What should I do. It says
something about having Null values and that not being allowed for a primary
key. Confused

I'm confused too.

Maybe you DON'T have a suitable candidate key at the moment. You need
a field (or a combination of fields) which a) is non-NULL in every
record in the table; b) is unique in every record in the table, no
dups; and c) is preferably though not necessarily reasonably short and
very unlikely to have its value change. If your IDNumber field is
giving you errors, it must be missing one of these conditions; or, you
might have (legitimately?) two records with the same IDNumber, or at
least one record without an IDNumber.

One possibility, a commonly used one, is to add an Autonumber field to
your table (or create a new table with all the fields of your current
table plus an autonumber, and run an Append query to move your data
into it). This has the disadvantage that the autonumber automatically
makes every record unique, making it a bit harder to trap
unintentional duplicate data; but it's pretty standard. You can use a
long integer foreign key in other tables to link to this Autonumber
primary key.

John W. Vinson[MVP]

 

[Guest]

Okay, so I was able to define my primary keys in both tables. I drew
relationships. Now what? My subform table and my subform won't link. What I
change on the subform should also change in it's table right? Not happening.
Also, my subform table and mainform tables won't link either. The main form
table has more records than the subform table, so I want the mainform table
to update the parallel records in the subform table.

 

[John Vinson]

I want the mainform table
to update the parallel records in the subform table.

YOU ARE MISSING THE POINT.

You apparently are assuming that you should be storing the same data
in the subform table as you are storing in the mainform table. YOU
DON'T WANT THIS.

Relational databases use the "Grandmother's Pantry Principle": "A
place - ONE place! - for everything, everything in its place".

A form with a subform should display *different* data on the two
forms. The Form would contain data from the "one" side table; the
subform would contain *different* data, from a different table, the
many side table.

If your main table has (say) client name, phone, address, etc., then
those fields should exist ONLY in the Client table; if your subform
table has (say, which you haven't said) Payment information, it should
contain the ClientID as a link, and the payments for that client.

If you're "backfitting" a primary key to existing tables which are not
related, the likelihood is that you either have no clientID at all in
the child table, or an incorrect one. It may require a possibly
complex update query to get the ID in.

Could you give us some help here, by posting the names and important
fields of your tables, with some indication of the content of these
fields? It may help us to suggest an improved design.

John W. Vinson[MVP]