Matching title and gender

[1961templar]

I thought you couldn't create any more tables as you had already reached your 5 table limit?

I had to consolidate two other tables to accommodate this. I still haven't heard back from the creator of the problem.

 

[dbDamo]

Hopefully that won't cause you problems later in design...

 

[1961templar]

Hopefully that won't cause you problems later in design...

I am not happy with the design now because its not possible to properly normalise with the limit on the number of tables.

Also the data entry form for Emloyee is not ideal requiring a title ID to be entered (1,2,3,4) instead of Mr, Mrs, Miss or Ms. I can create a Form based on a query to show this information but it is not possible to enter data into a Form based on a query.

 

[music_al]

Here ya go

I hope it does the trick


Al

 

[Kryst51]

Here ya go

I hope it does the trick


Al

Hey, I just looked at this. It has lookups at table level which should be avoided, and stores the gender in two spots which is a normalization issue. I'll fix it, and post the correction here. Good job music_al

 

[music_al]

I dont understand why its a normalisation issue.

I'd be interested to see the proper way to do it...

 

[Simon_MT]

Here's a cheat I got sick and tired of creating tables "descriptive tables" so I created a Table to contain multiple descriptors - obviously the combined Table can't be joined to allow Entry as the Foreign key is no longer unique however you can combine:

Artist Category "C"
Artist Origin "O"
Artist Style "S"
Artist Type "T"

In effect I consolidated Artists Categories, Artist Origin, Artist Style and Artist Type into one Table.

Going back to this tome regarding gender it is quite standard to fill in Sex M/F as to assume gender is not accurate enough. If you are dealing with personnel you should include the Gender option as any international or professional titles will not always conform to any standard rules. So you design your system that works for a variety of scenarios beyond conceptual considerations.

In other words you design your system to be flexible. Any arbitrary concepts are likely to send you back to the drawing board.

We have an expression in NZ - But does it work?

Simon

 

[music_al]

Is this the preferred way to do it ??


Al

 

[boblarson]

Is this the preferred way to do it ??


Al

ALMOST - get rid of the lookup at table level in tblPerson and you have it.

 

[Kryst51]

I dont understand why its a normalisation issue.

I'd be interested to see the proper way to do it...

Well, it's bad enough that we have to duplicate the gender, because of the table limit. But in your design the gender was stored in the title table and the person table. That isn't necessary. But, I understand your reasoning, however, if you store the gender in both places it can cause issues. Say you realized that Chris is indeed a woman, short for Christina. then you would have to change the title, but because gender is in both places, you would have to change it in both tables, and since thetables are related that really isn't necessary. You only need to store it with the title.

Edit: Also, storing the gender in two places ruins the point, which is data validation of the title to gender.

Also, lookups at the table level need to be avoided at all costs. Use the combo box wizard at the form level to achieve the lookup. I have attached a modified verson of music_al's db. I added in a couple of bells and whistles, I couldn't help myself.

Edit2: While writing and posting, music_Al posted his, Great, that's it except for, as Bob said, the lookup at the table level.

 

[1961templar]

Kryst51 - did you attach your modified version of music_al's db?

 

[Kryst51]

Kryst51 - did you attach your modified version of music_al's db?

I thought I did, I'll have to wait until I get to work this morning.

 

[Kryst51]

OK, I had deleted my version thinking I had posted it. As I said this has bells and whistles, but you don't need them to make the validation work, if you ignore the buttons and visible invisible stuff, then it's exactly like Music_Al's new one without the lookup at the table level.

 

[wiklendt]

ok, here's a tip: compact and repair.

it just made your 4Mb+ file down to below 400kb.

i also think, in most versions of access, you can tell access to "compact on close" the current database, which basically does 'compact and repair' each time you close the database and you don't have to remember to do it manually.

 

[Kryst51]

ok, here's a tip: compact and repair.

it just made your 4Mb+ file down to below 400kb.

i also think, in most versions of access, you can tell access to "compact on close" the current database, which basically does 'compact and repair' each time you close the database and you don't have to remember to do it manually.

Hm, thanks for that, as long as it uploads, (fits in the allowable limit) I usually don't worry to much. But I'll try to remember to do that in the future.

 

[wiklendt]

second point: i am wondering why you need a separate query for concatenated name when you already have a concatenated field in the first "qryPerson" query. seems redundant to me.... is there a limit on queries like there is on tables? in fact, i don't think you need the standalone query at all - you can just make an SQL source for the form (basically a query, but 'built-in' to the form, if you willl.)

 

[wiklendt]

Hm, thanks for that, as long as it uploads, (fits in the allowable limit) I usually don't worry to much. But I'll try to remember to do that in the future.

it's not just about meeting the upload limit. as the name suggests, "compact and repair" has more practical and helpful uses.

 

[wiklendt]

something else i noticed. you naming convention is inconsistent. you have "tbl_" and "frm_" but no underscore with "qry"... i personally would lean towards no underscores at all, but definitely would try to make the names consistent. in the field names, also, you use CamelCase. this is what i would use in your other names (e.g., tblPerson, tblTitle)

as another suggestion, i would use "tbl" for table with data, and "tlkp" for a table whose only data is to be as a source for a dropdown (Table LooKuP = tlkp). the practical upshot of which is that when access orders your tables alphabetically, you get all of your important data tables at the top of the list, followed by you lookup tables further below, and they're not all mixed up together.

 

[wiklendt]

next observation:

you have a lot of repetative code. i.e.,

    Me.Concatenated.Visible = False
    Me.FirstName.Visible = True
    Me.LastName.Visible = True
    Me.FirstName.SetFocus

and to toggle between true and false.

i would place the two visibility states:

first & last name TRUE
concatenated FALSE; and

first & last name FALSE
concatenated TRUE

into a public sub or function in a module (eg. ViewConcatTrue() and ViewConcatFalse() or similar) and refer to these as you need them in the code, rather than repeating the code over and over at each event on your form.

the practical upshot of this is if you want to change somethin down the track, you only need to do it in one place (the function) rather than remembering which events have which view and what you want to change it to...

 

[wiklendt]

hm. and if you're going to have a "save" button, perhaps you also need a "cancel" or "undo" button?