# Thread: Automatic Client #... Calculated

1. Registered User
Join Date
Sep 2006
Location
Massachusetts north coast
Posts
8

## Unanswered: Automatic Client #... Calculated

I hope I am not overstepping my bounds asking how to do this... I am not a programmer but can play around enough to get into trouble.

I have a client database and want to have the program automatically create a client number which is derrived by calculating the average of the client number before and after the new client. ok... it looks like this:

Client#...... Name

104,223..... Smith
???? ..........Turner (new client)
156,219.... Vasquez

I want the program to calculate the missing number which is the average of 104,223 and 156,219... or 130,221. I have the button created which will input the client information... name, address etc. Upon hitting a button on the form I want the program to put the new client into the list alphabetically AND create the number. Can this be done?

I would be willing to create a whole new form which is simply designed to calculate this number... I can then take the number and input it into the new client form manually.

In Excel I created a macro which does this. I figure where the client fits in the spreadsheet, place my cursor on the cell after the place the new client will be and whamo... it creates the number for me.

I appreciate any help... My version... 2000.

PATA

2. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Playing devil's advocate here ... What do you propose to do if (and when) you get 2 or more clients with the same last name???? Ex:

123456 Public, John Q.

??????? Public, Suzy T.

999999 Zorro, Alfonso

Or better yet, 3 of the same last name where the 3rd slots between the other 2 ... or does it????

Generically, I see a query with the ordering by name then number retrieving the 1st and subtracting the 2nd then divide by 2 ...

3. Registered User
Join Date
May 2005
Location
Posts
2,888
Good point on the names. Generically, I saw a function with recordsets that found the ID's of the clients with the name on either side of the input name, then did the math and returned the average.

4. Registered User
Join Date
Sep 2006
Location
Massachusetts north coast
Posts
8
M Owen... good point on the name. These clients are businesses not individuals although some of the clients ARE individuals (self employed individuals which simply go by their name). I was thinking of having a new field which would be the field which ultimately would be the alphabetizing field... the database would use that field and not the actual client name field... a field which would act as the proverbial "last name".

You both have hit the mark and are thinking what I want to do. But how do I do it? Am I asking too much... do I really need to know more programming? Is there a wizard which will do this or am I forced to create it myself

hmmm ... thinking.. I have the form which I hit the "new client" button and the client input screen pops up empty. I want to then input the info and then hit a button which will place the new client inline alphabetically AND then create the client number. The client number field alreade exists... but it is a field that is inputted... not automatic.

doable?

Thanks!
Last edited by PATA; 09-14-06 at 18:25.

5. Registered User
Join Date
May 2005
Location
Posts
2,888
I can't imagine any wizard that will do this for you; you'll need to create it yourself. My way would require code, though nothing too complicated. I don't have in my head exactly what M Owen has in mind, so perhaps his would be better.

6. Registered User
Join Date
Sep 2006
Location
Massachusetts north coast
Posts
8
umm... but it couldnt be done without havenig the whole program?

7. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally Posted by PATA
M Owen... good point on the name. These clients are businesses not individuals although some of the clients ARE individuals (self employed individuals which simply go by their name). I was thinking of having a new field which would be the field which ultimately would be the alphabetizing field... the database would use that field and not the actual client name field... a field which would act as the proverbial "last name".

You both have hit the mark and are thinking what I want to do. But how do I do it? Am I asking too much... do I really need to know more programming? Is there a wizard which will do this or am I forced to create it myself

hmmm ... thinking.. I have the form which I hit the "new client" button and the client input screen pops up empty. I want to then input the info and then hit a button which will place the new client inline alphabetically AND then create the client number. The client number field alreade exists... but it is a field that is inputted... not automatic.

doable?

Thanks!
Again ... Customer name/client name, all the same to me ... Just a name. Here's the question YOU need to answer: How do you resolve ties???? You want the client/customer to be ordered alphabetically ... Say you have 2 locations for ACME Warehouse that you do business with ... Obviously with different addresses possibly on different streets, cities, and/or states. Unless you bake that address/location difference into the name (ACME Warehouse #2) ties will be the death of this thing ...

8. Registered User
Join Date
Sep 2006
Location
Massachusetts north coast
Posts
8
That will never happen. My clients are all individuals. I do not sell a product to distributors... I am not like a physician with multiple offices... I perform one task for each individual client. If there is more than one location the 2 locations would be grouped and then considered one. Address means nothing.

You DB guys are thorough though. Experience is definately showing.

I am not that knowledgable in this area. As I said, just enought to try things and if it works... great. enought to be dangerous. Always working on a backup copy.

You had mentioned querys... I looked and have no clue where you were going with that.

I have an excel SS that has a macro which does it. I place the cursor on the client number after where I want the new client to be inputted.... Run the macro and it calculates the new number. Not that difficult. Problem is that I am performing the input of the client data twice... once in the excel form and once in the access form. Wouldnt need the excel form if I could do it once in the access db.

9. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
AGAIN: Not answering the question ... HOW do you want to handle ties????? Per your explanation: You have Joseph A. Banks and Carl Banks as clients. Joesph is in your listing currently. Along comes Carl ... Where should he slot in the list? This will go a LONG WAY towards designing your cient # solution ... It's up to you. I'll not ask you again.

10. Registered User
Join Date
Sep 2006
Location
Massachusetts north coast
Posts
8
well, Carl would come before Joseph. Sorry, I thought I answered that earlier... I would have the token "last name field" in which case Banks would be inputted. I see your point that if only Banks was inputted then Joseph could end up before Carl.

2 fields...

"Last name/company name" ___________________
"first name"______________

If I have a client that uses a company name simply input in the LN/CN field. If a client uses their name then input FN and LN/CN fields. Or would there be a problem with multiple words in the LN/CN field.

11. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally Posted by PATA
well, Carl would come before Joseph. Sorry, I thought I answered that earlier... I would have the token "last name field" in which case Banks would be inputted. I see your point that if only Banks was inputted then Joseph could end up before Carl.

2 fields...

"Last name/company name" ___________________
"first name"______________

If I have a client that uses a company name simply input in the LN/CN field. If a client uses their name then input FN and LN/CN fields. Or would there be a problem with multiple words in the LN/CN field.
NOW we're getting somewhere ... OK. Putting together the name(s) such that you'd have:

Banks, Joseph A.
Banks, Carl

Then you'd issue a compound SQL query like:

SELECT TOP 1 ClientNumber FROM SomeTable WHERE (SomeTable.Name<='" & MyTargetName & "') ORDER BY Name DESC
UNION
SELECT TOP 1 ClientNumber FROM SomeTable WHERE (SomeTable.Name>='" & MyTargetName & "') ORDER BY Name ASC;

to give you the lastmost record less than the new name AND the firstmost record greater than the new name ...

Then its a matter of reading those records and calc'ing the newclient # between ...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•