How To Make A Customer List With Extra Entries Per Customer

[Minitman]

Greetings,

I have a customer list of which 70% have only one entry per customer.

The problem is the oddballs:

Customers who change their names (get married)
Customers who change their address
Customers with more then one service address (Real estate agents)
Customers who have more then one phone number or change their phone
numbers
And customer who nave many of these exceptions

I would like to have one record per customer. What I have is a
separate record for any changes that occur.

Is there anyway to redesign this customer list to limit it to one
record per customer?

Any suggestions would be most appreciated

TIA

-Minitman

[Leith Ross]

Hello Minitman,

Add one field for each exception to be included in the record. Expanding the record's number of fields should be easy enough to do.

Sincerely,
Leith Ross

[Minitman]

Hey Leith,

Thanks for the reply.

I am a bit confused, You are using database terminology to apply to a
workbook. I could not get Access to work so I am still in Excel.

The way the customer list is now set up is thus:

| Name | Home Phone | Office Phone | Cell Phone | Emergency Phone | Billing Address 1 | Billing Address 2 | Service Address 1 |Service Address 2 | Map Code

Most of the customers will fit into this template. It's the ones that
have active information that I am trying to deal with. With over 2000
customers I need to stream line the search for a particular customer
in my entry and invoicing workbook. As it is, 90% of the customer are
no problem. But for example, I have 5 real estate people each with 20
properties that they will send us to. Do I set up 5 customers with
multiple service address or do I set up 100 different records in my
customer listing. I thought of using drop validation list for carious
service addresses, but when I finally do transport this system to
Access I'm not sure if that will work (and sure I don't know HOW to
make it work)

It appears that you may have some idea as to how to do this, Could you
share some examples?

It will be of great help.

Thanks

-Minitman

On Sun, 30 Oct 2005 23:32:04 -0600, Leith Ross
<[email protected]> wrote:

>
>Hello Minitman,
>
>Add one field for each exception to be included in the record.
>Expanding the record's number of fields should be easy enough to do.
>
>Sincerely,
>Leith Ross

[Leith Ross]

Hello Minitman,

When a worksheet is set up with Column and/or Row headers, it is in fact a database, often referred to as a list. A Row represents a record and the Columns the fields. You have 256 Columns (fields) per record and 65536 records per worksheet. So, you have plenty of space to work in. This layout can be easily imported to an Access database, when the time comes.

Excel provides a builtin tool to help you manage lists like yours. Its the Data Form. To use it, your list must have column headers (and you do). On the main Excel Toolbar, click Data and in the list click Form. You will see a window that controls to move through and modify your data. There is a limit to the number of columns it can handle, but I am not certain what that limit is. I don't think you will exceed it with your list. For best results, you should alphabetize your list by name. You can do this manually with Excel or add a command button to automate the process.

Back to your question. Add the additional fields for those special customers. Then alphabetize your list by contact name. Then it's an easy matter to scan the properties associated with that customer using the builtin data form.

Sincerely,
Leith Ross

[Minitman]

Hey Leith,

I have a question about the advisability of adding 20-50 new columns
just for 5 customers out of 2000? It seems like such a waste.

I was considering adding a validation drop down menu to the 4
fields(columns) that have additions (each row will be directed to
customer specific sub lists').

I guess I'm looking for a philosophical discussion as to the merits of
the various options.

Looking forward to hearing from you.

-Minitman


On Mon, 31 Oct 2005 11:19:45 -0600, Leith Ross
<[email protected]> wrote:

>
>Hello Minitman,
>
>When a worksheet is set up with Column and/or Row headers, it is in
>fact a database, often referred to as a list. A Row represents a record
>and the Columns the fields. You have 256 Columns (fields) per record and
>65536 records per worksheet. So, you have plenty of space to work in.
>This layout can be easily imported to an Access database, when the time
>comes.
>
>Excel provides a builtin tool to help you manage lists like yours. Its
>the Data Form. To use it, your list must have column headers (and you
>do). On the main Excel Toolbar, click Data and in the list click Form.
>You will see a window that controls to move through and modify your
>data. There is a limit to the number of columns it can handle, but I am
>not certain what that limit is. I don't think you will exceed it with
>your list. For best results, you should alphabetize your list by name.
>You can do this manually with Excel or add a command button to automate
>the process.
>
>Back to your question. Add the additional fields for those special
>customers. Then alphabetize your list by contact name. Then it's an
>easy matter to scan the properties associated with that customer using
>the builtin data form.
>
>Sincerely,
>Leith Ross

[Leith Ross]

Hello Minitman,

Rather than create a Drop Down and the code to use it, why not use a second sheet in your workbook for the specail cases. You can still use the Data Form and make your transition to Access easier down the road. Unless of course, you really want that Drop Down list!

Sincerely,
Leith Ross