one-to-one?

[mdemarte]

I have been struggling with this idea for over a week. I think that I may have a one-to-one table setup. I can't quite get it to work the way that I want though.

This will track EMS personnel. The idea that I have is to list the Basic Info for each person ONCE -- with their last name, first name, street address, city, etc. and then have 2 separate tables linked by IDNo for Fire Dept and for Haz Materials. Fire Dept would list which Fire Dept, their badge number, what date they started, etc. Haz Materials would list what their level of expertise is, pager no, etc.

Now, the problem is that when I set up the forms, I want the person's name on the Fire Dept form. If I set it up with Dlookup, then I can't find on the name. However, I CAN delete the Fire Dept record and still keep the Basic Info. If I set up the form with a query which includes both tables, then I can do the find, but when I do a delete, it wants to delete the Fire Dept record AND the basic info. If they are in the Haz Materials table, then I get an error about deleting related records.

So, the question is -- how do I set this up to do both? (That is, show the name info so I can do a find on it and yet only delete the Fire Dept info, not the Basic Info).

 

[KenHigg]

On the Fire Dept form set the record source up as a query based on the Fire Dept table and add the emp table to the query, link on id the pull the name in from that table.

Would this work?

kh

 

[mdemarte]

If I set up the form with a query which includes both tables, then I can do the find, but when I do a delete, it wants to delete the Fire Dept record AND the basic info.

No. That only works part of the way.

 

[KenHigg]

Sorry, didn't read all the way thru your post.

The way I would do this is to set a form to add basic emp data. Then another form to add fire fighter info, using a combo box to select the empl id.

kh

 

[The_Doc_Man]

OK, first, your personnel (basic) table is the parent table. An ID number (prime key) for each person is the right way to go. But at the next level, you have supplemental data that is NOT one-to-one. This is why you have problems, I think. You need to visualize it better.

First, the HAZMAT table (this is easier to discuss first and get out of the way). It has been a while since I've looked at that, but HAZMAT is a categorized certification 'cause there are different kinds of hazardous materials in which you can be certified. I.e. at a gross level: chemical, biological, radiological, explosive, etc. - then, of course, you can be level one in one of them and level three in another - and uncertified in a third category!

Therefore, certifications should be recorded individually showing when certified, last test, where certified, etc. And this is a many-to-one with the basic table as the "one" and the list of certificates as the "many." This is also a not-one-to-one because folks don't always understand that one-to-many INCLUDES one-to-one - but it ALSO includes one-to-NONE. And it would be easy to imagine that some of your folks are not yet certified.

If you forced a one-to-one relationship in your HAZMAT Cert table, you end up with some things being omitted in queries because you have conceptually forced yourself into a corner with the one-to-one paradigm. And if you set up a one-to-one table, Access will believe you. If you then have lied to it, Access will not do what you want. You would have different results between inner and outer joins on the split tables.

Now, the other table, Fire Dept. I think I disagree as to whether this is a separate one-to-one table as well. I'm sure you have a reason for minimizing your primary table, but splitting a table this way isn't correct from a normalization viewpoint. I think your person's badge number and date started and such are properly part of the basic table.

If it ever occurs that a person could be part of your EMS team without being part of a specific fire department, you have the one-to-NONE case again; i.e. one person, NO department. So it is obvious that there, you have a one-to-MANY (to include one-to-none) case.

Now, I can see that you might wish to reduce the size of a table. If you have record-length problems, I understand. BUT normally you should not have such problems with something like this.

Of course, if you have a fire-department table, you can easily link the person to the right department with a code number. But the badge number is personal data that belongs in the personal data table. It won't kill you to have an empty badge number in the basic table now and then.

I know of one or two cases where it is necessary to split tables into various one-to-one supplements. One of the cases ran into a number-of-fields limit and they were able to split it out to include lots of fields that were optional.

The other case ran into a length-of-record limit. Some narratives got too long and had to be split out for sizing issues alone.

But in general, if you don't have either of these problems, you are begging for trouble and lots of complexity when splitting a table to form a "true" one-to-one matchup.

 

[mdemarte]

