Moving form Subform to another subform

[sgtSortor]

I have a form with a tab control which has 7 tabs. Each one of the seven tabs has one or more subforms. One of the subforms has two subforms on it. The main subform is personnel information. The first of the two subs has address information and the other of the two subs has phone/fax info.

I'm really having issues getting from the address subform to the phone subform.

I need to be able to tab from the Zip code field to the phone number field.

HELP!

 

[missinglinq]

It sounds like you've gone overboard with your tables! Standard design would have personal info/address info/phone/fax info in one table and on one form.

What info do you have in your "personal info" table?

 

[sgtSortor]

Because of multiple addresses (physical addresses and mailing addresses) and many different phone numbers, Phone, Fax, Cell, Alternate Phone, I was taught that to normalize this they each needed to be in a different table.

 

[boblarson]

If you have the cycle property set to Current Record, put a text box on your form with the size of .01" width and height and then set the tab order to that one to be the last in the tab order. Then in the Got Focus event of that little control put

Me.Parent.YourOtherSubformContainerName.Form.SetFocus

if on another tab you MIGHT have to use:

Me.YourTabPageName.SetFocus
Me.Parent.YourOtherSubformContainerName.Form.YourPhoneTextBox.SetFocus

As far as the multiple tables, I will usually go with the extra tables if there is likely a highly unknown number of objects for an individual. For example, if I am going to collect just the home phone I will have that in the individual's record in the Person table. But, if I am possibly going to collect their home phone, work phone, cell phone, work cell, etc. then I will have a separate phone number table.

It really does depend on the data you expect to collect. But sgtSortor you are correct in that for full 3NF normalization you would have a separate table for those pieces where you could have multiple data.

 

[missinglinq]

The key phrase here is "It really does depend on the data you expect to collect." If your data is something that you truly have no idea as to the number of items, say a person's hobbies or their music preferences or periodicals that they read on a regular basis, then sure, this needs a separate table. But telephone numbers? Addresses? I'm sorry, but that's just overkill, and the difficulties involved in the development/maintenance of a database expand exponentially with each added table. Apps of any complexity, written with adherence to all the rules but without any input of common sense are, in my opinion, doomed to continuing problems.

 

[Mike375]

I store home, business and mailing address, home ph, bus ph, mobile ph and email address on the persons record. I also store on the persons record a "key" extra phone number.

Obviously that will mean some null fields but so what. If the person has another 7 phone numbers or whatever I store those in a Many table which is for file notes. If I didn't do that way then I would have a another table and a label on the form would become visible so as to say there are other phone numbers.

The problem with going overboard with stuff like phone, address, etc is the difficulty in displaying the data. A subform just never equals field placement from the actual record.

"It really does depend on the data you expect to collect." Not only that but also the nature of your business. On my main forms I have full mailing address displayed and usually the home and business suburb and the extra key phone number.

I also have a fourth set of fields for address and that is used for actual mailings. In most cases it is a duplicate of the mailing address but some insurance prospects and clients will say.....can you use my home address for mailing or can you use some other address etc.

 

[boblarson]

The key phrase here is "It really does depend on the data you expect to collect." If your data is something that you truly have no idea as to the number of items, say a person's hobbies or their music preferences or periodicals that they read on a regular basis, then sure, this needs a separate table. But telephone numbers? Addresses? I'm sorry, but that's just overkill, and the difficulties involved in the development/maintenance of a database expand exponentially with each added table. Apps of any complexity, written with adherence to all the rules but without any input of common sense are, in my opinion, doomed to continuing problems.

I've got several apps that use an extra table for contact info, and you can set it up to hold phone numbers and email addresses which allows you to add as many as you need but it really is no more difficult to manage than any other one-to-many relationship.

 

[georgedwilkinson]

Are you guys baiting?

Bob is absolutely correct: it is just another 1:M relationship and quite easy to manage.

I always assume that the users will come up with additonal "requirements" after the system is built. I can't tell you how many times I've had to go into someone else's database to fix their un-normalized contact information 'cause the user really did want to do things that we should be able to expect them to do after they've used the system for a while. Usually, it's just faster to create it from scratch.

