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 |
#11
|
|||
|
|||
Dependant's Table
Mr. Vinson,
I can't thank you enough for your continued assistance. Your instruction has been very beneficial. I made tables for Grade, Rank, Religious Preference, Marital Status, and Professional affiliation...none of which have a repeating value, so in essence, wouldn't need an autonumber ID? Instead of creating tables for these values should I just type them in when prompted by placing a combobox on a form and having them stored in an appropriate field? This would certainly limit the number of tables in my database, not sure how that affects space. This method will allow me to use queries based off of the actual value (e.g. M for male) correct? "John W. Vinson" wrote: On Sun, 3 Jun 2007 18:28:02 -0700, franklinbukoski wrote: I created the Gender Text Field as indicated in the Personnel Table. On the Form I used a combobox and typed the values: M,F into it (vice looking the values up in a Gender Table) and stored it in the gender field of the Personnel table. Worked exactly as you stated, thank you so much. I also tried creating a table: GenderID (Autonumber), Gender (Text) with the values M, F, and linking this Gender Text field to the Gender Text field in Personnel table. On the form, I input a combobox, looked the values up in the Gender table, selected to store the value in the Personnel table Gender text field with the validation rule IN ("M","F") and this did not work. The field would not accept either the M,F choice of the combobox. The relationships window would not allow me to use referential integrity or cascade options when I did this. Correct. It won't. Your personnel table - with this second option - will contain, not a gender letter, but a *long integer number* (the GenderID, a link to the numeric autonumber value). The validation rule will fail because the "lookup field" does NOT contain what it appears to contain. What *you* see is F or M; what the table actually contains is 1 or 2. The numeric value 1 does not fit the validtion rule since it is not equal to F nor is it equal to M. Access really, really pushes you to give every table a Primary Key - which is well and good; every table should have one. But it also pushes you to give every table an Autonumber Primary Key - which is often useful but is emphatically NOT required. A Primary Key should meet three tests: it must be unique within its table; it should be stable, so you don't have to deal with cascading updates if it changes; and - preferably, but less critically - it should be short. In a table of Genders, a single character text field meets all three criteria admirably: it's unique (there won't be two records in the Gender table with the same value); it's stable (F will always be Female, at least in the ordinary use of a database; if you translate the database to German it might become W but that's a one-time operation); and it's a quarter the size of a Long Integer. Keep it simple! For gender you don't NEED a Gender table. This is the kind of data for which list-of-values is ideal. Expanding to a bit more complex issue, consider a table of US States (and you can throw in Canadian provinces too). Access may want to give you an autonumber StateID, a state code ("ID", "MD", "TX") and a state name (Idaho, Maryland, Texas). I'd argue that the autonumber is redundant and unnecessary. Instead you can use a two-character text State field in your addresses, and have the two-character code as the Primary Key of your States table. A combo box on your form could store the two-letter ID while displaying the state name, if you wish. John W. Vinson [MVP] |
#12
|
|||
|
|||
Dependant's Table
Good evening,
ISO 5218 is a bit advanced for our business operation. I don't think I could convince the Boss to let me use 1 for male or 2 for female on our reports, although he may be glad that Male=1. I have to look up immutable. Just when I thought I was starting to understand a couple things... "Jamie Collins" wrote: On Jun 4, 5:01 am, John W. Vinson wrote: A Primary Key ... should be stable, so you don't have to deal with cascading updates if it changes; Stable immutable a table of Genders, Gender = masculine, feminine, etc. Sex = male, female, etc. a single character text field... [is] stable (F will always be Female, at least in the ordinary use of a database; if you translate the database to German it might become W but that's a one-time operation) "ISO 5218 defines a representation of human sexes through a language- neutral single-digit code" http://en.wikipedia.org/wiki/ISO_5218 Jamie. -- |
#13
|
|||
|
|||
Dependant's Table
On Mon, 4 Jun 2007 15:27:00 -0700, franklinbukoski
wrote: Mr. Vinson, I can't thank you enough for your continued assistance. Your instruction has been very beneficial. I made tables for Grade, Rank, Religious Preference, Marital Status, and Professional affiliation...none of which have a repeating value, so in essence, wouldn't need an autonumber ID? That's a judgement call. Some people routinely use autonumbers for all tables; some people avoid them altogether, or use them only as a last resort; others (like me) fall in between. Instead of creating tables for these values should I just type them in when prompted by placing a combobox on a form and having them stored in an appropriate field? This would certainly limit the number of tables in my database, not sure how that affects space. A Combo Box is going to be used in any case, table or not, just to let your users pick from a list of valid values. Whether that list is stored in a Table or in a Value List is up to you. The advantage of a Table is that you can have just *one* table and use it on many forms in many combo boxes, and it's easier to maintain; the advantage of a value list is that (for short lists of simple values) it reduces the number of tables and queries in your database. This method will allow me to use queries based off of the actual value (e.g. M for male) correct? Absolutely. That's one of the real advantages of the "natural key" approach of storing the human-meaningful text value - it's right there in your table and you don't need the extra step of a query looking it up. John W. Vinson [MVP] |
#14
|
|||
|
|||
Dependant's Table
On Jun 4, 11:31 pm, franklinbukoski
wrote: Stable immutable I have to look up immutable. Context is important. The use of 'stable key' in the SQL literature means one that will remain the same for a long time rather than one that will never change. An immutable key is fine if you can get it but reality doesn't always cooperate. Consider that if keys weren't supposed to change in the SQL world there would be no need for CASCADE referential actions! ISO 5218 is a bit advanced for our business operation I have to look up 'advanced' ;-) I don't think I could convince the Boss to let me use 1 for male or 2 for female on our reports... I suggest you format your data in reports rather than always using raw data values. Is, for example, your temporal data stored as fully- formatted text t to target reports or strongly-typed as DATETIME to favour querying and formatted only when displayed? I take the point, though, that using 'M' and 'F' makes the data more human-readable. (incidentally, that is an argument used against meaningless so-called surrogate keys such as autonumber). ISO 5218 is particularly advantageous when you would otherwise encode the other cases yourself: 0 = unknown (rather than NULL = missing), 9 = non- human legal person (corporate body, governmental organisation, etc). ...although he may be glad that Male=1 Did you read the Wikipedia entry? "The standard explicitly states that no significance is to be placed on the fact that male is encoded as 1 and female as 2. The encoding merely reflects existing practice in the countries that initiated this standard." Better than a YESNO column named IsMale g. Jamie. -- |
#15
|
|||
|
|||
Dependant's Table
I continue to learn more and more. Thanks for the further clarification. I
will research formatting in reports and the like. A while back someone told me to use Access because it was a very simple database/platform to use as an introduction into relational databases...hasn't been all that simple for me. "Jamie Collins" wrote: On Jun 4, 11:31 pm, franklinbukoski wrote: Stable immutable I have to look up immutable. Context is important. The use of 'stable key' in the SQL literature means one that will remain the same for a long time rather than one that will never change. An immutable key is fine if you can get it but reality doesn't always cooperate. Consider that if keys weren't supposed to change in the SQL world there would be no need for CASCADE referential actions! ISO 5218 is a bit advanced for our business operation I have to look up 'advanced' ;-) I don't think I could convince the Boss to let me use 1 for male or 2 for female on our reports... I suggest you format your data in reports rather than always using raw data values. Is, for example, your temporal data stored as fully- formatted text t to target reports or strongly-typed as DATETIME to favour querying and formatted only when displayed? I take the point, though, that using 'M' and 'F' makes the data more human-readable. (incidentally, that is an argument used against meaningless so-called surrogate keys such as autonumber). ISO 5218 is particularly advantageous when you would otherwise encode the other cases yourself: 0 = unknown (rather than NULL = missing), 9 = non- human legal person (corporate body, governmental organisation, etc). ...although he may be glad that Male=1 Did you read the Wikipedia entry? "The standard explicitly states that no significance is to be placed on the fact that male is encoded as 1 and female as 2. The encoding merely reflects existing practice in the countries that initiated this standard." Better than a YESNO column named IsMale g. Jamie. -- |
#16
|
|||
|
|||
Dependant's Table
How then do I get around using a child table that stores a "number" in the
parent table for use in queries and such. Do I just have to know (we'll use gender for simplicity purposes) that 1=male, 2=female, and use those numbers as criteria? As the choices increase I won't be able to remember their numeric value, and the database is going to be highly query/report driven. "John W. Vinson" wrote: On Mon, 4 Jun 2007 15:27:00 -0700, franklinbukoski wrote: Mr. Vinson, I can't thank you enough for your continued assistance. Your instruction has been very beneficial. I made tables for Grade, Rank, Religious Preference, Marital Status, and Professional affiliation...none of which have a repeating value, so in essence, wouldn't need an autonumber ID? That's a judgement call. Some people routinely use autonumbers for all tables; some people avoid them altogether, or use them only as a last resort; others (like me) fall in between. Instead of creating tables for these values should I just type them in when prompted by placing a combobox on a form and having them stored in an appropriate field? This would certainly limit the number of tables in my database, not sure how that affects space. A Combo Box is going to be used in any case, table or not, just to let your users pick from a list of valid values. Whether that list is stored in a Table or in a Value List is up to you. The advantage of a Table is that you can have just *one* table and use it on many forms in many combo boxes, and it's easier to maintain; the advantage of a value list is that (for short lists of simple values) it reduces the number of tables and queries in your database. This method will allow me to use queries based off of the actual value (e.g. M for male) correct? Absolutely. That's one of the real advantages of the "natural key" approach of storing the human-meaningful text value - it's right there in your table and you don't need the extra step of a query looking it up. John W. Vinson [MVP] |
#17
|
|||
|
|||
Dependant's Table
On Tue, 5 Jun 2007 04:00:01 -0700, franklinbukoski
wrote: How then do I get around using a child table that stores a "number" in the parent table for use in queries and such. Do I just have to know (we'll use gender for simplicity purposes) that 1=male, 2=female, and use those numbers as criteria? As the choices increase I won't be able to remember their numeric value, and the database is going to be highly query/report driven. Neither you nor your users should even SEE the autonumber values, much less need to remember them!!! They're "under the hood", used internally for linking things together; they should not be exposed to user view. If you're creating queries by entering literal search values on the Criteria line in the query grid, it's time to move on to the much more powerful Parameter Query. You can create a Form with an unbound combo box (cboSex let's say) and use =[Forms]![NameOfYourForm]![cboSex] as a criterion on the query. That said - let me reiterate that for a field like Sex I would always use a list of values and actually store the text. I see Jamie's point and respect it, but for the applications that I have built it's overkill. Again, you need to evaluate each case on its own requirements. John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|