Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Posts
    38

    Unanswered: Add to Max Value

    I have an append query that appends to a table that requires an ID.
    When I append the data I also have to give these records an ID number but I need an expression that will look at the maximum number and add one to it.
    Currently I'm using the following Expr1: DMax("[Participant ID]","Application2")+"1", when I run the query it does find the max number and adds one but all the records have that same number. How do I increment it in my query?

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Not sure how to do that without resorting to code.

    I do have a sample database that shows how to create a field in a query that show a running count of the records retrieved. You could use that running count to add the max found by DMax. Sample database is here. This is not the optimal way to do it at all, I did the sample merely to demonstrate what can be done.

    It would be much better to do this using VBA code and DAO or ADO.

    What are you using the append query for, to add several (or a lot) of records all at once to a table?
    Last edited by Cosmos75; 07-29-04 at 10:14.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Oct 2003
    Posts
    38

    Smile

    Thanks,
    We have an on-line application that we would like for applicants to fill out before they come in for their appointment. So potentially, I can have lots of records at one time. When they fill out their on-line application the information is sent to a text file and then I import it into Access. I can't dump it into the preferred table directly because it needs the Participant ID numbers. (Well I haven't gotten it to work properly, that way.) So I bring it into a new table and then use the append query to dump it into the appropriate table.

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Interesting, I actually had someone email me who was doing the same thing! He needed to create an incremental field that started at a number of his choice. He was pulling records from SQL Server to Access to manipulate then dump back into SQL Server.

    I'll have to dig around for the sample file that I created for him that used DAO to create the numbers for that incremental field that started at a number of his choice. If I can find out where I stuck I'll post it here it you think that is what you need. Just post back and let me know.

    Or you can try the query method and see if it it performs what you want quickly enough and without hogging system resources. I hope that between the two you will find a solution or perhaps someone here knows of a better method and we'll both learn something new!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Jan 2004
    Location
    Islamabad, Pakistan
    Posts
    97
    Hi!... mfernandez

    Try changing Expr1: DMax("[Participant ID]","Application2")+"1"

    to

    Expr1: DMax("[Participant ID]","Application2") + 1 and see if it works for you...

    - Saqib

  6. #6
    Join Date
    Oct 2003
    Posts
    38
    Cosmos75, If you can post the sample file that would be great, it sounds like what I might need. I'm willing to try anything at this point. Meanwhile I'll play around with the queries and see if I can get them to work the way I need.
    Thanks

    Saqib
    I tried the expression but it gives the same results. My query has three sample records and the max value from the table is 4026 so when I run the query the all three records have the participant ID as 4027.
    Thanks

  7. #7
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by mfernandez
    Cosmos75, If you can post the sample file that would be great, it sounds like what I might need. I'm willing to try anything at this point. Meanwhile I'll play around with the queries and see if I can get them to work the way I need.
    Thanks
    Found it! Here it is in Access97. Should be able to open it in Access 2000 and later. It uses VBA & DAO to create running calculations (Sum / Total, Average, Count) and to also create a sequential numerical field that starts at 1 or any number you specify.

    Just a thought - you may be able to use DMAX to automatically pull the the max number and resume the sequential number from that number.

    Hope this is what you need!
    Attached Files Attached Files
    Last edited by Cosmos75; 07-30-04 at 14:00.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  8. #8
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by mfernandez
    When they fill out their on-line application the information is sent to a text file and then I import it into Access. I can't dump it into the preferred table directly because it needs the Participant ID numbers. (Well I haven't gotten it to work properly, that way.) So I bring it into a new table and then use the append query to dump it into the appropriate table.
    Re-read that part and had a thought - Can't you just import it directly into the table and have the field Participant ID as an autonumber field? You might be able to just import all the fields witht he exception of Participant ID which Access should (I think) increment on it's own. You would have to worry about where autonumber resumes the increment (see more about that here). Perhaps you have already tried that and it didn't work? If so, let me know, I'd like to know if it does work or not.

    I've never imported data before so I am not sure if that would work or not.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  9. #9
    Join Date
    Oct 2003
    Posts
    38
    Importing data into a table that has an AutoNumber does work that way, where it will increment all on it's own but the table I'm dumping it into to does not have an AutoNumber. The data type is number. At one point this table did use an AutoNumber but the AutoNumber went a little to random than what we wanted. It would give us 10 or 11 digit numbers instead of incrementing in order. So the guy in charge of the database changed it from an AutoNumber to number and used the following expression to have it increment properly Expr1: DMax ("[Participant ID]","Application2")+"1". So I don't think he wants to change it back and I figure I could use this same expression in my query but it doesn't work properly. It does find the maximum value and add 1 but if my query has 20 records all 20 records have that next number. (For example, max value is 4026 all 20 records are 4027, instead of incrementing to 4027, 4028...) So I'm not sure what else to do.

  10. #10
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by mfernandez
    It does find the maximum value and add 1 but if my query has 20 records all 20 records have that next number. (For example, max value is 4026 all 20 records are 4027, instead of incrementing to 4027, 4028...) So I'm not sure what else to do.
    Thats because it is doing exactly is asked of it, to find the max of "[Participant ID]" in "Application2" and add 1 to it. IT isn't tied to the data that you are using for the other fields in the query. Think of it as a calculation on it's own and then being adding as an extra column in your query.

    There is a method to use aggregate functions to accomplish that but it requires a unique identifier for each records (either a primary key, autonumber field or combination of fields). However that is very inefficient.

    See this thread for further info. That is where I learned the query method (2 years ago!). You'd be better off using the sub-select query method from the sample database I first posted.

    I'd suggest testing the VBA/DAO code method and the Sub-Select Query method to see which works best for your situation. Let me know if you do test between the two, I'd like to know what you end up using and why!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi mfernandez,

    I created the same thing you did and avoided using the AutoNumber feature for that specific purpose. I am posting my code right here, but not sure if you'll understand what you see. Possibly checking back this weekend.

    Bud

    Code:
    Private Sub cmdNewID_Click()
    'My first success at creating an auto-incrementing number field
    'This is based on the tblSampleTest table 
    'On click of button a new Customer ID is generated and
    'focus is moved to strLastName field.
        
        DoCmd.GoToRecord , , acNewRec
        Me![lngID] = NewCustID()
        Me![strLastName].SetFocus
        Me![cmdNewID].Enabled = True
        
    End Sub
    --------------------------------------------------------------------
    And then finally this:
    Code:
    'This code finally worked and brought me success in creating an auto-incrementing field.
    .
    Public Function NewCustID() As Long
    On Error GoTo NextID_Err
    
    Dim lngNextID As Long
    
        'Find highest Employee ID in the tblSampleTest table and add 1
        lngNextID = DMax("[lngID]", "tblSampleTest") + 1
        
        'Assign function the value of the Next ID
        NewCustID = lngNextID
        
        'Exit function now after successful incrementing or after error message
    Exit_NewCustID:
    Exit Function
    
        'If an error occurred, display a message, then go to Exit statement
    NextID_Err:
        MsgBox "Error " & Err & ": " & Error$
        
        Resume Exit_NewCustID
        
    
    End Function
    It works well in my database, wish you luck,

    Bud

  12. #12
    Join Date
    Oct 2003
    Posts
    38
    Does this code go on a control on a form? I wasn't sure where to put it.

Posting Permissions

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