First, let me apologize if I should offend anyone in my reply. I realise that both the Doc Man and Pat Hartman are more experienced than myself. I am going to provide a bit more detail for clarification purposes.

Yes, I had the Basic Info setup first. If I truly need that as a parent table in a one-to-many relationship, then I whole-heartedly agree with that. I also know that sometimes a one-to-many relationship can be one-to-one or one-to-none.

Now, here is my concern. If a person is listed in the Basic Info (called tblPerson), I only want them listed ONCE in the Fire Dept (called tblFireDept), not many. I look at this like the example (that I could not find) of a company tracking parking info. Since some employees ride the bus, not every employee would have parking info. Yet, this would still be a one-to-one because each employee would have at MOST one parking slot. (At least, in the example given.) And, no one should be listed in the Fire Dept WITHOUT being in the Basic Info. (No orphan records. EMS people try to avoid orphans. Sorry, could't resist.)

Now, as for Haz Mat. Most of these people would indeed be in the Fire Dept. A chemist, however, might be on the Haz Mat team, but not be a Firefighter. From the list that I was given, the highest certifications is being listed -- level 2 includes level 1. If they wish to list each certification separately, then the DocMan is again absolutely correct.

To add to this, I also need to track Ambulance people. Some will be firefighters, and some will not. Again, I only want the Basic Info listed once, then a Fire Dept record, then an Ambulance record if necessary.

Now, maybe I am missing something. I have done enough databases to realise that if I set this up as one-to-many, then when I delete a child record, the parent record can still be kept. I guess the main reason that I thought that this should be one-to-one is to avoid multiple child records. If I set this up as a one to many, then how do I avoid more than one child record per parent record?

 

[The_Doc_Man]

You have yet to define why you need to split anything away from the main table.

A badge number is unique to a person. It BELONGS in the person table. A department number is unique to a department. The department table must, of course, be separate - but the department NUMBER can serve as a foreign key in the person table to indicate membership.

Let me try this: Do a mechanical experiment for yourself. Go out & buy a box of sticky notes. Not a pad. A BOX. Get a white-board or dry-erase board. Now define a person table by writing PERSON on the white board. Define a HAZMAT qualification table on the board by writing HAZMAT. Define a fire department table by writing FIREDEPT.

Make a bunch of "records" by putting information on the sticky notes. Once you have populated your tables, draw some lines connecting related records. The more lines you have to draw when describing an individual, the worse off you are EXCEPT when you have a true many-to-one relationship such as individual HAZMAT endorsements. When you split a table one-to-one, you have added extra lines.

Why am I pointing at the lines? Because these lines represent relationships in your table. EVERY RELATIONSHIP is going to require you to write an extra query because usually, you can only join two tables at a time. Access can SOMETIMES (and I re-emphasize SOMETIMES) do this right, but usually if you have many relationships at the same time, you have to write queries to elaborate the individual relationships into a join-select query.

THIS is why you keep things together where they belong. This is why you don't build a one-to-one relationships if you don't have to. This is why we are telling you to grasp the concept of normalization: Keep like things together. Keep different things separately.

Drawing things out on the white board with sticky notes also lets you visually examine the relationships better. 'cause you cannot program what you cannot see in your head.

 

[mdemarte]

OK, let me try it this way. The original idea from the EMS dept was to have separate databases for the Fire Dept and the Haz Mat team and the Ambulance Corp. So, if a person were in all three, then their last name, first name, street address, city, state, etc. would be listed 3 times and if it ever changed, then it would have to be updated in 3 separate places. Hopefully, you agree that this is NOT the way to go. Now, after some discussion, they realized that it would make more sense to have it all together. As you have pointed out, the Fire Dept is a good example of data that is duplicated. Each of those has an ID number, so this is a good example of obeying the 1st Normal Form, I just store the ID number as the foreign key and lookup the Fire Dept.

Now, there are a couple of reasons why I don't want to have all of the data in one table. First, have you heard of FOIL, Freedom of Information Law? So, basically if the information is there, then we have to supply it if someone requests it. Of course, if the field exists on the screen, then it tends to get filled in. So, the user WANTS the pager no. and the Driver ID No. for the Haz Mat team, but not for the Fire Dept. But if it gets filled in, then someone could request it and we would have to supply it. Secondly, the majority of the EMS personnel would NOT be on the Haz Mat team. So, all of those fields would be blank. While I do have some blank fields in some databases (not everyone has a middle name or supplies it for something like this), I don't make a habit of it when it is unnecessary. Last, each of these has a photo ID associated with it and an expiration date (Fire, Amb, Haz). So, I am trying to limit the confusion here as well.

My version of the white board with sticky notes (with info grouped accordingly):

TblPerson
Memberkey
Lastname
Firstname
Midname
Street
Town
State
Zip
Homephone
Birtthdate

TblHazMat (joined one-to-one to TblPerson)
HazMatKey (equals Memberkey)
DriverIDNo
HMExpDate
HMPhotoNo
PhysDate
PagerNo
Credential (this is the level of Haz Mat)

TblFireDept (joined one-to-one to TblPerson)
FireDeptKey (equals Memberkey)
FDID (foreign key)
FDPhotoNo
FDExpDate
Firechief (boolean)

TblFDID (one-to-many to TblFireDept)
FDID (primary key)
Department

TblAmbList (joined one-to-one to TblPerson)
AmbListKey (equals Memberkey)
AmbCorp (from drop-down list)
ALExpDate
ALPhotoNo
EMTNo
EMSCaptain (boolean)

TblAmbCorp (one-to-many to TblAmbList)
AmbCorp

I hope that helps clarify matters.

 

[The_Doc_Man]

First, have you heard of FOIL, Freedom of Information Law?

My current employer's contract is with the Dept. of the Navy. I have been in the employ of Dept. of Defense contractors for over 15 years. I have heard of FOI Act all too well. But I think you misunderstand something.

Whether it is data kept in one table or FIFTY tables, a FOI request could get everything you've got. If it exists on your computer, an FOI request could (SHOULD) get it even if it is NOT part of a database, and further, you could be held in contempt if you tried to use that ploy to hide data from a court-ordered response to FOI. You can be sued for non-compliance with an FOI request from a private-citizen FOI request. Moving data to another table DOESN'T legally hide it from an FOI request.

You still have the right to "black out" information that falls under the Privacy Act or require that the person signs a promise to RESPECT the Privacy Act. Actually, the Privacy Act of 1974 added paragraphs in Title 18, Section 1030 and Title 5, Section 552. Those two titles and any referenced titles should govern your response. Also, please note that some terms of the PATRIOT Act may apply to revelation of information about emergency workers. You would do well to check the regulations posted for the Dept. of Homeland Security, which also has some interest in this matter. THEY don't want you to respond to FOI requests for public safety workers without somehow getting involved here and there.

Based on privacy act concerns, you don't lose your obligation to resond to FOI, but you still don't lose your obligation to protect data under the various Privacy Acts. If THIS is your problem, you came to the wrong forum. Check with your departmental lawyer.

I reiterate that you are seeking to over-optimize your DB at the cost of denormalizing things that should stay normalized to keep your work load from going off the deep end. I think you are making a lot of work for yourself.

The ONLY time you are going to want to separate tables into one-to-one fragments is when the ENTIRE FRAGMENT on one side of the split will be missing. BUT EVEN THEN you have problems in forcing the system to use OUTER joins rather than INNER joins for the "sparse" tables. And Access usually wants that join to be an Innie, not an Outie.

Example: In a certain Dept of Navy DB, we have a "SpouseInfo" table because in the USA you can only have one "current" spouse at a time, and sometimes you have none. ALL of the stuff in our SpouseInfo table is across the one-to-one split 'cause it is ALL null if you don't have a spouse. BUT our "DependentsInfo" table is many-to-one. You can have a lot of dependents. Considering how many ports Navy guys visit.... but I digress.

As to whether it exists in one table or ten tables and you have visibility issues as to who can see/update it ....

All of your personnel data should exist in one table, in my opinion. If you don't want everyone to see the contents of the personnel or any other tables, then you have a case where you need to carefully secure the database and lock it into a switchboard that is also your opening menu. Then hide the DB window, giving yourself a back-door to bypass it, but allow that back-door to very few people.

Trust me based on my experience here. It doesn't matter HOW many tables you use. If the users can see the DB window, they can see everything anyway. And WILL look at everything. Human nature, don't you know?

Now, in this context, if you are worried about users sneaking past you, I would advise that you look at the idea of securing the tables so that users cannot see them. I.e. no read access to the general users. Then create some queries, set the "ByOwner" attribute, and choose some owners - perhaps "fake" owners - in different groups, thus allowing you to control who can open a query. Look up "By Owner" in your help files. It will explain the idea pretty well. Alternatively, you can use your normal group structure to restrict who can look at the "By Owner" queries, but then allow the query, once accessed, to look at anything.

Every time you build another split one-to-one table, you invite confusion, conflict, and resource bloat. 'cause every extra relationship between the two tables that are one-to-one just chews away at the limit of the number of simultaneously active queries. Every one-to-one relationship invites data omission 'cause you forgot where something was kept. Every one-to-one relationship involves a slow-down in response 'cause you have to evaluate queries from inner to outer, one layer at a time, and this would be an OGRE of a database. (Ogres have layers.... ) (But I like parfait....) Sorry, off the deep end again.

 

[mdemarte]

Time out! I am not trying to HIDE anything. I am also trying to normalize the data. That is why I visualize the data as I have broken it out. There are 4 people who will be looking at this data, but primarily ONE who will be maintaining it -- so not really able to control what they see. In my experience, most users will try to fill in a field such as pager no. or driver license no., even if they don't need it.

Like your Spouse info example, Haz Mat or Fire Dept or Amb Corp would only be filled in IF that's what the EMS person is. Yes, 90-95% would be Fire Dept, while only 20% would be ambulance and even less would be Haz Mat. Also, by making it one-to-one the EMS person could only be listed once in any one of the associated tables. I realize that it is a bit more overhead, but it makes sense.

I feel like we are going in circles here. Thanks for the input. I will play with it a little more.

 

[KenHigg]

mdemarte, I monkeyed with this for a few minutes and got it to do what I think you are aiming at (in part). It allowed just one record in the subord tbl and did not del the main record when the sub record was deleted.

Is this what you were driving at?
kh

 

[The_Doc_Man]

I finally get a glimmer of what you are saying, but this is complexity not normally associated with Access. In the Navy, we have Office and Enlisted supplemental data, and they are different tables 'cause of the differences between the two.

Let's say that you have a person table and you have unique person numbers you can use as prime key. You have name, address, phone, stuff like that. We don't disagree on this part of the issue.

That person could be one of the following:

1. Fire dept. Has a badge number in a specific format, has certain training and is in a particular department. Has passed physical requirements. Is assigned as driver, ladder operator, pump engineer, captain, fire fighter, or whatever other specialties and titles you recognize.

2. EMS. Has a different badge number, different format entirely. EMS Dept. is separate from Fire Dept. Person has been certified in different things. Driver, some levels of expertise. Don't know your state's requirements but in S. Louisiana, they have some EMS trainees who can drive and assist but cannot be the senior person in a unit (so cannot go by themselves on a run.) Others are allowed to be senior person in unit, can use radio & radio-linked equipment to get drug orders from physician, etc.

3. HAZMAT. Has a certificate. Has some endorsements for specialties. Maybe chemical, biological, radiological, etc.

AND in order for what you say to make sense, the information associated with the person for a fire dept. is TOTALLY DIFFERENT in number and type of fields from info for an EMS, which is TOTALLY DIFFERENT in number and type of fields from info for a HAZMAT person (who could be either Fire or EMS in terms of "owning" dept.)

Further, for it to make PRACTICAL sense, we have to be talking about more than just a couple of fields of sub-class unique info. I.e. if the ONLY differences between EMS and Fire Dept. are badge-number format, dept. number format, and maybe a couple of check-boxes for qualifications, then you do yourself LESS dirt by having blanks in a couple of places and combining the many one-to-one tables into a single table.

If the above case is correct and you are talking about a LOT of fields - like dozens of differences, you can do your one-on-one and it makes sense. BUT you will face pitfalls regarding inner/outer join situations. Access will really, really WANT you to allow it to define INNER joins. Your master query that retrieves "everything possible" will be a nightmare involving not less than three layers. And the query will STILL have nulls in it for folks who are EITHER an EMS or a Fire Dept. person. 'cause they won't be both, if I read you correctly.

Particularly if you want to see everyone from a single form, you would do better to have a single table for personal data. Otherwise you will have to force a sub-form situation for any unique details. But you will need some smarts on the form to identify whether the person is EMS or Fire Dept., and it sounds like the HAZMAT qualifications would be possible for either person. So we are talking about forms that need to be smart enough to tell which format they have to display and automatically adjust to show that info AFTER the parent record is loaded. Which usually means that you cannot truly make the main form a parent/child situation, cause you can have more than one child possibility. I.e. FIRE, EMS, FIRE+HAZMAT, EMS+HAZMAT. Reports will have the same exact problem.

Besides that, if you ever have more than one qualifier in the future (i.e. right now you don't distinguish between chem. and biol. and radioactive hazmat, but in the future you need to), you will wish you had not forced that into a one-to-one situation.

In the final analysis, it is your DB and you can round-file everything we tell you - but Pat and I see you walking into a mine field. I'm trying to keep you from blowing your foot off. If you want to go there, go and I'll even bless you for trying it. But you are inviting confusion and complexity. You should seek ways to UNIFY rather than SEGREGATE your supplemental data so that you can build well-behaved forms that won't rear up and bite you down the road. If you think you are still right, good luck. I hope you enjoy the work you have given yourself in dealing with the complexity of the forms you will need to show essentially disparate individuals on a single, unified form from a non-unified data table.

 

[KenHigg]

Hi Doc,

So what is your suggestion? Putting all the flds in one table?

kh

 

[mdemarte]

Ken Higg -- still not quite what I was looking for. I have a start on something.

The Doc Man -- Thank your for hanging in there with me. Yes, there is a bit more complexity involved, since some of our Fire Depts have Amb as part of their setup (which means a few more fields for that, but still under the Fire Dept section) while others are separating out into their own Amb Corp (for billing reasons, New York state is wonderful that way). Some of those Amb Corp people that used to be under the Fire Dept are still firefighters, too. Also, I suspect the same thing as you, that the Haz Mat is just getting started here and that more detail will be coming down the road. That is one reason that I am trying to set this up correctly now. Rest assured that I would not simply round-file anything that simply disagreed with my current attempt. I would prefer NOT to blow off my foot either. I have data with which I am testing this as well. I also spent some time reviewing Pat's Auto Populate and Cascade Combo example.

Now, most of the reports will deal directly with the tables as I have set them up. That is, all of the Fire Dept personnel, by town. Or, just a town's Fire Dept personnel. Or, just the Haz Mat people. However, for a county-wide emergency, they will want to know how many EMT Paramedics they have, and I will want to be able to give that to them from both the Amb Corp and the Fire Dept Amb people. A little farther down the road, I will be looking at that.

Pat Hartman -- Thanks for your input. Not having designed a one-to-one setup, I did not know that there is still a parent child setup here. On reflection, it makes sense. I would not want a blank Basic Info record for a Fire Dept person, etc. I originally had the key field names the same, then changed them because I thought I was doing it wrong! OOPS! I will switch it back.

I had not thought about tabs for different sections. I like the idea and will check into that.

You also brought up a good point about being able to add a different primary key later -- if after testing that becomes necessary. I learned my lesson about the necessity for that after converting some Approach DBs (that I had designed years ago) WITHOUT primary keys in the child tables. Yes, there were foreign keys to link them to the parent table, just no primary key in the child table.

 

[The_Doc_Man]

mdemarte,

I guess part of my viewpoint is that I have experience. You know what that is, don't you? Experience is the ability to recognize your mistakes when you make them again... (with apologies to the estate of Ambrose Bierce).

Seriously, WHERE POSSIBLE, you will do better long-term to consolidate rather than separate your data insofar as it applies to a single person. Yet I do have experience with cases where such consolidation is just not possible. I foresee some real "smarts" being required behind your forms, to the point that you might not be able to do a "real" parent-child relationship, and you might do well to take THIS approach:

Instead of having a single form that is smart enough to correctly display ALL information, just show the data you WERE able to consolidate on your main form. If you just couldn't consolidate it farther, DON'T try to do sub-forms in the traditional sense.

Instead, put a command button on your form to open a Pop-Up form that holds your supplemental info specific to the table (I.e. Amb or Fire Depts) from where it came. Put some smarts in the code underneath that button to do these things:

a) open the correct form for that person, whether Fire or Amb or whatever. This means that in the future, you could add a third type of supplemental table if, say, your police dept. fell into this database down the road ('cause of the HAZMAT qualifications.)

b) In the DoCmd where you pop up the pop-up form, tell it the primary key for that person (which is, of course, in the primary form). HINT: Here, it would be easier syntactically if the primary key is a pure number totally independent of badge number or any other formatted department-related number. Cause the DoCmd.OpenForm syntax of the WHERE clause to be used as a filter for the form makes it easier to provide pure numerics.

c) Under the button you can identify cases where the person has NEITHER class of entry ('cause it hasn't been added yet) and offer to invoke a dialog box to CREATE the right class of entry. With the smarts in the button, the application becomes smoother from an operational standpoint even if the code is a bit uglier from the programmer's standpoint.

