If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
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. -- |
#32
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
"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. -- |
#34
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
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. -- |
#35
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
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. -- |
#36
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
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. -- |
#37
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
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] |
#38
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
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. |
#39
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
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] |
#40
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
|
Thread Tools | |
Display Modes | |
|
|