Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    12

    Unanswered: Automatically sequentially increasing ID number in table grouped by data in field

    Hi. I have this Access 2007 db that I'm building to track multiple projects' progress. One of the tables I've created is for a RAID log. Ideally, I'd have a new RAID table created for every new project entered into the db but I'm not good at VBA and I've been told not to use VBA if I want to put it on SharePoint. So, instead, I have one RAID table for all projects. I can easily query the table for each project's RAID log but I want to have a sequential ID number for each project's RAID item.

    I'd like it to look like this:

    ID ProjectName RAID
    01 ProjOne blah blah
    01 ProjTwo Blah
    01 ProjThree Blahhhh
    02 ProjOne Another Blah
    03 ProjOne thing
    02 ProjThree ISSUE
    etc.

    I thought of using DLookUp somehow but I couldn't figure out what the expression was that I needed. In Excel, I could do it pretty simply but this solution is eluding me. Could someone help me figure this out?

    Thanks!
    Joker

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its down to you data design
    ...probably:-
    ProjectID
    RAIDID
    ....
    pk a composite of ProjectID and RAIDID

    dmax is probably the domain function you need
    next RAIDID would be something like
    Code:
    dmax("RAIDID","mytable","ProjectID = " & requiredprojectID) +1
    ..supply a value in place or requireporjectID. it assuem that project ID is numeric, if its string then delimit with ' or "

    ...but you'd need to cater for a null value if the dmax function found NO rows matching the project ID
    Code:
    nz(dmax("RAIDID","mytable","ProjectID = " & requiredprojectID) +1,1)
    as to how you implement that within a macro beats me as I don't use macros
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2010
    Posts
    12
    Thanks! I'll give it a try and see how that works.

    What if I didn't want to save the number values in a table and only wanted to just display them in a query or report? Do you know if there's a function that will do that within a select query?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does the rtaid number have any value or merit within your organisation
    would it matter if an item was reported as, say #11 one week and #937 next week?
    if not then you don't need to store the information then don't, but if you dont' store it then DMAX won't work for you.

    if it does have some meaning (ie if its #937 and should always be referred to as 937 then store the infiormation

    you should NEVER rely on an inferred sort / colaltion sequence in a database. grabnted sharepoint LISTS may be different, but in a DB context you should always be specific
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2010
    Posts
    12
    Quote Originally Posted by healdem View Post
    does the rtaid number have any value or merit within your organisation
    would it matter if an item was reported as, say #11 one week and #937 next week?
    if not then you don't need to store the information then don't, but if you dont' store it then DMAX won't work for you.

    if it does have some meaning (ie if its #937 and should always be referred to as 937 then store the infiormation

    you should NEVER rely on an inferred sort / colaltion sequence in a database. grabnted sharepoint LISTS may be different, but in a DB context you should always be specific
    The only value it would have would be to denote which item one was referring to in meetings. So we can say, "Item 12 hasn't been updated in a couple of weeks. Can you provide one?" etc. Otherwise, there's no inherent value. There's already a auto date stamp for each added RAID item so they can all be ordered by date regardless of the number provided.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id recommend that you do store the raid id, if for no other reason when referring to an item at a meeting the index will be relevant over time as progress is chased. If the index changes it allows for confusion.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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