You might also add a separate button to open up the HAZMAT stuff as a distinct pop-up form, again passing it the primary key for that person before you open the form. Now, doing it this way, if you eventually make the HAZMAT take on a many-to-one structure, you just change that one form and touch nothing else. Besides, as you pointed out earlier, HAZMAT does not restrict itself to the Fire Dept.

I still think consolidation is going to be better for you, but if you really cannot bring the disparate forms together, then the approach I suggested above might be easier to keep straight and might just minimize the logic you have to implement.

It is so because it uses a time-honored method: Divide and conquer. By making the "other side" of the one-to-one a separate, monolithic form, you isolate its logic. Then the only problem is passing the ID number to the pop-up form. Let the pop-up handle its own affairs in isolation. And if you did design the tables correctly, there will never be any "bleed-through" of data from the primary table to the supplemental table except for the personal ID number. I.e. that will be the ONLY commonality between the two tables. That is, after all, the goal of normalization.

Stated another way: If you are going to have truly separate and independent tables, then be prepared to have truly separate and independent forms to go with them. Ditto reports. Choose methods that support such independence from the get-go, 'cause you'll spend too many hours retrofitting the mess otherwise.

 

[mdemarte]

USERS! GRUMBLE! GRUMBLE!

Well, 2 weeks of work down the tube! I had the application set up (with the one-to-one relationships as outlined above, just corrected the primary key to be the same name of memberkey) far enough to show the main user the application with the separate forms. I guess it's a good thing that I did not have it all done. Now mind you, this user had been heavliy involved in the discussion. But somehow, she was expecting the info on one page.

