Data in different tables.

[Wilfried]

Hi,



I have 5 tables:

main

data1

data2

data3

data4

Each table has the field "name".

In a form, I want to write data in the "name" field to all the tables at the
same time.

Is this possible without complicated programming?

Regards, Wilfried (Belgium)

[pietlinden@hotmail.com]

Re: Data in different tables.

You could... but why? What data do data1-4 contain? Are they really
child tables of main?

[Mike Preston]

Re: Data in different tables.

On Thu, 30 Jun 2005 18:43:25 GMT, "Wilfried"
<wilfried.verme ire@pandora.be> wrote:
[color=blue]
>Hi,
>
>
>
>I have 5 tables:
>
>main
>
>data1
>
>data2
>
>data3
>
>data4
>
>Each table has the field "name".
>
>In a form, I want to write data in the "name" field to all the tables at the
>same time.
>
>Is this possible without complicated programming?[/color]

Yes. It is extremely easy.

You remove the "name" field from each of the data tables and then you
only need to replace it once, in the main table.

Do a search in google on "normalization" . It will explain why you
will want to do it this way.

mike

[Bob Quintal]

Re: Data in different tables.

mbpatpas@pacbel l.net.invalid (Mike Preston) wrote in
news:42c4691c.1 082821665@news. INDIVIDUAL.NET:
[color=blue]
> On Thu, 30 Jun 2005 18:43:25 GMT, "Wilfried"
> <wilfried.verme ire@pandora.be> wrote:
>[color=green]
>>Hi,
>>
>>
>>
>>I have 5 tables:
>>
>>main
>>
>>data1
>>
>>data2
>>
>>data3
>>
>>data4
>>
>>Each table has the field "name".
>>
>>In a form, I want to write data in the "name" field to all the
>>tables at the same time.
>>
>>Is this possible without complicated programming?[/color]
>
> Yes. It is extremely easy.
>
> You remove the "name" field from each of the data tables and
> then you only need to replace it once, in the main table.
>
> Do a search in google on "normalization" . It will explain why
> you will want to do it this way.
>
> mike[/color]

What about if the "name" is the foreign key he wants to
propagate to the additional records?

--
Bob Quintal

PA is y I've altered my email address.

[Mike Preston]

Re: Data in different tables.

On Thu, 30 Jun 2005 23:03:31 GMT, Bob Quintal <rquintal@sPAmp atico.ca>
wrote:
[color=blue]
>mbpatpas@pacbe ll.net.invalid (Mike Preston) wrote in
>news:42c4691c. 1082821665@news .INDIVIDUAL.NET :
>[color=green]
>> On Thu, 30 Jun 2005 18:43:25 GMT, "Wilfried"
>> <wilfried.verme ire@pandora.be> wrote:
>>[color=darkred]
>>>Hi,
>>>
>>>
>>>
>>>I have 5 tables:
>>>
>>>main
>>>
>>>data1
>>>
>>>data2
>>>
>>>data3
>>>
>>>data4
>>>
>>>Each table has the field "name".
>>>
>>>In a form, I want to write data in the "name" field to all the
>>>tables at the same time.
>>>
>>>Is this possible without complicated programming?[/color]
>>
>> Yes. It is extremely easy.
>>
>> You remove the "name" field from each of the data tables and
>> then you only need to replace it once, in the main table.
>>
>> Do a search in google on "normalization" . It will explain why
>> you will want to do it this way.
>>
>> mike[/color]
>
>What about if the "name" is the foreign key he wants to
>propagate to the additional records?[/color]

Oh, gosh. Here we go again.

A pox on keys that have real world meaning. A pox I say.

mike

[Wilfried]

Re: Data in different tables.

The table "main" contains the names and addresses.

tables "data1-4" are used to store data of 4 different contests

to make an overall calculation with the 4 data-tables, it is necessary that
they all contain the same names (with "0" in the other fields).

Now I do this myself, for each new name I open the 4 data-tables and type a
zero in the first field (the name is filled in automatically).

<pietlinden@hot mail.com> schreef in bericht
news:1120168239 .580942.278830@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> You could... but why? What data do data1-4 contain? Are they really
> child tables of main?
>[/color]

[Wilfried]

Re: Data in different tables.

[color=blue]
>
> Yes. It is extremely easy.
>
> You remove the "name" field from each of the data tables and then you
> only need to replace it once, in the main table.
>
> Do a search in google on "normalization" . It will explain why you
> will want to do it this way.
>
> mike[/color]

I can't do that Mike, the tables are linked together with the "name"-field.

