Joining existing records in two tables

[Jacques]

Hello all,
I have three tables with 1 to many relationships.

TableA 1 to many TableB
TableB 1 to many TableC

The PK of A is in B, and B's PK is in C.

My problem is that table C already existed with data in it.
Tables A and B are imported data, so none of the related Key fields have
their corresponding ID number in them.

I want to make a form that will allow the user (just one) to connect all the
records together. I've tried making some forms with drop downs, but even
though it shows the corresponding records and lets you choose it, it makes
another record in the table your trying to use (as oppossed to actually
connecting two already existing records).

Thanks beforehand,

 

[TC]

I imagine you'll need to write some code to change the PKs in table C
to match the records in table B.

If you had to let the user do this manually, I'd do something like
this. Have two listboxes, one on table B, and one on table C. Let the
user click a record in each box. Then, when he clicks a command button,
code behind the button would see what record he had selected for B -
extract the relevant PK for C - then write that PK into the record he
had selected for C.

Would that work?

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[John Vinson]

Hello all,
I have three tables with 1 to many relationships.

TableA 1 to many TableB
TableB 1 to many TableC

The PK of A is in B, and B's PK is in C.

My problem is that table C already existed with data in it.
Tables A and B are imported data, so none of the related Key fields have
their corresponding ID number in them.

I want to make a form that will allow the user (just one) to connect all the
records together. I've tried making some forms with drop downs, but even
though it shows the corresponding records and lets you choose it, it makes
another record in the table your trying to use (as oppossed to actually
connecting two already existing records).

Thanks beforehand,

I'm missing something here.

Is there ANYTHING in TableB which would allow you to determine to
which record in TableA it is related?

Is there ANYTHING in TableC which would allow you to determine to
which record in TableB IT is related?

You say there are no foreign keys, which implies to me that the
answers to both these questions is No. If that's the case, how is the
user (with ANY form or user interface) to determine which records go
with which???

John W. Vinson[MVP]

 

[Jacques]

We have the information, just not in the DB, and trying to go over paperwork
to match the three tables up is pain staking.

The person who normally works with this wanted to be able to connect
everything, but if they have to write down PK's as opposed to picking it
from a list, it may get problematic (and this makes user error way too
easy).

 

[Jacques]

I'm afraid my VB skills aren't up to par for that yet.

 

[John Vinson]

We have the information, just not in the DB, and trying to go over paperwork
to match the three tables up is pain staking.

The person who normally works with this wanted to be able to connect
everything, but if they have to write down PK's as opposed to picking it
from a list, it may get problematic (and this makes user error way too
easy).

Ouch. This isn't going to be easy any way you slice it!

Is there any information in the table that would let a user search for
and select (perhaps from a combo box) the proper record to relate? Or
will the user just need to see both entire records, and - what click a
button "ok, these match"?

I fully agree, that typing in a numeric ID code is NOT going to be
suitable.



John W. Vinson[MVP]

 

[Jacques]

Well,
The user knows the info, and has documentation for whatever is known.
I had made a form that allowed for the lookup of one table to match the
other, but what happens is, the table on the MANY side of the One-to-Many
relationship just makes a whole new record; and has the nerve to not keep
the record info from the information that was chosen.

 

[Jacques]

John,
I got it. I made a query (which I could have done with a form now that
I think about it) whose only source table was TableB. I then had the
related field in TableB do a lookup from TableA and bound TableA's PK. I
then repeated the process for TableC with a lookup to TableB.
This allows the user to see multiple fields so that the records are
easier to recognize.

The mistake I was making, was trying to user TableA to do a lookup from
TableB, when it should have been the other way around.

Sometimes, things take a second to click, ya know.

I appreciate the help.

 

[Jacques]

TC,
I figured it out. I appreciate the help. To see what I did, read the
bottom of this thread (my reply to John).

Thanks again,