One to Many and Auto Number Problem

[Robert T]

I helped a co-worker set up a One to Many design. The parent table had a
field called TransID and we linked it to a child table which also had a field
called TransID. However, in keeping with the goal of having unique records [a
Primary Key], even though I don't like them, we added an Auto Number field to
the child table. Keep in mind the linking field was still TransID.

The One to Many form did NOT work because of a problem related to the Auto
Number field in the child table. I therefore deleted the Auto Number field
[which I don't like] and then everything worked perfectly.

Questions
-----------
1. Did I do something wrong?
2. Does every table need a Primary Key?
3. Since everything's apparently working perfectly now, it appears to me
that we don't need a Primary Key in the child table. Am I wrong?

Thanks,
Robert

 

[Stefan Hoffmann]

hi Robert,

I helped a co-worker set up a One to Many design. The parent table had a
field called TransID and we linked it to a child table which also had a field
called TransID. However, in keeping with the goal of having unique records [a
Primary Key], even though I don't like them, we added an Auto Number field to
the child table. Keep in mind the linking field was still TransID.

This setup is correct, if it looked like that:

ParentTable:
- ID, AutoInc, Primary Key
- otherFields, ...

ChildTable:
- ID, AutoInc, Primary Key
- TransID, Long Integer
- otherFields, ...

The One to Many form did NOT work because of a problem related to the Auto
Number field in the child table. I therefore deleted the Auto Number field
[which I don't like] and then everything worked perfectly.

What kind of problem?


mfG
--> stefan <--

 

[mscertified]

1. Did I do something wrong? -Maybe

2. Does every table need a Primary Key? -No
3. Since everything's apparently working perfectly now, it appears to me
that we don't need a Primary Key in the child table. Am I wrong? -No

-Dorian

I helped a co-worker set up a One to Many design. The parent table had a
field called TransID and we linked it to a child table which also had a field
called TransID. However, in keeping with the goal of having unique records [a
Primary Key], even though I don't like them, we added an Auto Number field to
the child table. Keep in mind the linking field was still TransID.

The One to Many form did NOT work because of a problem related to the Auto
Number field in the child table. I therefore deleted the Auto Number field
[which I don't like] and then everything worked perfectly.

Questions
-----------
1. Did I do something wrong?
2. Does every table need a Primary Key?
3. Since everything's apparently working perfectly now, it appears to me
that we don't need a Primary Key in the child table. Am I wrong?

Thanks,
Robert

 

[Armen Stein]

I helped a co-worker set up a One to Many design. The parent table had a
field called TransID and we linked it to a child table which also had a field
called TransID. However, in keeping with the goal of having unique records [a
Primary Key], even though I don't like them, we added an Auto Number field to
the child table. Keep in mind the linking field was still TransID.

The One to Many form did NOT work because of a problem related to the Auto
Number field in the child table. I therefore deleted the Auto Number field
[which I don't like] and then everything worked perfectly.

Questions

Well, something was wrong. We use AutoNumber primary keys in most
tables, including child tables. As long as the relationships are
connected to the right fields, they should work fine.

2. Does every table need a Primary Key?

In theory, no. In practicality, yes.

3. Since everything's apparently working perfectly now, it appears to me
that we don't need a Primary Key in the child table. Am I wrong?

Maybe. In the future you may need code, relationships or other logic
that refer to specific child records. That will be difficult without
a primary key.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Robert T]

Parent Table:
---------------
The Primary Key is TransID [text field] that is an autoincemented field via
code on the form.


Child Table
------------
The first field was the Auto Number field [Primary Key]
The 2nd field was TransID [Text and Linking Field].

The above did not work because Access would not allow us to save any records
once we enterted data into the child table on the form.

After I deleted the Auto Number field from the child table, everything
worked perfectly.


:

 

[Tony Toews [MVP]]

I helped a co-worker set up a One to Many design. The parent table had a
field called TransID and we linked it to a child table which also had a field
called TransID. However, in keeping with the goal of having unique records [a
Primary Key], even though I don't like them, we added an Auto Number field to
the child table. Keep in mind the linking field was still TransID.

The One to Many form did NOT work because of a problem related to the Auto
Number field in the child table. I therefore deleted the Auto Number field
[which I don't like] and then everything worked perfectly.

Why don't you like the AutoNumber field on the child table? All my
apps have autonumber primary keys on every table.

I suspect you may have had the wrong fields specified in the Link
Child fields on the subform controls properties on the parent form.

Questions

Strictly speaking no. But life will be much more difficult down the
road without one.

3. Since everything's apparently working perfectly now, it appears to me
that we don't need a Primary Key in the child table. Am I wrong?

What if you want to delete one record in the child table. How do you
refer to that one record?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Robert T]

:

Good Questions Tony:

[Why don't you like the AutoNumber field on the child table? All my
apps have autonumber primary keys on every table.]

I like using text fields. In fact, I teach my students that unless you are
going to perform calculations with numbers, such as dividing, multiplying,
etc. you should always use TEXT fields.

Setting up an auto incremental rule on a text field is simple and we never
have to worry about losing numbers. I guess the latter point is trivial, but
I feel as if I have more control with text fields.

[I suspect you may have had the wrong fields specified in the Link
Child fields on the subform controls properties on the parent form.]

Tony, that was my first thought, however, the only change I made was going
into the child table and deleting the Auto Number field. If the Link Child
field was incorrect, deleting the Auto Number field should've had no bearing
upon that relationship.

[What if you want to delete one record in the child table. How do you
refer to that one record?]

Good question but I've never had that problem before. I just find the record
in the child table and delete it.

Robert

 

[Stefan Hoffmann]

hi Robert,

Parent Table:

Child Table

The above did not work because Access would not allow us to save any records
once we enterted data into the child table on the form.
After I deleted the Auto Number field from the child table, everything
worked perfectly.

This is strange, as the auto-increment does not interfere with your
referential integrity.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Robert,

[Why don't you like the AutoNumber field on the child table? All my
apps have autonumber primary keys on every table.]
I like using text fields. In fact, I teach my students that unless you are
going to perform calculations with numbers, such as dividing, multiplying,
etc. you should always use TEXT fields.

This is nonsense - sorry for saying it so clearly.

Each stored attribute has its own domain and constraints and thus a
corresponding data type. This data type depends not on the desired way
of operating with it.

Setting up an auto incremental rule on a text field is simple and we never
have to worry about losing numbers. I guess the latter point is trivial, but
I feel as if I have more control with text fields.

An auto increment is not loosing numbers, cause it never was made for
that. It is purpose is to deliver a unique value greater than the last
generated value.


mfG
--> stefan <--

 

[Robert T]

Stefan:

I like using text fields. In fact, I teach my students that unless you are

going to perform calculations with numbers, such as dividing, multiplying,
etc. you should always use TEXT fields.

This is nonsense - sorry for saying it so clearly.

Why is that nonsense? Most users make social security numbers, phone
numbers, ID Numbers, etc number fields. I tell them even though they are
using numbers, those fields should be TEXT fields.

Robert

 

[Stefan Hoffmann]

hi Robert,

Why is that nonsense?

As you can see, I'm stumbled upon this, because I have another context
of reading as we're here in a technical newsgroup. I assumed that you
meant number, when using the word number.

Most users make social security numbers, phone
numbers, ID Numbers, etc number fields.

A SSN, e.g. 078-05-1120 is not a number, it is a alpha numeric string.
This cannot be stored as a number (Long Integer). But it has a numeric
representation consisting of three fields: area number, group number and
serial number. These three fields can be stored as numbers (Long Integer).

I tell them even though they are using numbers, those fields should be TEXT fields.

You should tell you students the difference between numbers and alpha
numeric strings, and the misuse of the term "number" in the real world.

I didn't want to offend you.


mfG
--> stefan <--

 

[Robert T]

Stefan:

[I didn't want to offend you.]

I appreciate your concern but I wasn't offended. I just didn't understand
why you felt so strongly and wondered if it was a case of semantics. Although
I'm relatively new to Access, I've worked with PC databases for many years
and it was always ingrained upon us to use TEXT for what you call an Alpha
Numeric String. In fact, we were always discouraged against using a numeric
field as a Primary Key.

As for my students, they are not into terms such as "alpha numeric string",
they only see numerals, therefore, their first instinct is to make serial
numbers, phone numbers, etc. into numeric fields.

Robert

 

[Robert T]

Tony:

I stand corrected. I made a copy of the updated database and returned to the
child table where I inserted an Auto Number field as a Primary Key.
Everything worked properly. Therefore, it's fairly obvious I must have
screwed up the Child Linking Field the first time around.

Even though it was inadvertent, I apologize for giving erroneous information
in my last reply to you.

Robert

 

[Tony Toews [MVP]]

Good Questions Tony:

[Why don't you like the AutoNumber field on the child table? All my
apps have autonumber primary keys on every table.]

I like using text fields. In fact, I teach my students that unless you are
going to perform calculations with numbers, such as dividing, multiplying,
etc. you should always use TEXT fields.

I strongly disagree. You should use the type of field that is
appropriate to the data. This helps catch all kinds of errors.

What about date fields? Do you suggest text fields.

Setting up an auto incremental rule on a text field is simple and we never
have to worry about losing numbers. I guess the latter point is trivial, but
I feel as if I have more control with text fields.

How do you setup an auto incremental rule on a text field? In code?

[I suspect you may have had the wrong fields specified in the Link
Child fields on the subform controls properties on the parent form.]

Tony, that was my first thought, however, the only change I made was going
into the child table and deleting the Auto Number field. If the Link Child
field was incorrect, deleting the Auto Number field should've had no bearing
upon that relationship.

<shrug> Not sure as I can't see what you are doing but understanding
what was going on 12 or so years ago took a bit. But since then I've
been having no troubles with subforms.

[What if you want to delete one record in the child table. How do you
refer to that one record?]

Good question but I've never had that problem before. I just find the record
in the child table and delete it.

But I do all my deletes, and lots of updates in code using SQL. I
need a primary key to do so.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

A SSN, e.g. 078-05-1120 is not a number, it is a alpha numeric string.
This cannot be stored as a number (Long Integer). But it has a numeric
representation consisting of three fields: area number, group number and
serial number. These three fields can be stored as numbers (Long Integer).

This assumes you are American.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I like using text fields. In fact, I teach my students that unless you are
This is nonsense - sorry for saying it so clearly.

Why is that nonsense? Most users make social security numbers, phone
numbers, ID Numbers, etc number fields. I tell them even though they are
using numbers, those fields should be TEXT fields.

American SSNs could be text or numbers depending on your preference.
Phone numbers should be text. ID numbers could be either.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I appreciate your concern but I wasn't offended. I just didn't understand
why you felt so strongly and wondered if it was a case of semantics. Although
I'm relatively new to Access, I've worked with PC databases for many years
and it was always ingrained upon us to use TEXT for what you call an Alpha
Numeric String. In fact, we were always discouraged against using a numeric
field as a Primary Key.

While there are those who disagree the overwhelming sentiment in
Access is to use an autonumber field as the primary key which the user
never sees.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I appreciate your concern but I wasn't offended. I just didn't
understand why you felt so strongly and wondered if it was a case
of semantics. Although I'm relatively new to Access, I've worked
with PC databases for many years and it was always ingrained upon
us to use TEXT for what you call an Alpha Numeric String. In fact,
we were always discouraged against using a numeric field as a
Primary Key.

I've never used a database engine in which integer fields were not
significantly faster than any character fields of more than 4 or 5
characters. The reason was the size of the index pages involved --
if the value takes fewer bytes to store, it means you get more data
into a smaller space, so that scans of the index take less time.

Also, I think that numeric processing functions in our CPUs are
faster than character processing.

I don't know for a fact that either of these are true, but it's
always been my surmise as to why all the database tutorials I ever
encountered recommended integer fields over text where feasible.

 

[David W. Fenton]

American SSNs could be text or numbers depending on your
preference. Phone numbers should be text. ID numbers could be
either.

Even US postal code fields should be text, because you otherwise
lose your leading zeroes (anyone who has ever imported US address
data from badly-formatted Excel spreadsheets has seen this).

I can't see that there's any reason to use text for fields that
don't need to store non-numeric data. Most people don't know that US
SSN fields *do* need to be alpha-numeric, because certain classes of
numbers can have letters in them. I just pulled up some SSN data
from a 10-year-old project (the first one where I encountered it)
and there are numbers like 9CH004213 and 9FN000210.

Or course, these days, SSN should only be stored encrypted as text
or bytes, so it's really a moot point.

I think the point is good, that there are a lot of seemingly-numeric
fields that ought to be text, but there aren't so many of them as
students might think if you give them a rule preferring text, which
to me is a very inadvisable rule. I think it's great to take the
obvious examples (like US zip codes and SSNs) and use those to teach
that they shouldn't make assumptions about their data before fully
investigating it. But to make a rule favoring text is just as wrong
as storing those two data attributes as numbers.

 

[Tony Toews [MVP]]

Even US postal code fields should be text,

And, of course, this assumes that you'll never have an address that is
outside the USA.

I can't see that there's any reason to use text for fields that
don't need to store non-numeric data. Most people don't know that US
SSN fields *do* need to be alpha-numeric, because certain classes of
numbers can have letters in them. I just pulled up some SSN data
from a 10-year-old project (the first one where I encountered it)
and there are numbers like 9CH004213 and 9FN000210.

Now that one I didn't know.

I think the point is good, that there are a lot of seemingly-numeric
fields that ought to be text, but there aren't so many of them as
students might think if you give them a rule preferring text, which
to me is a very inadvisable rule. I think it's great to take the
obvious examples (like US zip codes and SSNs) and use those to teach
that they shouldn't make assumptions about their data before fully
investigating it. But to make a rule favoring text is just as wrong
as storing those two data attributes as numbers.

Agreed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/