Tables Relationsship

[Shariq]

I have two table; TableA and Table B. Both have a key field named Item.
When I add a record in TableA, I would like it to add to TableB
automatically. How do I do it?

 

[Ken Snell [MVP]]

Not possible directly from table. You need to use a form to add data and
then the form can be programmed to add data to another table.

Of course, I must ask... why do you wish to store apparently redundant data?
Or are there more details about this setup that are not obvioius to us?

 

[John Vinson]

I have two table; TableA and Table B. Both have a key field named Item.
When I add a record in TableA, I would like it to add to TableB
automatically. How do I do it?

Well, I'd ask - should you ever do it? I'd be inclined to say No.

First off, one to one relationships are quite rare. If you're not
Subclassing or doing Table-Driven Field Level Security (or don't
recognize those terms), you probably don't want two tables with the
same primary key.

Secondly, it is almost never appropriate - even with a valid one to
one relationship - to create an empty "placeholder" record in TableB
with the intentof filling it in later.

What real-life Entities do these two tables represent? What are you
trying to accomplish with this new record?

John W. Vinson[MVP]

 

[Shariq]

Here is the details.
The master table TableA is a huge table that contains product information;
the column name for product name is Item and it is a key field. TableB
contains serial numbers for each product and are incremented as a product
label is printed from within the application. When a product label is
printed, I want to write serial number for that product to TableB which is
not availabel to the users. The user will add new records into TableA and at
that point I want to automatically add that Item number in TableB.
I hope this explains what I am trying to accomplish.

 

[Ken Snell [MVP]]

Is there a special use for TableB that precludes your use of the data in
TableA to know which serial numbers have been issued? TableA will already
contain that information.

You haven't commented about when you are doing this? Are you using a form?
--

Ken Snell
<MS ACCESS MVP>

 

[Shariq]

TableA does not have Serial Numbers information in it, it is in TableB for
each product. The VB.net application uses TableA for data management and user
interface. When print option is selected for the selected product, the
application reads Serial Number from TableB and prints ou the label. The
Serial Numbers are managed in TableB for each product and the default value
is '00001'. The label uses TableA for product information and TableB for
Serial Numbers. There are many good reasons to have tables setup this way to
fit within our needs. The VB.net application uses API calls to a 3rd party's
labeling product to product labels.

 

[Ken Snell [MVP]]

OK. But please tell us how you're running the application. It's not possible
to give a good suggestion on how to do what you seek to accomplish without
knowing the circumstances and process that you use to generate the serial
number, etc. In other words, what is the application doing when you need it
to write a record to TableB?

Generically, using an append query probably is the best way to accomplish
your needs.

 

[Shariq]

Ken, I should have mentioned this earlier; I am adding new records in TableA
using Access database directly into the table. My question has nothing to do
how the application is run or processes the Serial Numbers or reads/writes
the Serial Numbers. All I need is to setup relationship in Access database
for two table in a way that when I add a new product in TableA, TableB woud
be updated to add the product number in it. Currently, if I delete a product
from TableA, that product number (Item) would automatically be deleted from
TableB (uses MS Access Table Relationship option).

 

[Ken Snell [MVP]]

A relationship by itself will not put "empty" records into a related table.
You will have to add a record to that TableB via an append query that is run
when you finish entering data into TableA.

That is why I asked about how you're entering data.
--

Ken Snell
<MS ACCESS MVP>

 

[Shariq]

How do I create the append query?

A relationship by itself will not put "empty" records into a related table.
You will have to add a record to that TableB via an append query that is run
when you finish entering data into TableA.

That is why I asked about how you're entering data.

 

[Ken Snell [MVP]]

It completely depends upon what you're doing in ACCESS at the time that you
need it. If you're working in a form when you want to run the append query,
you can have the form's programming create an SQL string and then execute
it. Or you can run a stored query.

Forgive me, but as I've said a few times already, if you don't tell us what
is happening at the time that you need this to run, we cannot provide
specific suggestions.

--

Ken Snell
<MS ACCESS MVP>