PK - To AutoNumber or Not To AutoNumber - That is the Question! :-

On 8 Aug, 12:51, "BruceM" wrote:
I expect
there is a valid point in the article, but the example gets in the way.

Personally, I can never see the valid point of an article once I've
spotted a spelling mistake or see the word 'enormity' used to mean
'big' rather than 'evil' or when another such irrevocable feature has
got in the way. Excuse me while I go for a lie down.

In table design view the Data Type is listed as Autonumber, and the Field
Size as Long Integer. There is probably a semantic reason why the
terminology is inaccurate, but these are the terms Access uses. An
autonumber can be related only to a Long Integer field. I don't know what a
ReplicationID is, or how it differs from other use of an autonumber field.
I was responding to your reminding me that autonumber is not a format.

I'm confident I can show you to be wrong but could you admit it to
yourself, I wonder? Oh well, here goes nothing...

In table design view, click on 'Long Integer' and it will reveal
itself to be a dropdown. The second option on the dropdown after 'Long
Integer' is 'Replication ID' while the 'Data type' still shows
'Autonumber' (if I wasn't open minded, how would I know so much about
autonumber and in the Access user interface too g?)

I think describing autonumber as a 'data type' is as wrong as
describing it as a 'format'. I'd suggest 'property'. Here's what it
looks like as Access/Jet SQL DDL (ANSI-92 Query Mode syntax):

CREATE TABLE Employees (
myID GUID DEFAULT GenGUID() NOT NULL UNIQUE,
employee_number CHAR(10) NOT NULL PRIMARY KEY
);

The best article I can come up with is:

How to use GUID fields in Access from Visual C++
http://support.microsoft.com/kb/170117

"Microsoft Access 95 introduced a new GUID (Globally Unique
Identifier) data type used for database replication. You can use GUID
fields (called Replication ID fields in Access) to store any GUID
number in a compact 16-byte binary format that can be indexed and used
as a primary key for a table... Microsoft Access also provides an
"AutoNumber" type GUID field. You can use this type of field for the
primary key of a table and let Access automatically generate each new
GUID."

I hope those C++ examples don't lead you to dismiss the article
outright g because, for me at least, it quite clearly states that
GUID fields a.k.a. Replication ID fields are autonumber fields.

Jamie.

--

"Jamie Collins" wrote in message
ups.com...
On 8 Aug, 12:51, "BruceM" wrote:
I expect
there is a valid point in the article, but the example gets in the way.

Personally, I can never see the valid point of an article once I've
spotted a spelling mistake or see the word 'enormity' used to mean
'big' rather than 'evil' or when another such irrevocable feature has
got in the way. Excuse me while I go for a lie down.

The article claims that a particular combination of fields used as the key
will demonstrate uniqueness to the auditor's satisfaction (and deny
reimbursement to the person in my example who bought a can of paint late in
the day). The problem is not a spelling error or a misused word, but rather
that the author uses faulty logic to make a logical point that is therefore
not valid. If my logic is flawed, please point it out rather than restating
that the only problem with the article is that I am of closed mind as I read
it.


In table design view the Data Type is listed as Autonumber, and the Field
Size as Long Integer. There is probably a semantic reason why the
terminology is inaccurate, but these are the terms Access uses. An
autonumber can be related only to a Long Integer field. I don't know
what a
ReplicationID is, or how it differs from other use of an autonumber
field.
I was responding to your reminding me that autonumber is not a format.

I'm confident I can show you to be wrong but could you admit it to
yourself, I wonder? Oh well, here goes nothing...

I did not claim to be right about anything except my observations of the
table design interface. There is a column labeled Data Type. In that
column, Autonumber may be selected. In the Field Size box, Long Integer or
ReplicationID may be selected. I do know that a relationship must be
between two fields of the same Data Type (Text and Text, etc.). When one of
the fields is Autonumber, the other must be Long Integer. If you believe I
am wrong in referring to these things by the terms by which they are
identified in the design interface, I will try to remember to use your
chosen words when conversing with you ;-). I think "property" would
probably be more accurate, as you suggest, but if I was describing the
interface I would use the term the user will actually find there.