So, she and I talked about separating the info by grouping the different areas together and then putting a box around them. If someone leaves say the Fire Dept, but stays in the Amb Corps, then I will put a button to clear out that info (it would have been easier to delete the Fire Dept record). I did re-express my concern that if the person was NOT in say Haz Mat, that the info for that area should NOT be filled in. This will be one table record, like the DocMan suggested.

Anyway, should anyone else be interested in what I did:

First, the user was to input a Basic Info record, called tblPerson. As Pat Hartman pointed out, that is the Parent record. Next, in order to add a person to the Fire Dept, I tested for an existing record. (Found this part on this web site.)

mcrTestFireDeptForDupRec

RunCommand SaveRecord

Not IsNull(DLookUp("[memberkey]","tblFireDept","[memberkey]=[Forms]![frmEmsPerson]![memberkey]")) and put up a Msg Box.

IsNull(DLookUp("[memberkey]","tblFireDept","[memberkey]= [Forms]![frmEmsPerson]![memberkey]")) and then ran another macro mcrAddFireDept
-------
mcrAddFireDept

Echo No
Open Form frmFireDept in Add Mode
Set Value of Item to [Forms]![frmFireDept]![memberkey]
Set Value of Expression to [Forms]![frmEMSPerson]![MemberKey]
Close frmFireDept
Open Form frmFireDept in Edit Mode Where Condition [qryTblPersonToTblFireDept]![tblFireDept.MemberKey]=[Forms]![frmEMSPerson]![MemberKey]