To make some calculations with the data-tables, it is necessary to have the
names in all four the tables

(even when there is no data in the other fields)

thanks anyway,



regards, Wilfried

[Bob Quintal]

Re: Data in different tables.

mbpatpas@pacbel l.net.invalid (Mike Preston) wrote in
news:42c4fac2.1 120108110@news. INDIVIDUAL.NET:
[color=blue][color=green]
>>
>>What about if the "name" is the foreign key he wants to
>>propagate to the additional records?[/color]
>
> Oh, gosh. Here we go again.
>
> A pox on keys that have real world meaning. A pox I say.
>
> mike[/color]

The O.P. would have the same problem updating a number into the
foreign key field of the four tables, whether it's a number or a
name

A pox on autonumbers and a pox on those who use them instead of
a significant primary key.

And I'm not saying that the primary key should not be a numeric
ID, just that it should never be an autonumber.

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

Re: Data in different tables.

"Wilfried" <wilfried.verme ire@pandora.be> wrote in
news:Or8xe.1345 49$7m2.7242824@ phobos.telenet-ops.be:
[color=blue]
> The table "main" contains the names and addresses.
>
> tables "data1-4" are used to store data of 4 different
> contests
>
> to make an overall calculation with the 4 data-tables, it is
> necessary that they all contain the same names (with "0" in
> the other fields).
>
> Now I do this myself, for each new name I open the 4
> data-tables and type a zero in the first field (the name is
> filled in automatically).[/color]

What you need to do is go to the relationships view and set the
four "name" fields to relate to the name in your main table.
Mike Preston is correct in that you should assign a NameID field
to that table and use it instead, because you may occasionally
run into 2 John Smiths. There are 5 Robert Quintals in the local
phone directory.

Once you have your relationships, you set up a form to enter the
data in the main table, and you then add a subform for each of
the other four forms. The wizard should take care of
automatically populating the sub-tables.
[color=blue]
> <pietlinden@hot mail.com> schreef in bericht
> news:1120168239 .580942.278830@ g47g2000cwa.goo glegroups.com.. .[color=green]
>> You could... but why? What data do data1-4 contain? Are
>> they really child tables of main?
>>[/color]
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

[Wilfried]

Re: Data in different tables.

> What you need to do is go to the relationships view and set the[color=blue]
> four "name" fields to relate to the name in your main table.
> Mike Preston is correct in that you should assign a NameID field
> to that table and use it instead, because you may occasionally
> run into 2 John Smiths. There are 5 Robert Quintals in the local
> phone directory.
>
> Once you have your relationships, you set up a form to enter the
> data in the main table, and you then add a subform for each of
> the other four forms. The wizard should take care of
> automatically populating the sub-tables.
>[/color]

Why didn't I think of that ;-)

It's so simple and I think you are absolutely right.

I'm going to try it out as soon as possible.

Many thanks to all of you.

Wilfried.

[Mike Preston]

Re: Data in different tables.

On Fri, 01 Jul 2005 15:18:48 GMT, Bob Quintal <rquintal@sPAmp atico.ca>
wrote:
[color=blue]
>mbpatpas@pacbe ll.net.invalid (Mike Preston) wrote in
>news:42c4fac2. 1120108110@news .INDIVIDUAL.NET :
>[color=green][color=darkred]
>>>
>>>What about if the "name" is the foreign key he wants to
>>>propagate to the additional records?[/color]
>>
>> Oh, gosh. Here we go again.
>>
>> A pox on keys that have real world meaning. A pox I say.
>>
>> mike[/color]
>
>The O.P. would have the same problem updating a number into the
>foreign key field of the four tables, whether it's a number or a
>name[/color]

Not necessarily. I think the OP would have a much more difficult
time. <g>

That might allow the light bulb to go on.
[color=blue]
>A pox on autonumbers and a pox on those who use them instead of
>a significant primary key.[/color]

:-)
[color=blue]
>And I'm not saying that the primary key should not be a numeric
>ID, just that it should never be an autonumber.[/color]

Autonumber and random, IMO. The more difficult it is for folks to
think that it might mean something, the better. Besides, if
autonumber and random, if and when the database is replicated, you
avoid the: "I replicated my database and now the autonumbers are no
longer in sequence. Help!" requests.

mike

[Bob Quintal]

Re: Data in different tables.

