Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Advanced Microsoft Access Question

Options
  • 29-04-2007 11:38pm
    #1
    neon_glows
    Closed Accounts Posts: 873 ✭✭✭


    I am posting this here even though it isnt a programming query but a database query, how ever the complex nature of the question i feel is more appropriate to this forum.

    This is part of a college assigment I am working on and is not a set question i must complete, it is more of an advanced feature i would like to incorporate into my database, i was unable to find a solution online for this question so felt i should post it here in.

    it might be that access will not allow you do this.

    Im trying to make a field called location that initially starts at value SA001 and keeps incrementing to value SA999 and then goes to SB001 to SB999 and same with SC but not any further than that, its is to simulate nearly 3000 storage locations in a mock warehouse.

    Nicky


Comments

  • Options
    #2
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,871 Mod ✭✭✭✭Seth Brundle


    One way is to set up a small procedure to update the table by taking the autonumber primary key field and checking it it is less than 1000, 2000 or 3000, casting it as a string and then concatenate the right three digits against the SA, SB or SC.


  • Options
    #3
    bonkey
    Registered Users Posts: 15,443 ✭✭✭✭bonkey


    A lot depends on where you need access (pun!) to this field.

    If you need it in a SELECT statement, you can (working kbannon's idea a bit further) dynamically calculate such a value from the autonumber field.

    One such strategy would be to head down lines like this :

    (autonumber mod 3) gives you a value you can use CASE with to generate SA/SB/SC.

    ((floor(cdbl(autonumber)/3)) mod 999) + 1 gives you a 1-999 value.

    You can combine these together into a single field in yoru select statement.
    Ugly on performance (calculating at select, rather than on insert), but functional.

    Alternately, you could calculate it post-insert on each record (once it had an autonumber assigned to it) using a similar strategy, and then be able to select it back efficiently.


  • Options
    #4
    neon_glows
    Closed Accounts Posts: 873 ✭✭✭neon_glows


    Thanks for the advice guys, i got it working but had to alter it around alot, just to make it an autonumber between 1- 300


  • Options
    #5
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,871 Mod ✭✭✭✭Seth Brundle


    Why 300?


Advertisement