In table design view, click on 'Long Integer' and it will reveal
itself to be a dropdown. The second option on the dropdown after 'Long
Integer' is 'Replication ID' while the 'Data type' still shows
'Autonumber' (if I wasn't open minded, how would I know so much about
autonumber and in the Access user interface too g?)

I think describing autonumber as a 'data type' is as wrong as
describing it as a 'format'. I'd suggest 'property'. Here's what it
looks like as Access/Jet SQL DDL (ANSI-92 Query Mode syntax):

Geez. I used "format" to refer to how the number looks to an observer. I
acknowledged that it was inaccurate terminology. Then I used the term
Access uses. Again, this was problematic. It looks like I'm wrong at every
turn. The worst part is, it's not even relevant. You know what I mean.


CREATE TABLE Employees (
myID GUID DEFAULT GenGUID() NOT NULL UNIQUE,
employee_number CHAR(10) NOT NULL PRIMARY KEY
);

The best article I can come up with is:

How to use GUID fields in Access from Visual C++
http://support.microsoft.com/kb/170117

"Microsoft Access 95 introduced a new GUID (Globally Unique
Identifier) data type used for database replication. You can use GUID
fields (called Replication ID fields in Access) to store any GUID
number in a compact 16-byte binary format that can be indexed and used
as a primary key for a table... Microsoft Access also provides an
"AutoNumber" type GUID field. You can use this type of field for the
primary key of a table and let Access automatically generate each new
GUID."

I hope those C++ examples don't lead you to dismiss the article
outright g because, for me at least, it quite clearly states that
GUID fields a.k.a. Replication ID fields are autonumber fields.

Jamie.

--

In article ,
says...
Hi again, John,

When you adapted his database, did you do so from scratch or modify what was
there? I have a few areas that I'm not sure of and don't know if you could
provide some guidance:

1. He has all of his answers in the answers table, listed by question
number and response, i.e. 1. first choice answer 1. second choice
answer 1. third choice answer, etc. I have some about 3 questions that
require a choice from about 15 - 25 choices. Should I simply continue his
way of doing this, or create sub-tables?

2. On my forms, I have the respondent code and the interviewer code, plus
the date and time the survey was filled in. I decided to use the question
code as my PK because it is unique - each has its own combination of letters
and numbers - however, these other fields aren't numbered. Should I add a
fictitious number and have the interviewer code, date, etc. as just another
answer, or should I treat these differently?

3. I also have a lot of information about the respondents in a separate
table, but I guess this is just additional information that I would add to
the one in Duane's db.

4. I have two sets of surveys - one for adults and one for children - the
names of the surveys indicate which is which. I suppose there is no need to
separate these at all... Just have a table with questions, a table with
answers, a table with responses.

Sound correct?

Thanks!


Here is something I have put together on surveys. It is merely another thing to
take a look at. http://www.psci.net/gramelsp/temp/Survey_Practice_2.zip

I tried to implement a design that was suggested by several other people.

On Aug 8, 3:29 pm, "BruceM" wrote:
There is a column labeled Data Type. In that
column, Autonumber may be selected. In the Field Size box, Long Integer or
ReplicationID may be selected. I do know that a relationship must be
between two fields of the same Data Type (Text and Text, etc.). When one of
the fields is Autonumber, the other must be Long Integer.

Even if the Autonumber is a 16 byte Replication ID (GUID) field? I
don't think so.

Jamie.

--

OK, you got me. I don't know what a replication ID is, nor how it is used
in relationships.