mbpatpas@pacbel l.net.invalid (Mike Preston) wrote in
news:42c583ce.1 155192398@news. INDIVIDUAL.NET:
[color=blue]
> On Fri, 01 Jul 2005 15:18:48 GMT, Bob Quintal
> <rquintal@sPAmp atico.ca> wrote:
>[color=green]
>>mbpatpas@pacb ell.net.invalid (Mike Preston) wrote in
>>news:42c4fac2 .1120108110@new s.INDIVIDUAL.NE T:
>>[color=darkred]
>>>>
>>>>What about if the "name" is the foreign key he wants to
>>>>propagate to the additional records?
>>>
>>> Oh, gosh. Here we go again.
>>>
>>> A pox on keys that have real world meaning. A pox I say.
>>>
>>> mike[/color]
>>
>>The O.P. would have the same problem updating a number into
>>the foreign key field of the four tables, whether it's a
>>number or a name[/color]
>
> Not necessarily. I think the OP would have a much more
> difficult time. <g>
>
> That might allow the light bulb to go on.
>[color=green]
>>A pox on autonumbers and a pox on those who use them instead
>>of a significant primary key.[/color]
>
>:-)
>[color=green]
>>And I'm not saying that the primary key should not be a
>>numeric ID, just that it should never be an autonumber.[/color]
>
> Autonumber and random, IMO. The more difficult it is for
> folks to think that it might mean something, the better.
> Besides, if autonumber and random, if and when the database is
> replicated, you avoid the: "I replicated my database and now
> the autonumbers are no longer in sequence. Help!" requests.
>
> mike[/color]

Alphanumeric and meaningful. Take the case of a bank. Each
account number is a primary key describing that account. the
account number is also a foreign key in the transactions table.
If you suggest using an autonumber, the auditors will tar and
feather you.

Besides, the users should never see the table. all they should
see on the form or report is a field with a label.

Different example. Invoices and invoice lines. the pk of the
invoice must never change. It should not be an autonumber but
may easily be a set of sequential alpha-numeric characters.

I'm maintaining a system where the clown who wrote it used
autonumbers as keys. He could have used three other candidates.
If you have two or more candidate keys in a table you are not
normalized.

That's my opinion, and its not humble after 25 years designing
databases.

--
Bob Quintal

PA is y I've altered my email address.

[Mike Preston]

Re: Data in different tables.

<and we're off to the races....>

On Fri, 01 Jul 2005 22:51:30 GMT, Bob Quintal <rquintal@sPAmp atico.ca>
wrote:
[color=blue][color=green][color=darkred]
>>>> Oh, gosh. Here we go again.
>>>>
>>>> A pox on keys that have real world meaning. A pox I say.
>>>>
>>>> mike
>>>
>>>The O.P. would have the same problem updating a number into
>>>the foreign key field of the four tables, whether it's a
>>>number or a name[/color]
>>
>> Not necessarily. I think the OP would have a much more
>> difficult time. <g>
>>
>> That might allow the light bulb to go on.
>>[color=darkred]
>>>A pox on autonumbers and a pox on those who use them instead
>>>of a significant primary key.[/color]
>>
>>:-)
>>[color=darkred]
>>>And I'm not saying that the primary key should not be a
>>>numeric ID, just that it should never be an autonumber.[/color]
>>
>> Autonumber and random, IMO. The more difficult it is for
>> folks to think that it might mean something, the better.
>> Besides, if autonumber and random, if and when the database is
>> replicated, you avoid the: "I replicated my database and now
>> the autonumbers are no longer in sequence. Help!" requests.
>>
>> mike[/color]
>
>Alphanumeric and meaningful. Take the case of a bank. Each
>account number is a primary key describing that account. the
>account number is also a foreign key in the transactions table.
>If you suggest using an autonumber, the auditors will tar and
>feather you.[/color]

Then the auditors need an attitude adjustment.

Then again, perhaps this is why, when banks merge, they constantly
screw things up. If the account number wasn't tied to the primary
key, then they could merge bank records quite simply. Amazing, huh?
[color=blue]
>Besides, the users should never see the table. all they should
>see on the form or report is a field with a label.[/color]

This we do not disagree with. But if they shouldn't see it, why does
it need to take any particular form? One ghost is as good as another.
Why do you want to put requirements on the ghost?
[color=blue]
>Different example. Invoices and invoice lines. the pk of the
>invoice must never change.[/color]

This applies to all pk's, imo.
[color=blue]
> It should not be an autonumber but
>may easily be a set of sequential alpha-numeric characters.[/color]

Why not an autonumber? If it doesn't change, and users don't see it,
then why does it matter what it looks like?

