Design to Avoid Repeating Addresses

[Les Simpson]

Hi all. A newbie here. I have been working on a membership database in Access 2007 for our gliding club and am seeking to avoid repeating addresss.

I cannot get this to work either in tables or in Forms.

This is what I have done so far:

4 tables:

Member
ID field autonumber Primary Key- One to Many
and all the usual membership fields

MemberAddresses
MemberID field ForeignKey to the Primary key ID in Member table above
AddressID field ForeignKey to the Primary key in Adresses table below
AddressType field I used the selection wizard for address types. This is the many side to one record in the AddressType file below.


AddressType
ID Field autonumber. The one side of many in AddressType field in MemberAddresses.

AddressType field. 3 records, Home, Work and Next of Kin.

Addresses
ID autonumber Primary Key- One to Many in MemberAddresses table
And all the usual address fields.

Would appreciate a steer as to why this does'nt work.

Les

 

[Kryst51]

What's not working about it?

 

[Les Simpson]

Many thanks for your very quick reply Kryst.

When I put in dummy address, say an address with "Home" selected thats OK fine. But when I select Work or Next of kin the Home address remains. I want the address field to clear so that I can input a new address.

I am travelling without lights on this.

All the best

Les

 

[Kryst51]

OK, it sounds like you aren't advancing to the next record, you need to either have a button to submit the record or some after update event....

What happens if you tab through the form?

 

[Les Simpson]

Sorry for the delay Kryst.

I have deleted the whole Form. Will write a new Form tomorrow then I'll tab through as you suggest.

Do you consider the underlying tables and relationships are sound? If so, then I can eliminate that.

All the best

Les

 

[Kryst51]

Your table set up sounds OK, but without seeing it completely, I can't say for sure, also a lot of that is determined by function, which only you would really know.

You should have tried what I said before you deleted your form. Maybe we could have come up with a solution without you having to start over. But, too late now, eh?

I'll wait for your reply.

 

[Les Simpson]

I'm a trial and error man Kryst. Ripped it up a will start again works sometimes. Can usually spot the obvious but not in this case;yet. Again many thanks for your Help. By the way my great grandfather made it to Fort Griffin TX near Albany just after the Civil War. He fought on the Union side. He never returned home. Small world.

All the best

Les

 

[Kryst51]

I'm a trial and error man Kryst. Ripped it up a will start again works sometimes. Can usually spot the obvious but not in this case;yet. Again many thanks for your Help. By the way my great grandfather made it to Fort Griffin TX near Albany just after the Civil War. He fought on the Union side. He never returned home. Small world.

All the best

Les

That is an amazing piece of family history to have! Thanks for sharing it.

 

[The_Doc_Man]

I have a separate issue to raise here, one of a different nature than the selection process.

How often do you find addresses that match other addresses such that you would want to reference them rather than just duplicate them?

Let's say your club has 50 members. All of them will have a home address. Some will have next of kin. Some will have work addresses. At most you have 150 addresses in that case. Your odds favor less than 150 addresses, though, since it is theoretically possible that you have people whose spouses work but they don't, so no work address. You have people for whom next of kin either doesn't exist or they demurred providing that address. What's left is say 120 addresses, just guessing. How many of them will be duplicated?

If that number is more than 10% I would be very surprised unless your club is taken almost exclusively from the employees of a single company.

Given that Access tables are "sparse" (not always fully populated), you save enough space by just having a simple child address table where you have the person ID, ACTUAL address, and address type code, rather than that extra layer of reference to an address list. If an address was not given for work or next of kin, you just don't have that entry.

I question "over-normalizing" tables UNLESS you know that you will have a significant percentage of repetition of the data therein. Normalization is good, but the possibility exists to have too much of a good thing. See, there is the "hidden cost" of normalization - the overhead of YOUR PROGRAMMING TIME to track the extra levels of relational pointers. Not to mention that when you start layering the required joins, Access can get a bit ... pi$$y about the order of joining.

Please don't take this comment as me trying to be harsh. I tend towards being more pragmatist than purist. I always look at "level of effort" or perhaps look at cost/benefit ratio. The more relations you have, the more work you must expend to maintain them as your DB grows, so there is your cost issue (sadly, very hard to quantify).

Counter-example: Your club has 10,000 members taken almost exclusively from the employees of no more than three or four companies. You would expect a lot of value returned from normalization in that case because the list of unique work addresses would be very small.

 

[Les Simpson]

You are are right on the nail with that advice Doc Man and I really appreciate that steer. I had thought about simpler approaches but I wanted to climb the overhang with the big boys to see if I could do it. I did seek a universal solution to push my boundaries.

I think I will go back to one child Address's file containing all the fields needed to get my database working.

Many thanks for the time and effort you put into your note.

All the best

Les

 

[The_Doc_Man]

No problem. We all had that learning curve about when to do XYZ and when to find another way.

If experience means the ability to recognize mistakes when you make them again, I may well be one of the most experienced guys on the forum.