0

i have a front end in access and back end is sql server 2008

one of the fields is the account number and here are the rules

it is a zipcode like 92111 plus a dash plus a number.

so the first one would be 92111-1, the second 92111-2

this has to do with how many clients we have in the zip code

i would like this zip code to be automatically generated. here is what i need:

  1. the user enters the zip code
  2. i have a stored procedure that checks if this zip code exists already, to increment it: if 92111-4 exists already, then make it 92111-5.

what is the proper way of doing this?

Kevin Ross
  • 7,185
  • 2
  • 21
  • 27
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • Does your procedure add a new account record along with calculating the next number or does it just return the next number? – JeffO Sep 20 '10 at 18:16
  • @jeff: it should a new account, both zip and sequence – Alex Gordon Sep 20 '10 at 18:20
  • i think the proper way would be to develop a web app front end.... – DForck42 Sep 20 '10 at 18:23
  • 2
    There is nothing in this question that indicates a web app is more 'proper' than a desktop app. – JeffO Sep 20 '10 at 18:29
  • 2
    what happens if/when they move to a different location with a different zip code? – DForck42 Sep 20 '10 at 20:43
  • 1
    @DForck42 - Good point. @jenny - you should consider bouncing back this "business requirement" and suggest simply a number. In that case you can use an identity column to generate the account number and not worry about locking tables, computed columns, etc. – Nelson Rothermel Sep 20 '10 at 21:01
  • This is a terrible idea. You're storing two pieces of data in one field, which is bound to cause problems. I also question why you even need this in the first place. What are you going to use it for? Sorting? If so, why does it matter the order the records were added in? Seems to me that you can get a sort order from much more meaningful data without polluting the data in your postal code field. – David-W-Fenton Sep 22 '10 at 18:37
  • Why do you think you need this sequence number? What are you going to use it for? – David-W-Fenton Sep 22 '10 at 18:43

2 Answers2

2

If you're storing both the zip and the client sequence number in a single account number field, you would have to split them apart to figure out the next sequence number in a given zip code.

It should be simpler to store them in 2 fields:

zipcode   sequence_num
92111     4
92111     5

Then derive your account number field with a query whenever you need it.

SELECT zipcode & "-" & sequence_num AS acct_num
FROM YourTable;

Then when you need to determine the next sequence_num, lngNextSequenceNum, within a given zipcode, pZip:

lngNextSequenceNum = DMax("sequence_num", "YourTable", "zipcode = " & pZip) +1

That approach can work fine for a single user application. If your application is multi-user, you need something more refined. However, that requirement exists whether you store "account number" as a single field or split it between two fields.

See Create and Use Flexible AutoNumber Fields for a multi-user approach.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • @hansup: thank you very much. why do i need a diff approach for multiuser? – Alex Gordon Sep 20 '10 at 18:19
  • 1
    You don't want a user to enter a zip code, get an account number, adn then go to lunch before saving the account record. The odds the next person creates a new account with the same number is likely. – JeffO Sep 20 '10 at 18:31
  • @hansup: thank you very much. cant i just have access automatically save every minute or so? – Alex Gordon Sep 20 '10 at 18:42
  • Maybe, but I wouldn't do it that way. If two users are adding accounts in the same zip code, creating a conflict could happen instantly. BTW, I just realized you said the table is in SQL Server. In that case, you might consider a stored procedure, or insert trigger, or something. I was thinking strictly in Access terms. – HansUp Sep 20 '10 at 18:47
  • @hansup: how would i do this with a stored procedure or insert trigger? – Alex Gordon Sep 20 '10 at 18:57
  • @hansup: you seem to be the #2 expert on SOF on access DBs, surely a trigger isnt anything too difficult for you to come up with? – Alex Gordon Sep 20 '10 at 19:51
  • @hansup: ive seen your responses. we both know you are at least #2 after fenton – Alex Gordon Sep 20 '10 at 20:15
  • @hansup to keep you on your toes – Alex Gordon Sep 21 '10 at 15:31
  • 1
    I don't know how to tell you to write a SQL Server trigger or SPROC, either. I'm no SQL Server expert at all, and have never claimed to be. My apps that use it are all upsized from Jet, and I keep it as simple as possible, and in none of my apps have I been required to move functions into SPROCs. I could probably marginally improve performance in a few cases, but I haven't found it to have enough bang for the buck in the cases I've been working with personally. – David-W-Fenton Sep 22 '10 at 18:42
1

I agree with HansUp that you should keep the ZIP and sequence separated. You can create an indexed computed field called AccountNumber which joins the zip and sequence.

The only way I know of doing this is locking the table, selecting max(sequence) ... where ZIP = 12345, inserting the new record, then unlocking the table. However, locking the table means everyone else has to wait, greatly affecting scalability. I can't recommend this, but I don't have another solution.

Nelson Rothermel
  • 9,436
  • 8
  • 62
  • 81