One form, two tables

[Guest]

Hello - I am using Access 2003.

I have two tables which I created a relationship between the two (and its a
serious relationship - LOL). I have one form and I would like to update both
tables with the same information when I enter it into the form.

Both tables are identical except for one field.

Have I asked the impossible?

Thanks!

 

[Pieter Wijnen]

1) You most likely have a serious Table design Flaw, There's very seldom a
reason to have all fields the same in two tables
2) You must use a Form for the MainTable Containing a SubForm for the
subTable where you set all the fields that are common as the link criteria
(link Master & Child)

Pieter

 

[John W. Vinson]

Hello - I am using Access 2003.

I have two tables which I created a relationship between the two (and its a
serious relationship - LOL). I have one form and I would like to update both
tables with the same information when I enter it into the form.

Both tables are identical except for one field.

Have I asked the impossible?

Thanks!

You've certainly asked the unusual. Storing the same data in two different
tables is Very Bad Design and is essentially never desirable! Why do you feel
that you need to do this!?

It can certainly be done - you could open a Recordset based on the second
table in the form's BeforeUpdate event. You'll need some trickiness to handle
new additions, updates of existing records, and (particularly) deletions. But
my question remains - what benefit are you seeking from violating relational
principles and storing data redundantly?

John W. Vinson [MVP]

 

[Guest]

Hi - I do not know Access very well (I guess you could tell by the question I
asked) so I don't fully trust it yet.

What I did was take an old log which was in Excel and convert it over into
Access. But before doing that, I stopped using it in Excel about two months
ago and started using Access so there are quite a few records not in the old
log. Hence the two tables with almost the same fields.

I think, after reading both responses, so I do not mess up Access in anyway,
I will combine the two tables into one.

 

[John W. Vinson]

I think, after reading both responses, so I do not mess up Access in anyway,
I will combine the two tables into one.

Good idea. An Append query would be the tool of choice!

John W. Vinson [MVP]