There is nothing that stops a business rule from applying to another
field. You want to use the database engine to enforce a relationship
between record creation and reporting. There simply is no need.
[color=blue]
>I'm maintaining a system where the clown who wrote it used
>autonumbers as keys. He could have used three other candidates.
>If you have two or more candidate keys in a table you are not
>normalized.[/color]

Maybe, but irrelevant. I don't think we are strictly talking about
normalization, here. We are also talking about changes in
information. A normalized piece of data can change, such as the
maiden name of an individual, recorded only once and used by all
processes that require use of said maiden name. A pk should not.
[color=blue]
>That's my opinion, and its not humble after 25 years designing
>databases.[/color]

I yield to your experience. But the conclusion makes no sense to me,
except in the context of ensuring an application conforms to an
outside standard (such as an auditor's requirement), whether or not
that standard makes sense from a design perspective.

mike

[Larry Linson]

Re: Data in different tables.

"Mike Preston" wrote
[color=blue][color=green]
> > That's my opinion, and its not humble
> > after 25 years designing
> > databases.[/color]
>
> I yield to your experience. But the conclu-
> sion makes no sense to me, except in the
> context of ensuring an application conforms
> to an outside standard (such as an auditor's
> requirement), whether or not that standard
> makes sense from a design perspective.[/color]

Well, having taken my first programming class in 1958 and worked full-time
as a programmer and developer since 1959, I've got a few years even on Bob.

In my (also) not so humble opinion: Surrogate keys make sense, just for
convenience, when the number of fields required to uniquely identify the
item is "many" or, out of necessity, when there aren't a unique set but
processing requirements demand a unique key. Natural keys make sense when
the number of fields required to uniquely identify the record is "few" or
"manageable ".

Use the appropriate approach depending on the circumstances and the people
doing the work. And, as the very knowledgeable Quality Assurance Manager of
a major computer company's contract services group used to tell us, "Be
very, very careful about using words like 'never', 'none', 'all', or
'always'." If you pressed him hard on the subject, he'd admit that "being
very, very careful" meant pretty close to "never" using those all-inclusive
terems. <GRIN>

Larry Linson
Microsoft Access MVP

[Bob Quintal]

Re: Data in different tables.

mbpatpas@pacbel l.net.invalid (Mike Preston) wrote in
news:42c5cb28.6 402616@news.IND IVIDUAL.NET:
[color=blue]
> <and we're off to the races....>
>
> On Fri, 01 Jul 2005 22:51:30 GMT, Bob Quintal
> <rquintal@sPAmp atico.ca> wrote:[/color]
[color=blue]
> This applies to all pk's, imo.
>[color=green]
>> It should not be an autonumber but
>>may easily be a set of sequential alpha-numeric characters.[/color]
>
> Why not an autonumber? If it doesn't change, and users don't
> see it, then why does it matter what it looks like?
>
> There is nothing that stops a business rule from applying to
> another field. You want to use the database engine to enforce
> a relationship between record creation and reporting. There
> simply is no need.
>[color=green]
>>I'm maintaining a system where the clown who wrote it used
>>autonumbers as keys. He could have used three other
>>candidates. If you have two or more candidate keys in a table
>>you are not normalized.[/color]
>
> Maybe, but irrelevant. I don't think we are strictly talking
> about normalization, here. We are also talking about changes
> in information. A normalized piece of data can change, such
> as the maiden name of an individual, recorded only once and
> used by all processes that require use of said maiden name. A
> pk should not.[/color]

It is a normalization issue as well. You have introduced
duplicate information,(a unique record identifier where a unique
identifier already exists) since you have added a duplicate
primary key.[color=blue]
>[color=green]
>>That's my opinion, and its not humble after 25 years designing
>>databases.[/color]
>
> I yield to your experience. But the conclusion makes no sense
> to me, except in the context of ensuring an application
> conforms to an outside standard (such as an auditor's
> requirement), whether or not that standard makes sense from a
> design perspective.
>[/color]
Well it does make sense from a design perspective too. It makes
the database easier to undewrstaand for whoever is going to
maintain it after you are gone. It also means that you don't
have to go to extraordinary measures to protect the uniqueness
of the field that should have been the primary key.

There are times to use a surrogate key. I have a table where the
real primary key is spread across 5 fields. (two text, two
numeric and a datetime. I copped out and used an autonumbe5r to
simplyfy the constraints. And that is the issue, in essence. If
it is simpler without the autonumber, don't use an autonumber.
[color=blue]
> mike
>[/color]
--
Bob Quintal

PA is y I've altered my email address.