Echo Yes
-------
Since the Form was based on a query of tblFireDept and tblPerson, the close and re-open updated the query. Requery and Repaint Object did not work for me.

Now, if you were in the Fire Dept part of record, I did not want to delete the record, because that deletes the Basic Info also. So, I set up a delete button to delete the tblFireDept record ONLY, as follows:

mcrDelTblFireDeptRec

Echo No
RunCommand SaveRecord
OpenTable tblFireDept
ApplyFilter [memberKey]=[Forms]![frmFireDept]![memberKey]
SetWarnings No
RunCommand DeleteRecord
CloseTable tblFireDept
Echo Yes
Requery of frmFireDept
-----
Hope this helps someone else down the road.

The DocMan -- does this mean that I blew my whole self up instead of just my foot? SIGH!

 

[The_Doc_Man]

The DocMan -- does this mean that I blew my whole self up instead of just my foot?

Don't worry, you're in good company. Many programming veterans walk with a limp for a while after a customer review of the project. They're the lucky ones. The folks I pity are the ones having trouble sitting down.

By the way, just remember: USER is a four-letter word, to be spoken with equal vehemence, spite, and anger as any other four-letter word you might have occasion to use.

Single-view forms generally mean you have to have single tables or single queries (that might gloss over the details of multi-joined tables.)

