Individuals should have their own entries even if they share the same address, because each person may move to a different address in the future.  You can design ways to save time in entering the same address over and over, but the actual records have to be different for the people.

Kevin, each person does have their own record. The address table, with a many to many relationship connected by a junction table allows for this without duplicating addresses. The individuals can always change their address. In fact, this setup allows me to track the address history which is a requirement. I would not delete an address, only mark it as not current. The current address would have the current flag set to true.

Regarding your original goal, it would be tough to keep a list of unique, "standardized," normalized addresses because addresses are pretty "free form."  "101-58 Main Street" can be written as 10158 Main St, or Str, or just Main (omitting the "St").  "4978 N. University Dr., Lauderhill, FL 33351" and "4978 North University Drive, Fort Lauderdale, FL 33351-2468" are in fact one and the same, yet there is no exact text match in the street name, city, or zip code.  How would you tell they are the same other than with your own eyeballs?  It would be more time-consuming to maintain an address list like that than to have address fields in every record, duplicates and all.  Perhaps that's why you are asking for professional help.  Short of devising an addressing "system" the way the Post Office does, I don't see another way.  If your ultimate goal is just saving space on your database, consider whether the saved space would actually commensurate with the time and cost you would have to spend on developing this.

If you wanted to just have the ability for a customer to have multiple addresses, then you would be dealing with a simple one-to-many relationship and you can put in as many addresses as you want for just that customer, much like I did with my multiple phone numbers video. However, once you get to the point where you want to be able to have multiple people share the same address, now you're dealing with a many-to-many relationship which gets more complicated. I can see what Kevin is saying where if the address isn't entered exactly, you might get two people with what looks like different addresses to the computer but are really the same address. So, to that, and I can only say it's only going to be as perfect as the data entry, but you could still maybe let someone search or look in a combo box to see if that address is already in the system before they type it in. But that's more of a user issue than a design issue. A few people have asked me how to do something like this previously, so maybe I'll put together a TechHelp video showing just the basics. So, hang tight, I'll put it on the list.

I'm working on exactly the same issue. It makes no sense to put the same address into an Employee table for 1,000 employees at the same work address, Nor for five family members in the same household. The USPS, and I would imagine other countries, have a standardized address, that can be checked online for correctness for 99.6% of all US addresses at any time. Even worse, is here in Colombia, I have to take addresses down to an atomic level, which has about (at last count) 12 different fields not including the city or departamento (= state). I cringe when I see a db only allow for 1 address and it's buried in the "person" table. A person is not an address.

Having 5000 customers with the same address is no different from having 5000 customers ordering the same item, same quantity, and same price, in which case your order table will have 5000 entries with identical item, unit, and price.  Do you complain about that there?  Of course not.  Those duplications are a *realistic* representation of what is taking place.  Likewise, you should not complain about having 5000 identical street name, city name, state, etc.

@Kevin, you are forgetting the basic principles of normalization (i.e. Nf1 & Transitive Dependency). It's not to aviod duplicating data, that's just a small part of it all. How can all those customers share one order? But people can share one address.

Having duplicated data doesn't necessarily violate normalization if the duplication is merely *incidental*, which is the case in my example, and yours as well.  People ordering the same items and people living/working at the same address are both incidental occurrences, and are not something to be corrected.  The only duplicated data that need to be corrected are erroneous multiple entries that should've been entered as one entry.  Are the people living at the same address an erroneous fact if they in fact live at the the same address?  Of course not.

The picture below shows an order from my old job in which we sold the same style, color, units, at the same price to hundreds of stores all at once, and that is how the table looks.  This is just the way it is.  If it is an accurate representation of what took place, then it's okay for a table to look like this.  If you worry about database size, we did this for 20+ years and our database barely exceeded 400 megabytes, nowhere close to the max size limit in SQL Server Express.

Wow.  Let me see if I can simplify.  Addresses are selected from a combo box based on the Address table. Example: Father1, Mother1, Child1, Child2.  All live at the same address. I enter address for Dad into Address table.  I then link Mother1, Child1, and Child2 to this address via the EntityXAddress junction table. (So far, only one address in the Address table.) Grandmother moves in but Grandfather stays where he is. I link Grandmother to same address a Father1 with the EntityXAddress junction table. I add Grandfather's address to the Address database.  I now have 6 entities but only two addresses in the address table.  I understand there is always the issue of what is entered for the address and that could vary (most typos can be corrected before the address is committed) but that is always an issue with data entry. That is a training issue but since the address is being looked up, it can help to minimize those errors.  I have been able to construct the system I just described.  The problem I have is getting this to work with ABCD properly.

One more thing to help clarify: each of the sample people has their own EntityID.

Hi Donald, when you "codify" certain data, you need to use the codes many many times for them to be worthwhile, not 6 times as in your example.  This is why we have social security numbers, because we use our SS# many many times in our lives to apply for jobs, benefits, loans, etc.  This is why states are given state codes (NJ, NY, FL, etc.), because we use them millions of times.  If you don't use your codified data enough times, you don't get the benefit, and you have the added complexity of having to manage the codes, junction tables, etc.  In your example, other than those 6 people, who else are going to use that address?  No one.  People also have work addresses (sometimes multiple ones), and each work address is likely linked to just ONE person, unless your whole family work at the same place.  So again, this whole endeavor is not needed, from what I'm seeing.  Addresses already have state codes and zip codes, and that is enough codification already.  You don't need to codify them any further.

When you fill out application forms, you are often asked to enter many codified pieces of info: SS#, driver's license#, insurance card#, etc.  And yet you have to fill out your address in full.  That is because there is no code for addresses.  We would love to write just a 10-digit code instead of the full address, but that just isn't how the world works.

