Full Version: Moving from subform to subform
UtterAccess Forums > Microsoft® Access > Access Forms
First, sorry for such a basic question. I don't have access to books or even the Microsoft website (don't ask, really...). I need your help!
*background and table description**
First, I am working on a database that is meant to store and retrieve data that was originally collected in hard copy. This is *descriptive* data on villages in a developing country, and the data will be used in various ways (retreival, analysis, etc.). The data is not static in that it doesn't change, but it is kind of static, because there is nothing you can do about a lot of it. The people who are giving the data from firsthand experience are NOT the people who are entering and using the data. Data-entry clerks are entering the data from filled-in forms. There are no *optional* fields. Every detail applies to every question, everything should be filled in, and there are a heck of a lot of "wrong" answers.
In order to keep my tables "normalized" (although I don't really understand what that means- I have been told they were not normal, but all the MS Access websites are devoid of definitions- any definitions are welcome) I have been very careful to make it so that you never enter the same type of data twice, and all of my different sets of data are in different tables. So instead of one table for each group along the lines of:
etc. etc.
I have a bunch of little tables, like
and so on.
**form description and rationale**
Now, as you all can imagine, the people who will be entering the data cannot be trusted. If they can skip a box or enter the wrong type of data or not enter anything, they will. (Especially as they are not customers, but just data-entry clerks, who believe they have no interest in doing a good job.) So, since I need ALL of these fields to be completed, I need to have what appears to be one form, which starts at the top and goes to the bottom, and which has all of the questions in the "right" order (as they are on the forms where the data was originally entered).
In order to get the tab-order right, I've got a cascade of embedded forms, with subform 2 inside form 1, subform 3 inside subform 2, subform 4 inside subform 3, and so on.
Everything is all linked up neatly in the relationships tables and so on.
Now for the real problem.
Subform 3 (by the way, these are all abstract examples, not the actual names) deals with languages. Each city has a certain number of inhabitants, and these people may be divided into up to five different linguistic groups. Now once I had a table like:
tbl lang
and so on.
I was told that this was not "normalized", although to my mind it was perfectly normal. It's not like the data-entry clerk *chooses* these languages. Every village has one and only one first language, and may have one (but can only have one) second language.
But he said it wasn't "normal", so I changed it so that they enter data and the table looks like
tbl lang
langRATING (choices: 1st, 2nd, 3rd)
langTYPE (choices: Tajik, Uzbek, Paxto, etc.)
This means that for ONE CITY'S DATA, they have to enter several records on this form just to finish the languages.
When they are finished, then they should be able to tab to the next form.
**the question**
Keeping in mind that my data-entry clerks are normally not even high-school graduates, how can I make it so that they can easily finish filling in the data for tbl lang, and then tab directly to the next form?
I need the tab order on subform 3 to go around and around, but also for there to be a kind of button somehow in the tab scheme that they can press "enter" on and get to subform 4.
So far, my problems have included the following:
1) when I put a button in subform 3 that was supposed to bring the user to the (embedded) subform 4, pressing it got me an error "the object sbfrm4 isn't open". This, despite the fact that I can clearly see the sub-form in my set up. It *is* open, or looks open.
2) Anyway, I do not want them to just tab through automatically to subform4, as they will normally need to enter several records in subform 3. So it should be an "on click" go-to, not a tab-through.
How can I solve this problem? Is what I am saying clear?
First, UA has several articles on Normalization. I suggest you do a search. you will find plenty of info to help you define it. A quick definition is the process of designing table structure to reduce or eliminate duplication of data.
How you have a problem with your structure in that you have what's referred to as Repeating Groups. For example your tblVulnerablePopulation. The group there is a census, a count of household by criteria. So the count is the group. An attribute of that group is the what's being counted. By having separate fields for each item being counted you have a repating group. Which is why you were told the Lang1, lang strucutre was not normalized. So your structure should be more like this:
VPOPID (Primary Key Autonumber)
TownID (Foreign Key to identify town)
CountTypeID (FK to identify type, i.e. Livestock, homes, men, etc.)
This structure will make it easier to aggregate your counts over regions and to do other reporting and analysis. Another example was the languages. Let me explain why the normalized structure is better. Lets say you wanted a list of all towns that listed Uzbek as a language. You would have to search all the language columns. with the new structure you just have to search one column. You can also weight by the rating.
Now to your questions. The code to move to a specific control on a form is Me.controlname.SetFocus. What are you using behind your button?
Dear HTH,
Thank you for your definition. In fact, I did not see any such definition on the many pages I looked at after my search on normalization. I guess most people have books that define it.
Regarding your advice, if I let the user define the CountTypeID (isn't that what happens when you have a foreign key? you have to use a combo box in the form so that the user chooses which type? and they have to enter several records for one village sometimes), how can you make sure that they enter data for EVERY SINGLE TYPE? And the number of cows is totally different to the number of people. The types of data is "integer" for both, but otherwise, they are completely different things. So different that I really do not understand why that would count as "repeating" data. The number of widows is not the same as the number of widowers: the number of widowers is an attribute of a village, and "widower" is an attribute that a certain number of people have. Moreover, I do not need to analyse this in Access: it is physically impossible anyway. Access cannot make graphs with five different formulae and two x-axes and so on (I tried... said "not enough memory"), so I will do it in Excel, with its logical structure of DCOUNT and DSUM and other wonderful features. Does that mean I do not need to normalize?
HAs for the code- I am not programming in SQL myself. I am using the MS Access windows that you get when you click on the "properties" of an object. So on my button, I had a macro that said "gotorecord" and "gotocontrol". I have tried using one but not the other and vice-versa, but I feel that the result is so totally wrong that I must be on the completely wrong track. I deleted the button, and now I just want to know:
How do I get from one subform to another subform by pressing "enter" when you are on a button? Or after you finish with the records from one subform? The next question is related- how can I limit the number of languages (i.e., language records) they enter for each village (minimum 1, maximum 4).
It seems that I am a hopeless case. All the "beginner" Access sites are so technical starting right away with lists of dos and don'ts that do not seem to relate to my simple problems at all. Do you know of a website for true beginners?
Sorry to bother you.
You are looking at the the wrong thing to determine difference. Sure the thing being counted is different. Obviously, widows if different from widowers and cows from people. But it not WHAT is being counted that constitutes a repeating group, its that its a count. All those things have a common thread, they represent a count of some item. That's what makes the count an entity and what's being counted an attribute.
Yes, you can ensure that each item is entered in a number of ways. You can populate the table with a record for each item being counted and test to make sure each one is filled in. Or you can Test for the presence of each item before exiting the form.
The line of code I gave you is not SQL but VBA. I've never used Gotocontrol, but SetFocus will work.
Sure you can limit the number of languages entered. Using DCount to test for number of records or setting a unique index on the combination of town and Rating.
UA is the best site for Access I've seen. It caters to beginners all the way up. When we know your level we try to pitch our answers to them. If there is anything you don't understand, feel free to ask.
I have always taught that normalizaton is a group of problem-solving techniques that are applied BEFORE you have the problems that the techniques are designed to solve.
For instance, the repeating groups problem that someone has pointed out to you. The problem is that if you need to add another count, you will have to change the structure of the table. This always means no one else can use the database while you are doing this. While this may or may not be a big deal in your particular situation, it has been a big deal in enough people's situations that it has almost reached the status of a "law".
Another normalization technique is that calculated fields are NOT stored in tables. This is because Access does not store the formula that does the calculation, only the result of the calculation. As a for instance, storing someone's birthdate and age. Access will store the Age as a VALUE, say 55. Anyone entering data into the table can enter a birthdate that is NOT correct for the age OR an age that does not match up with the birthdate OR both. Therefore, what we do instead is store birthdate and calculate age. Other calculated values are dealt with in a similar fashion.
Still another technique that has stood the test of time is storing data in the smallest usable units. You will hear this referred to as atomicity or atomic. It means that you don't store someone's fullname all in one field. You have made it harder to sort by last name or to fetch those records that have a particular characteristic in common. Instead, store the FirstName and LastName in separate fields. Heck, you might even want to include middle initial, courtesy title, and suffix, depending on your data. Likewise, address is not stored all in one field, either. It becomes harder to sort by postal code or state. It becomes harder to fetch those records that have the same postal code. Instead StreetAddress, City, State, Zip Code. Other fields are possible.
Each table in your database should have a field known as a Primary Key. While there is controversy over how this is best done, the consensus here is that it should be a field that has no value as data. The simplest to use is what Access calls an AutoNumber. This is a value that is self-generated by Access each time a row is entered into the table. Its sole purpose is to ensure that each row in the table has a value that is different from every other row. This way, there is always a way to tell one row from another.
That's enough for now.
Thanks to Scott and Frank for that.
o Frank- don't worry, the database is still very much in design phase. I didn't include the PK (all auto number) in my examples but it is there. There is no question of interruption in use- I am prepared to start over at any time. The forms are being designed now, however, because one of the main reasons I'm using Access instead of Excel is ease of data entry, which means that if I can't put it in a form, it's not worth it. In fact I'm trying to start over now, although it's difficult since I remain very worried that if a test doesn't work, people will not enter the fourth language and so on.
Atomizing data is not an issue with me- since I use Excel and other tables, I can immediately see the benefit of this. It's the separation of tables into tiny units that gets to me, because of the types of analysis that I want to do. Likewise, I don't even allow calculated data in my data-collection forms (except for age, because here people count the years as they go along and have to calculate back to birth-year... which means that for them, age is not a calculation but an estimate of a total count, whereas birth year is a very difficult calculation to make).
All advice I've heard on normalization so far has to do with breaking down tables into the tiniest tables possible with as little "repeating" data as possible. However, sometimes I think what is repeating to one person is not repeating to another. For example, we ask for the numbers of families, households, people, children, etc. and then cross-check it all with formulae, because it never adds up and it's the first way to check the work of the people collecting the data. So we NEED all of that data. It's not like you can order three families, two households, but none of the population, with a total order of five items. These things- number of families, number of households, total population- they are all attributes of a village in our ontology.
To Scott- I see what you mean... "That's what makes the count an entity and what's being counted an attribute." This means that it is exactly opposite to reality and language, in which things have number as an attribute ("ten cows", "55 people, with 13 children among them"). I have an inherent distrust of systems that do not respect the normal logic of life. We do not think in terms of "a peopleish 55 with a childreny 13 in it" or "a ten made up of cows". I must wonder whether this is a problem with the way Access deals with such things, and not the way I am programming it, since after all, who is in charge here? Or am I missing the point?
Oreally would like to understand the main point behind all of this. I just know that if I understand the system I will understand how to use it, but until then it will be more head-banging. Thanks ever so much for your help.
Kind regards,
I re-read your original post and now believe that due to the problems you are going to have with data entry your tables and forms should be as simple as possible.
The repeating groups problem that comes up with the languages and the various counts of things and people only is a problem is you have to continuously change the design of your tables to allow more languages or counts to be placed in the table. If you are ALWAYS going to have the same number, then it will still not be normalized, but no one but you will know it.
In other words, instead of many tables, I would go back to your original design, one row per city with each row containing ALL the data for that city.
Then, I would put all the fields on one form. If space didn't allow all fields, I would use a tab control. Code on the Before Update event of the form would verify that all fields were filled in.
I am probably going to lose my Access Advising License because of this but it sounds to me like normalization is going to be the least of your problems.
Dear Frank,
Thanks for putting up with a stubborn goat like me. You wrote: "If you are ALWAYS going to have the same number, then it will still not be normalized, but no one but you will know it." Actually, since I am one of about 6 people in all of Northeast Afghanistan that can actually use Access to analyze data, it was always going to be only me knowing it. :-) However your words are a great comfort to me, as my main goal is to get something that can be used. Only I am not going to go ahead with my own clunky ideas without reason, as I know that I am not really an expert.
Do you know if anybody has a website describing how different ontologies can lead to different normalizations? E.g. in my ontology, the atom is the village. Things that may sound like smaller units are, for our intents and purposes, just general descriptors of the village. At the same time, I am always afraid of leaving things out of my normalization ontology and this principle of normalization is still hazy for me.
Advice is welcome!
Elizabeth, you will have some problems using a database that is not normalized, which is why we push it so hard. However, given your other problems, like staying alive in what we in the US think is a war zone, they will be minor.
That you need to do is carefully determine what output you wish to get from the database. Then, once that has been done, you must of course put the data into the database what you think you need to generate that output. Normalization simply gives you more flexibility in meeting the needs that you cannot foresee right now.
HAs a for instance, the up to 5 languages that a village might have. If you put the languages in the same record, as Language1, Language2, etc., then any queries you make to find out which villages have a certain language in common become harder to do because you don't know exactly which Language fields to query. For VillageA, the language of interest might be in the Language1 field but for VillageB, the language of interest might be in the Language2 field. It can be done, but is harder to do is all.
A normalized database would have a table for the Villages, which would have at least a VillageID as Primary Key, the name of the village, other data that applies to the Village as a whole, and a table for the Languages, which would have at least a LanguageID as Primary Key, the name of the language, and a VillageID that can can be used to match each record in the Languages table to one record in the Villages table. You would then "join" the tables on their respective VillageID fields and always query the language name field in the languages table for a particular language name.
The various counts would not be as difficult as the languages, because presumably each count of the same thing would be in the same field. So the village population would be in the Population field for each row of the Villages table. The herd size for each village would be in the HerdSize field for each row of the villages table. If you are always going to have the same number of counts, then this will not be a big problem. However, if you will be continually changing the structure of the table over time, then it could become burdensome depending on how many people might be using the database at one time. That is what frequently happens here in the US where databases are used around the clock for ECommerce, for instance. Then there is no good time for the database to be down. That is why we stress doing it the way Scott told you. It doesn't sound to me like that will be a problem for you.
In your situation, I would probably put the counts in the same row as the villages to which they apply and the languages in their own table as I described already. I would have a table of the possible languages which I would use to populate a combo box that the data entry clerks could possibly be persuaded to use, so that at least what languages that are entered would be spelled consistently.
I would probably not use subforms at all. One form with, possibly, a tab control. Each page of the tab control could be used to group the fields in some logical fashion. I would not use it unless I had too many fields to fit neatly on the form
I would use code on the AfterUpdate event of the form to check that all entries were filled that needed to be filled in and not let the form leave a record until all fields were filled in.
Let me know if I can help you again or if you have any questions about what I have posted.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.