Table question

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

Guest

Hi

I ceated my first table called tblCustomerProfile in which CusID is the primary key.

Now I have a second table named tblPart1 which is related to another form called frmPart1, with CheckBox for certain customer preferences.

In the second table I also use the CusId as the primary key.

Is it possible to put the CusID from tblCustomerProfile into tblPart1 when the ID is entered the first time?

Again thanks to all for your help. Much appriciated.

Cheers

Ren
 
I'm not sure if I follow, but it sounds like you have a one-to-one
relationship. For every record in tblCustomerProfile, you want to have a
record in tblPart1. Correct?

The easiest way to do this is to set up the relationship between the two
tables so that they are linked by customer number. Then, in your form where
you enter your customers, base the form off of a query that pulls data from
coth tables. When you add a record (through your form) if you add
information to both tables, a record will be created in both tables.

For example, lets say you have...

tblCustomerProfile
CusID
CusName
CusAddress
CusCity
CusSate

tblPart1
CustomerID
CusIndustry
CusNumOfEmployees
CusPreferredClient

etc.



You would create a one-to-one relationship using tblCustomerProfile.CusID
and tblPart1.CustomerID as your common link.

You would build a query that pulled all five fields from tblCustomerProfile
and all the fields from tblPart1.

I would put code behind the tblCustomerProfile.CusID field to automatically
fill in the tblPart1.CustomerID field with the same value when you tab out
of the tblCustomerProfile.CusID field.



Rick B



When you add records to the form, you will end up with a record in both
tables!




Hi

I ceated my first table called tblCustomerProfile in which CusID is the
primary key.

Now I have a second table named tblPart1 which is related to another form
called frmPart1, with CheckBox for certain customer preferences.

In the second table I also use the CusId as the primary key.

Is it possible to put the CusID from tblCustomerProfile into tblPart1 when
the ID is entered the first time?

Again thanks to all for your help. Much appriciated.

Cheers

Ren
 
Hi Ren - You may want to try two steps:
1. Create a relationship between the two tables on the
CusID fields.
2. Create a Form-SubForm with tblCustomerProfile as your
Form and tblPart1 as your subForm linked through the CusID
field. When you select your Customer, the subForm will
assume the same CusID as its link.

Good luck.

Rick
-----Original Message-----
Hi

I ceated my first table called tblCustomerProfile in
Click to expand...
which CusID is the primary key.
Now I have a second table named tblPart1 which is related
Click to expand...
to another form called frmPart1, with CheckBox for certain
customer preferences.
In the second table I also use the CusId as the primary key.

Is it possible to put the CusID from tblCustomerProfile
Click to expand...
into tblPart1 when the ID is entered the first time?
 
Hi

I ceated my first table called tblCustomerProfile in which CusID is the primary key.

Now I have a second table named tblPart1 which is related to another form called frmPart1, with CheckBox for certain customer preferences.

In the second table I also use the CusId as the primary key.
Click to expand...

One to one relationahips like this are VERY RARE. If both tables are
unique for a customer, you have the option of just putting all the
fields in one table. Why bother with the overhead of two tables and a
join?

I suspect that there are actually *many* "customer preferences", and
that you might want to consider a one to many relationship to a table
with just a PreferenceID. Using a Form with a Subform will let you
easily enter these records. Having fieldnames for each "preference" is
not a good design; if the list of preferences should ever change,
you'ld be stuck redesigning your tables, queries, forms and reports;
if you store the preference *as data* you don't need to do so.
Is it possible to put the CusID from tblCustomerProfile into tblPart1 when the ID is entered the first time?
Click to expand...

It is possible with a fair bit of difficulty, if you enter the data
using a Form... but it is not necessary and it is not a good idea.
Empty "placeholder" records have a nasty habit of remaining empty.
Instead, if you do want this design, use a Form for tblCustomerProfile
with a Subform for tblPart1, using CusID as the master link field and
child link field. It will fill in when (and not before) you enter data
into the subform.
 
Much appreciated John. THANKS

Cheers

Ren

John Vinson said:
One to one relationahips like this are VERY RARE. If both tables are
unique for a customer, you have the option of just putting all the
fields in one table. Why bother with the overhead of two tables and a
join?

I suspect that there are actually *many* "customer preferences", and
that you might want to consider a one to many relationship to a table
with just a PreferenceID. Using a Form with a Subform will let you
easily enter these records. Having fieldnames for each "preference" is
not a good design; if the list of preferences should ever change,
you'ld be stuck redesigning your tables, queries, forms and reports;
if you store the preference *as data* you don't need to do so.


It is possible with a fair bit of difficulty, if you enter the data
using a Form... but it is not necessary and it is not a good idea.
Empty "placeholder" records have a nasty habit of remaining empty.
Instead, if you do want this design, use a Form for tblCustomerProfile
with a Subform for tblPart1, using CusID as the master link field and
child link field. It will fill in when (and not before) you enter data
into the subform.
Click to expand...
 
Back
Top