"Jamie Collins" wrote in message
ups.com...
On Aug 8, 3:29 pm, "BruceM" wrote:
There is a column labeled Data Type. In that
column, Autonumber may be selected. In the Field Size box, Long Integer
or
ReplicationID may be selected. I do know that a relationship must be
between two fields of the same Data Type (Text and Text, etc.). When one
of
the fields is Autonumber, the other must be Long Integer.

Even if the Autonumber is a 16 byte Replication ID (GUID) field? I
don't think so.

Jamie.

--

On Aug 8, 3:29 pm, "BruceM" wrote:
The worst part is, it's not even relevant. You know what I mean.

I did cut you some slack: you *can* create a relationship between a
Long Integer Autonumber and (for example) a DATETIME column but I
*know* you meant with RI enforced ;-)

Jamie.

--

On Wed, 8 Aug 2007 06:22:10 -0700, dee wrote:

Hi again, John,

When you adapted his database, did you do so from scratch or modify what was
there? I have a few areas that I'm not sure of and don't know if you could
provide some guidance:

I modified his database. This was about four or five years ago, I no longer
have the database (I was working for someone else at the time), and I'm not
sure I remember all the details.

1. He has all of his answers in the answers table, listed by question
number and response, i.e. 1. first choice answer 1. second choice
answer 1. third choice answer, etc. I have some about 3 questions that
require a choice from about 15 - 25 choices. Should I simply continue his
way of doing this, or create sub-tables?

A table can hold tens of millions of records. You do NOT need or want to
create subtables. Just add more records to the answer table!

2. On my forms, I have the respondent code and the interviewer code, plus
the date and time the survey was filled in. I decided to use the question
code as my PK because it is unique - each has its own combination of letters
and numbers - however, these other fields aren't numbered. Should I add a
fictitious number and have the interviewer code, date, etc. as just another
answer, or should I treat these differently?

So each question will be asked once, and once only, for the entire use of the
database?! Surely not; but that's what a primary key does. And why should
*fields* be numbered? I'm really not at all sure what you're saying here! The
Questionnaire table should have its own primary key; the Question table should
have its own primary key (the question number, which may very well be a Text
field); but when the users are entering answers, that table should NOT use the
Question Number as its primary key!!

3. I also have a lot of information about the respondents in a separate
table, but I guess this is just additional information that I would add to
the one in Duane's db.

Sure, just add fields to the people table.

4. I have two sets of surveys - one for adults and one for children - the
names of the surveys indicate which is which. I suppose there is no need to
separate these at all... Just have a table with questions, a table with
answers, a table with responses.

Exactly. There would be separate Questionnaire records for the two surveys,
each related one-to-many to the Questions table and the Answers table.

Sound correct?

Thanks!

John W. Vinson [MVP]

HI there,

A nice clean database. I have a question for you... What if the answers are
not limited to set choices.

In my case, I have some questions whose answers are limited to the list of
choices, but I have others where they need to fill in information, such as x
number of times per week or day or month (they have to choose) that something
takes place. Other times, they may simply write their own answer.

How would that be handled?

Thanks!

--
Thanks!

Dee


"Michael Gramelspacher" wrote:

In article ,
says...
Hi again, John,

When you adapted his database, did you do so from scratch or modify what was
there? I have a few areas that I'm not sure of and don't know if you could
provide some guidance:

1. He has all of his answers in the answers table, listed by question
number and response, i.e. 1. first choice answer 1. second choice
answer 1. third choice answer, etc. I have some about 3 questions that
require a choice from about 15 - 25 choices. Should I simply continue his
way of doing this, or create sub-tables?

2. On my forms, I have the respondent code and the interviewer code, plus
the date and time the survey was filled in. I decided to use the question
code as my PK because it is unique - each has its own combination of letters
and numbers - however, these other fields aren't numbered. Should I add a
fictitious number and have the interviewer code, date, etc. as just another
answer, or should I treat these differently?

3. I also have a lot of information about the respondents in a separate
table, but I guess this is just additional information that I would add to
the one in Duane's db.