The world is changing and it drastically changes the way people enter data into their databases. Ten years ago, some (few) people were using "mobile" phones, and there was not a way to enter that into many databases at the time. When the software industry finally caught up, "mobile" phones were no longer "mobile" but were more accurately identified as "cellular". Most systems still list them as "mobile".

Also, 10 years ago, everybody had a home phone. Now, my entire family carries a cell phone and it would be redundant and expensive to have a home phone. I don't even know if people still use home phones.

The point is, system requirements change and the person who designs the database isn't the one who has to clean up the mess (think about the Y2K debacle). Since we are presumably smarter at this type of thing than the average user base, best to go ahead and do it right the first time.

 

[Mike375]

I don't even know if people still use home phones.

They certainly do in Australia and one big reason being the cost of calls on a mobile phone both phoning out with one and also the cost to a person who makes a call to a mobile.

As to storing addresses I guess you should not store any addresses in the persons record. But what if the person's home suburb in combination with their business suburb "describes" the person and in fact will be used to select records.

What about a persons name. Some people have a first and last name. Some have a middle name and some have two middle names.

Some people have the same address for mailing, home and business while others have all three different addresses. What if record selection or categorising the person is dependent on how many addresses he has.

 

[georgedwilkinson]

They certainly do in Australia and one big reason being the cost of calls on a mobile phone both phoning out with one and also the cost to a person who makes a call to a mobile.

Another great reason for a flexible (i.e. normalized) design. You never know who is going to use your system.

As to storing addresses I guess you should not store any addresses in the persons record.

I totally agree, except in the case of transitory, ETL, or reporting data. Definitely not in a production OLTP system.

But what if the person's home suburb in combination with their business suburb "describes" the person and in fact will be used to select records.

This is a problem in every system. But it doesn't mean the address needs to be in the same table as the person. A view (query) of that data will help resolve the seeming disparity. And a person certainly doesn't lose his identity if he loses his job or home.

Most industrialized countries have a unique, government assigned number which uniquely identifies that person within that country. Using the name, country, and that number would almost certainly uniquely identify a person within an international system for centuries. By then, everyone will have a cyborg implant with its own serial number.

What about a persons name. Some people have a first and last name. Some have a middle name and some have two middle names.

Correct. In the "person" table, you would have first name, middle name, last name, prefix, suffix, etc. For the cases you cannot anticipate up front, you could have an aliases table, supported by an alias type lookup table.

Some people have the same address for mailing, home and business while others have all three different addresses. What if record selection or categorising the person is dependent on how many addresses he has.

And in addition to that, more than one person can live at the same address. Compound that with the fact that, over time, more than one family can live at that exact same address.

Create a unique address table. Create a table types lookup table. Create a party address table. The party address table has 3 foreign keys (PartyID, AddressID, and AddressTypeID), to and from dates, other stuff you may want to track about the relationship, and possibly a surrogate key. This flexible design allows multiple people to inhabit the same dwelling over time and allows an individual to have multiple addresses at the same time and over time.

