Put all address info into one table (A2K)

Topic

New Reply

I have a db with 40 odd tables several of these tables mostly contain address info:

Mostly Address:
Customers
Suppliers
Delivery Address
Pits
Employee Address

So I

Reply | Quote

Replies

Have you tried a make table query ?

Sounds like 40 into 1 is going to make a pretty big table !

How are these tables linked at the moment ?

You could always design one form with different buttons to change the recordsource, leaving the tables as they are.

There are many ways you can do this, its up to you how you see the design and which way will benefit you most.

Dave

Reply | Quote

My concern is that I’ve got similar data in several different tables the solution appears to be to move that similar data to a separate address table. However, this raised the problem of how to identify each address as it relates to the table it came from? I think I’ve resolved this. What I can do is keep the original tables, just move the address information into a separate table then if I need to see the address information relating to say the supplier I could use the supplier table to limit the results to just suppliers addresses.

Comments appreciated.

Reply | Quote

I had a similar situation that I solved in this way. I made a table of all names and addresses called tblContacts and a table of contact types called tblContactTypes.
The fields for tblContacts included the usual, FirstName, LastName, CompanyName, Addr1,…..then a series of fields to indicate which lists they belonged to. ListId1, ListId2, ListId3… to ListId25 (probably breaking table normalization rules but it worked)

The fields for tblContactTypes were ContactTypeID (key field, indexed, no duplicates) and ContactTypeDescription

A form had a combo box with row source tblContactTypes and a filter the returned the records based on the users selection.

Another form was used each time a new type of contact list needed to be added. It never got larger than 25 which was fairly manageable.

Hope this helps.

Reply | Quote

I have been thinking along similar lines but I could see a problem arising if I decided an address belonged to two categories how would I show this?
Reading your post the answer dawned on me! Have as you describe a contact type table and a contacts table now have a many to many table that brings the contact table and contact types table together. Therefore a supplier type address could also be listed as a customer type address.

Reply | Quote

I’ve wrestled with this also, but I always go back to multiple tables rather than a single Address table. For one thing, I’m not sure what problems are solved by having a single table! Let’s say I have vendors and customers. Now, I might have a few companies that are both, and thus could share a single address record; but is this really such a big problem? And even then, often the addresses are sometimes different (one is Attn: Accounts Payable, the other Attn: Accounts Receivable).

One specific problem is data entry. Let’s say I’m entering a new Customer. How do I enter an address for this Customer? I really need to enter it in the Address table, but I also should somehow check to make sure I don’t already have this address. I think this might be a pain (and cause grumbling from data entry personnel).

Reply | Quote

>>>>>>>>>>>>>>>>I think this might be a pain (and cause grumbling from data entry personnel)<<<<<<<<<<<<<<<<<<<<

In an orders database I construct recently I had the opposite problem you describe! I had complaints from the data entry personnel because they were keying in a delivery address for customer on one day, and then on the following day they have to key in the exact same address again so I had to solve this problem. I decided that if I put the addresses in a separate table and gave each address a unique number then when the operator got to the address tab for a new order they would have the option to select an address from the address table with a command button or type in a new address.

This proved to be quite efficient because the orders table only stores the reference number for the address not the actual address. The problem I had was how to display the address on any future order viewing forms. The solution I found was to put combo box on the form, this combo queried the address table, then under this combo box I put several unbound text boxes which accessed the address info in the combo box, Addr1, Addr2, etc with something like

Reply | Quote

What you seemed to be describing is entering a delivery address for the same customer more than once, which really isn’t the situation I was describing (which was entering the same address for 2 different entities). Similar, yes, but there are differences.

Does your combo box list all addresses, or just addresses for this customer? If you have alot of addresses, I could see this as really slowing down the performance of the combo box. In network situations, I try to avoid having big combo boxes like that.

I typically will have a CustomerAddresses table that will have all Customer addresses (with a field to denote billing vs. shipping addresses). But I won’t put Vendor addresses in this table.

Reply | Quote

>>>>>>Does your combo box list all addresses or just addresses for this customer? If you have a lot of addresses, I could see this as really slowing down the performance of the combo box<<<<<<<<<

Ah now we're getting into the issues I need to understand. I have already noted that the fields (unbound) that get their data from the combo box with this column (1) seem to fill slowly.

You are correct the delivery address is entered for the same customer more than once. This has come about because most of the customers are "accounts" but one of the accounts is listed as "cash sales" this "account" which is really cash sales obviously has the most addresses. The genuine account customers rarely have more than 20 or 30 addresses. Your comments are prompting me into a useful train of thought. I may separate the orders process into two systems one for account customers and one for cash customers. Although this could open a can of worms! The main problem I can foresee is that this makes management of the deliveries difficult.

Reply | Quote

Mark,

I’ve *always* used a single address table. Each address inclues a foreign key to the person/entity it belongs to, along with a type indicator (mailing, main, home, head office, whatever). I don’t worry about unique addresses, only about not having to plow through multiple tables to get an address.

Reply | Quote

Charlotte,

How does this work? Say you have a Customer table and a Vendor table, aside from the Address fields themselves, what does your Address table look like?

Reply | Quote

I use a table called something like DBMember. It is the central fact table in the database and it holds a MemberID (autonumber) and a field that tells me whether the member is a person, a company or whatever. The Customer table would have MemberID (Long) as its primary key and so would the Vendor table. The same member could be in both tables if appropriate. The address table would have the MemberID as its foreign key.

Reply | Quote

If I correctly understand what you are describing, if you had a company that was both a customer and a vendor, they would still have 2 member entries and hence 2 address records (even if they were the same). So what is the advantage of doing this?

Reply | Quote

No, the DBMember would have address entries (one or more). Normally, the way I would do it, is to create the DBMember as an organization. Then the Customer table would contain a record for that DBMember with customer-related information and the Vendor table would contain another record for the same DBMember with vendor-related information. You might need a secondary key in the Address table if you wanted to bind the address to a DBMember only in their identity as a Vendor or a Customer. Did I forget to mention that the Customer and Vendor table would also have a unique Autonumber key (not the primary key)? That’s what you would use as the foreign key in Address if you needed to do so.

Actually, to me, there isn’t any real difference between a customer and a vendor, there’s a difference between people and organizations. To to be able to list customers or vendors, I would look to a table like Sales or Purchases and retrieve the DBMemberIDs from there for my list.

Reply | Quote