4. I have two sets of surveys - one for adults and one for children - the
names of the surveys indicate which is which. I suppose there is no need to
separate these at all... Just have a table with questions, a table with
answers, a table with responses.

Sound correct?

Thanks!


Here is something I have put together on surveys. It is merely another thing to
take a look at. http://www.psci.net/gramelsp/temp/Survey_Practice_2.zip

I tried to implement a design that was suggested by several other people.

Hi John,

Sorry if I wasn't clear - was up half the night working on this thing for
the last few nights and I think I'm getting a bit incoherent! :-)

I will have separate tables for:

- Questionnaire names and descriptions and questionnaire PK, an autonumber.

- Questions, using the question number, which, yes, is text as the PK.

- Answers, using a combined question number and answer PK and including only
those two fields.

- Responses table, using an the respondentID combined with the questionID as
PK (?) and including the individual responses.

- Questionnaire Respondents table with respondentID as PK and including
questionnaireID and participantID

- Respondents table, using their ID code as the PK. This will be very
detailed, including DOB, language, address, phone, male-female, etc., etc.

- Interviewer table, using the ID code as the PK. I am getting your idea
about a "people" table that would include both respondents and interviewers,
but there would be many blanks fields for the interviewers because we may
only include their code, first and last names and maybe one other field.
Does this sound correct?

I think that's it! Does it seem sound?

Thank you so much for your time. I greatly appreciate it and your valuable
advice.





--
Thanks!

Dee


"John W. Vinson" wrote:

On Wed, 8 Aug 2007 06:22:10 -0700, dee wrote:

Hi again, John,

When you adapted his database, did you do so from scratch or modify what was
there? I have a few areas that I'm not sure of and don't know if you could
provide some guidance:

I modified his database. This was about four or five years ago, I no longer
have the database (I was working for someone else at the time), and I'm not
sure I remember all the details.

1. He has all of his answers in the answers table, listed by question
number and response, i.e. 1. first choice answer 1. second choice
answer 1. third choice answer, etc. I have some about 3 questions that
require a choice from about 15 - 25 choices. Should I simply continue his
way of doing this, or create sub-tables?

A table can hold tens of millions of records. You do NOT need or want to
create subtables. Just add more records to the answer table!

2. On my forms, I have the respondent code and the interviewer code, plus
the date and time the survey was filled in. I decided to use the question
code as my PK because it is unique - each has its own combination of letters
and numbers - however, these other fields aren't numbered. Should I add a
fictitious number and have the interviewer code, date, etc. as just another
answer, or should I treat these differently?

So each question will be asked once, and once only, for the entire use of the
database?! Surely not; but that's what a primary key does. And why should
*fields* be numbered? I'm really not at all sure what you're saying here! The
Questionnaire table should have its own primary key; the Question table should
have its own primary key (the question number, which may very well be a Text
field); but when the users are entering answers, that table should NOT use the
Question Number as its primary key!!

3. I also have a lot of information about the respondents in a separate
table, but I guess this is just additional information that I would add to
the one in Duane's db.

Sure, just add fields to the people table.

4. I have two sets of surveys - one for adults and one for children - the
names of the surveys indicate which is which. I suppose there is no need to
separate these at all... Just have a table with questions, a table with
answers, a table with responses.

Exactly. There would be separate Questionnaire records for the two surveys,
each related one-to-many to the Questions table and the Answers table.

Sound correct?

Thanks!

John W. Vinson [MVP]

In article ,
says...
HI there,

A nice clean database. I have a question for you... What if the answers are
not limited to set choices.

In my case, I have some questions whose answers are limited to the list of
choices, but I have others where they need to fill in information, such as x
number of times per week or day or month (they have to choose) that something
takes place. Other times, they may simply write their own answer.

How would that be handled?

Thanks!


Too many errors in my database. I uploaded a somewhat better version, but there
are still too many unresolved conceptual problems. I do not think this will
work as it is.

Your question is another unresolved issue.