Not sure what you mean by categorizing, but the above design certainly can be queried for almost any combination of having addresses or not having addresses at any time in history. However, depending on the requirements of the system, you may want to handle categorizing people a different way. (I guess it's "categorise" in the rest of the world.)

 

[Mike375]

Not sure what you mean by categorizing

Putting the person in a category for record selection. This is vital for a good telemarketing data base as it can determine the number of contacts the caller can make per hour. For example a call is made and the prospect is not available. What is now important is when that record reappears for another call. Thus their category is changing with each call result.

For my use the key factors are record selection, display and ease of changing the display. My measure is how many contacts were made per hour and how many contacts were required to get an appointment.

Do you know that if the caller has doubt about the record selection and he thinks....did I juist call him an hour ago etc....he hesitates on each record which reduces the contacts per hour also increases the contacts to gain an appointment.

See if you reckon you can normalise this situation. When a call is made there are 3 sets of data. One is the results, could be "unavailable", "wrong number" and so on. The other is the date/time of the call. Sometimes there is another set of times and weekday.....these are where a secretary might say something like...only call on Tuesdays and Fridays beteen 9am and 11 am. Thus that record will only appear on those days and times. Or it might be reversed to a..don't call on etc...

As the caller makes his "clicks" these details go into fields in the main table. The date/time of the call is for 5 attempts. Thus 5 fields for it. The other time/weekdays and other times are always only the last call. When the caller clicks "next prospect" the data just stored goes into a Many table, thus a recordfor each call. However, I display the 5 last call date/times from the fields in the maintable.

I need the 5 dates are on a row because it relates to record selection as do the "only call on Tuesdays and Fridays etc...

I tried as an experiment to normalise but I could not do it. However, I think the reason (apart from display problems/subforms) is the Many table that stores each call does not relate to the prospect. It is related to the telemarketer, types of occupations called, localities etc.

You can get a headache with this stuff

 

[Rabbie]

Also, 10 years ago, everybody had a home phone. Now, my entire family carries a cell phone and it would be redundant and expensive to have a home phone. I don't even know if people still use home phones.

If I didn't have a home phone I wouldn't have internet access. My broadband connection uses the telephone lines. Also I need it for Fax etc.

 

[georgedwilkinson]

This sounds like a really fun project. I'd love to answer all your questions but I'm afraid it'd take me several hours and I'm already into this one for a lot of time...I gotta bill my clients for my time.

However, it sounds like you've kinda given most of the information needed to normalize...read through your last post and look for the nouns and events that you need to capture information about. Those are good candidates for a table/entity.

For instance: category, contact/call (an event), caller (a type of person), prospect (a type of person), call result, appointment, etc.

When you have your list, you may find that you can consolidate some of the candidates by adding a "type" or "status" field (like caller and prospect or contact, call, and appointment). But don't be afraid to keep them separate for your own reasons (performance, design considerations, asthetics, etc.).

Post back your results when you're done and we'll take a look. Often, I tell people that their problem is easy and will only take a little while to solve...not in this case. You have a lot of work to do, and being familiar with the business/problem domain, you might be the only one who can do it right.

I'm very excited for you and I wish I could work on this project. It looks fascinating and is not fully described in the public domain of data modeling (though it is very similar to the CRM model).

 

[Mike375]

I got interested in it because recently I have been helping a friend in the same business make a data base that is essentially the same as this one. Because I started making this one in 1996 and learning Access at the same time, mine is full of band aids etc. So I thought we would try and make it as close to Access rules as possible.

There were a couple of areas we had to stick to some tables where each record would have half the fields null. That was because of how we receive insurance policy benefit updates from insurance companies.

In the posted example above we never join the prospect's table to the call results table. It is always been linked to telemarketers or occupation categories and name list suppliers etc

In reality the date/times of each call are two different things that just happen to be the same set of characters In the Many table they are about the telemarketer's activity, about call results Vs Day of Week or Time of Day, in other words all the statistical information that tells which are the best days or times to call certain occupations and which days/times are the best and worse for the telemarketer etc. and etc. We even export that table to Excel and send it to name list suppliers because of credits on "wrong phone numbers" and "person no longer works there". So while the Many table has all the looks of something that should be related to the prospect it in fact has nothing to do with the individual prospect.

For the prospect those dates/times/days are in reality just an extension of occupation, gender, locality etc in that they determine record selection.

Those dates/times/days as a description of the prospect are also passed to the diary when an appointment is made so the salesman can see the history of the calls.

Perhaps the biggest reason that all is not how looks is because the prospect is only a temporary record where as the call results are a permanent record. 99% of the prospects in the table are there for no longer than a month. They have either been a waste of time, wrong phone number etc or they go onto become an appointment in which case they not only move out of the "call calling area" but they in fact move to the salesman. Approximately 19 out of 20 prospects go into the "reject table". We have to move them out of the main table because otherwise the number of records would become huge and in addition once they have hit the reject mode the name is of no use. Actually they are recyled after 6 months and with all previous call data removed except where the prospect has said "don't call again".

While the call results Many table is a permanent record we do in fact archive it every 2 months. It would also get far too large too quickly. Plus archiving each two months matches our 60 day "goal period" for telemarketers.

Apart from the above the biggest problem with using subforms is the display and doubly so since we change it around. While I have several versions of the main calling form and a "a rename" that does not cover every change. Often the telemarket might want the call dates in a different position. Since I have done telemarketing and do telemarketing I know exactly what he is talking about. If he has a bad night with his wife and wants to see the form upside down then that is fine by me.