Table help

  • Thread starter Thread starter bearli89
  • Start date Start date
B

bearli89

I have very limited knowledge of Access and was wondering if someone could
help me figure something out.

I have a table that I have labeled tblPayment. My primary key is set to
autonumber. My next column is Contract # and I have it set to look up the
contract numbers in tblContract. What I would like to do is have the next
two columns Client # and Employee # populated based on the contract number
selected.

Is there a way to do this?

Thanks
 
One never, EVER populates data in "Tabel 1" from data in "Table 2". As a
design practice, it's completely unnecessary, redundant, and plain bad design.
 
Then what do you do about foreign keys?

Dennis said:
One never, EVER populates data in "Tabel 1" from data in "Table 2". As a
design practice, it's completely unnecessary, redundant, and plain bad design.
Click to expand...
 
I'm not talking about FKs. I'm talking about duplication of data in general.
It's obvious that FK values WILL be included in other tables. But not non-FK
data.

In your post, you reqested assistance in actually copying data from a second
table into a first table. You made no mention of whether or not the data was
to be considered FK data.
 
I have very limited knowledge of Access and was wondering if someone could
help me figure something out.

I have a table that I have labeled tblPayment. My primary key is set to
autonumber. My next column is Contract # and I have it set to look up the
contract numbers in tblContract. What I would like to do is have the next
two columns Client # and Employee # populated based on the contract number
selected.

Is there a way to do this?

Thanks
Click to expand...

If you have set up the relationship between your tables, it sounds
like you want to do this via a query.

Heather
 
If you have set up the relationship between your tables, it sounds
like you want to do this via a query.

Heather
Click to expand...

P.S. I didn't mean to use to query to populate the fields in the
table. I meant to use the query to show the information you want on
the same page.
 
I have very limited knowledge of Access and was wondering if someone could
help me figure something out.

I have a table that I have labeled tblPayment. My primary key is set to
autonumber. My next column is Contract # and I have it set to look up the
contract numbers in tblContract.
Click to expand...

If you're using a Lookup Field in your table, be aware that a lot of
developers here really dislike the Lookup Field feature. See
http://www.mvps.org/access/lookupfields.htm for a critique.
What I would like to do is have the next
two columns Client # and Employee # populated based on the contract number
selected.
Click to expand...

No. You do NOT want to do this!

You can *display* these fields, on a Form (which you should be using for all
your interaction with data, table datasheets are not designed for that
purpose). It is neither necessary nor is it good design to store the client
number or the employee number redundantly in your payment table. The *ONLY*
field that you need to store in the payment table is the foreign key field to
the contract table.

To display the other contract fields on your form, you can use a Combo Box on
the form, bound to the ContractID field; include whatever other fields you
want to see in the combo's row source. Elsewhere on the form put textboxes
with control sources like

=comboboxname.Column(n)

where comboboxname is the name of that combo box control, and (n) is the *zero
based* index of the field you want to see. That is, if the client number is
the third field in the combo's row source query, use (2).

John W. Vinson [MVP]
 
Back
Top