Results 1 to 11 of 11
  1. #1
    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. #2
    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 ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  4. #4
    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. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

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

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote 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 ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    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.

    Thanks for your help guys.

  9. #9
    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.
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    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.

    Would this solve my problem/answer your question...

    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. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote 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.

    Would this solve my problem/answer your question...

    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 ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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