Kevin, you lost me. I am not trying to codify the addresses, I am only linking one person to multiple addresses and one address to  multiple people with the address selected by a combo box. Unless I am mistaken, this is a basic many to many relationship. What I have works fine as I stated earlier. My issue is getting this to work with the ABCD setup that Richard uses in ABCD. I need to be able to track the address history for each entity and flag which are current. I also need to ensure that there is only one current flag for each address type (work, physical, etc). I have other forms I have designed for ABCD that work without issue. If anyone is willing to help with this adaptation of addresses to ABCD  please let me know. Thank you.

I realize now that I may have confused my needs unless you have ABCD and know its structure. For the ABCD people out there, I want to change the AddressF, where Street1 becomes a combo box for a selection from the AddressT. The bottom line is to change this relationship from one-to-many to many-to-many. Again, gratis or fee-based is fine with me.

@Donald I wouldn't use a combo box for the street address, I would use a search form. I'll show an example below if the Window's capture will allow it (which I just discovered it won't, so better to watch the video, but there is a right click option that pops up for the Entity Relationship Forn, Entity of relationship text box). It's a many-to-many back on the same table, but the idea is the same. Look in the Developers Template marketplace for more examples.

I understand Kevin's points, I lived with them for thirty years, imposed upon me by others, and every month I had to deal with dozens of updates to addresses (numbering in the hundreds of thousands of records) that had no way of maintaining the historical activity. Which then became hours of research, at times in old backups, until I could justify sending a check to an address that the client no longer had in their database either. I'll stick with the simple observation that a person is not their address and vice versa.

I've watched the ABCD videos, but I don't have a copy, and I can't comment on what problems Richard may or may not have been addressing with his ABCD design.

The key fields in your EntityXAddress junction table -- THAT is the codifying you did.  You assigned key fields (the"codes" in question) to the addresses in order to make that junction table.  My point is that you don't do that unless it's actually beneficial.  Using a home address only 6 times total (and work addresses possibly only once per person) doesn't really warrant all that extra work.  If you insist on doing this, I won't stop you.  I just want you to have the necessary info.

Databases are ALL about codifying information (when to do it, when not).  Coded information facilitates basically all database operations.  This is why we are given all sorts of codes in our lives: SS#, ID#, employee#, credit card#, etc.  And we use these codes millions of times, not 6 times.

@ Kevin To expand on the example I previously gave, addresses weren't just used "6 times". I sent thousands of checks each month, some for over a million dollars out to the customers of my client (Ford among many others). Imagine Ford paying a local dealership for the advertising that the dealership did of Ford's vehicles in all kinds of advertisments. The dealerships didn't move much, but they often changed the address where they wanted the check sent. Smallers stores (customers of my client) for other clients often moved around a lot, maybe 1,000 + plus records changing each month for all the clients.

My first client, because of their large IBM systems insisted that I put the customer's address inside the customer record. I'll never again let a shortsighted client mandate cripple my products for the next thirty years.

@ Donald To finish off my example. I'll include a capture of the 'junction' table and the form for my previous example capture. For the addresses, I'll create an Address table, and then a EntityAddress table to serve as the 'junction' table. For the new 'junction' table, I'll just change the Related entity column to one for the Address ID and probably get rid of the two geo (geography) columns as they won't apply for addresses.

@ Donald You have the concept correct in your 12th post (starts with Wow). I'll be happy to help with the problem as it a much simpler version of what I have to deal with. I don't think the site's posting rules allow us to contact each other. Richard can do that if he wishes.

I don't know how big your database is going to get, as far as addresses, but you may want to check out Richard's Access Search Template. And then incorporate that concept back into the junction form somehow.

Thomas, if you'd like me to add you to the consultants section on the Developer Network page, I will. Then Donald would be free to contact you there by email. I'm putting a video together today on some alternatives and options that I've used in the past - and you all make good arguments. In the end, it's really just what's best for the customer's business.

Hi Richard, yes, I guess that would be best. In the Other Access Consultants section. I'll send the data to your email.

Thomas, please let me know how to contact you once things are set up.  Also, since you do not own ABCD, I need to know if Ricard has authorized you to have the segments of code you will need to know for my system.  Thanks.

I have his info, but I haven't had time yet to add him to the Consultants page. His email is [email protected]. Feel free to contact him at your leisure. My general rule of thumb is that if a consultant is going to use significant portions of one of my databases for your project, then you should have purchased that database (e.g. the ABCD or a seminar or whatever it may be from.) You're on your honor, of course.

Thank you Richard. I own ABCD,  Thomas does not. I think I have actually found the issue. I believe my EntityXAddressQ is too complex and not updatable. Back to the drawing board. Hopefully Thomas can help.

Thanks Richard, I'm assuming Donald purchased the ACBD, or how else would he be using it?

Touche. If I took the time to re-read every thread from the beginning, every time I hop in the Forums, I'd be here 25 hours a day. LOL

But I have had to politely cut ties with consultants before who had purchased some of my seminars and templates and were essentially customizing and reselling them basically "as-is" with some minor cosmetic additions. I don't mind if people customize my work for their clients, provided that client has purchased the database from me. It's only fair.

I just wanted to thank everyone for their input here. Thomas and I connected and had a good discussion, after which I looked closely at my code and found where I was making my error. HINT: when looking at SQL statements, use F2 to see the entire code, not just the beginning. I am not saying my being a bit lazy was the issue - but I am just saying. Anyway, the AddressF in my ABCD now handles many-to-many relationships.