Just remember, you can do almost anything with a query you could have done from a table. If this user says "It all has to be in one place" that STILL does not mean "all in one table." The user would NEVER know the difference between the two-tables-with-outer-join case and the single-table-with-many-sparse-fields case as long as you show her the query first. Even strong-headed users don't care what is behind the scenes as long as they can see something that looks like something they would understand.

And from a normalization viewpoint, no matter what your customer says, if the data's natural structure says "two tables" then make it two tables and tell the user "Your suggestion did not work correctly." Glaze her eyes over with a recitation and explanation of the normalization rules plus the side effect of inner joins (that can lose unpaired records) vs. outer joins (that can present inconvenient nulls) and she'll acquiesce quickly enough - unless she ALSO has had formal training in database layout AND can draw a workable design for you. But based on what you just told us, that won't happen.

 

[mdemarte]

Originally, this user was self-taught and had setup the database in Fox Pro to just track Fire Dept members (which included the Ambulance people). To give her credit, she did not do badly, even when she converted to Approach. Her biggest limitation is the same as what you said, one-dimensional thinking. She was typing in both the Fire Dept ID No and the Department name, instead of doing a table join. Also, she was setting up separate reports of the data for each Fire Dept instead of one broken up by Fire Dept.

Once bitten, twice shy. I already showed her the new layout with the data together on the screen as requested. So far, she approves!

I still have to setup the buttons to clear each "area" (like Fire Dept or Haz Mat). Right now I am working on the report part.

She is also using another database that I converted from Approach, that tracks the courses that people have taken. That one used to one big table, also. But the Access version has a student one-to-many to schedule, with a course one-to-many to schedule. So far, there weren't enough courses to put the instructors in a separate table linked one-to-many to the course. Anyway, once she adjusted to it, she likes it. Maybe that makes up for this one, eh?

 

[The_Doc_Man]

The worst thing in the world is a self-taught (incompletely taught) convert to a new way of thinking. 'cause they start getting ideas. Be prepared for the "good thing" that makes up for the headaches to quickly turn into a very "bad thing" when she pushes the model too hard.

To be fair, when someone is used to flat-file databases, there is a mental leap to enter the world of relational databases.

Just to protect yourself, be prepared to discuss normalization at least through third-normal form, which is all that MOST Access DBs need. Very few require 4th or 5th normal form. But 1, 2, and 3 are all very important.