Results 1 to 7 of 7
  1. #1
    tact8 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    3

    Sequentially Number Batches of multiple Orders based on date and time.

    Hello. I would hope something like this would be simple. I looked through the forums for a solution, but did not find one.

    I have Orders that are separated into different Batches. I need my Batch column to be filled with sequential numbers indicating the chronological order of each Batch of that Order, based on date and time columns. Right now, I have a blank column in my table called Batch. I need to write a query (or VBA script) that would put numbers in this column starting at 1 for the first Batch that occurred, and then 2 for the next Batch, and so on, chronologically. And when there is a new Order number, the Batch starts again at 1.

    I would like to write a query to perform this. I am open to VBA as well. I have tried both a SQL Query and using VBA, but I feel like I am approaching this the wrong way. I have no problem sorting the data how I need it, but I can't get the Batch column to fill in properly.

    Here is an example of what I need performed:

    From table: DataTable

    Order Date Time Batch
    1000 1/1/2018 7:00 AM
    1000 1/2/2018 5:00 AM
    2000 1/2/2018 6:00 AM
    2000 1/2/2018 8:00 AM
    2000 1/3/2018 7:00 AM
    3000 1/3/2018 9:00 AM
    3000 1/4/2018 5:00 AM
    3000 1/4/2018 7:00 AM

    And the query I need, would produce the following:



    Order Date Time Batch
    1000 1/1/2018 7:00 AM 1
    1000 1/2/2018 5:00 AM 2
    2000 1/2/2018 6:00 AM 1
    2000 1/2/2018 8:00 AM 2
    2000 1/3/2018 7:00 AM 3
    3000 1/3/2018 9:00 AM 1
    3000 1/4/2018 5:00 AM 2
    3000 1/4/2018 7:00 AM 3

    Thanks for any advice. I am using MS Access 2013 to perform this operation.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Just so you know your Date and Time columns can be one column. A DateTime type field is a double that already stores both date and time. Also note that you shouldn't use the words Order, Date, or Time as field names as they are reserved words.

    The following code should loop through each row of the table and update as desired. The code assumes that date and time are one field named [OrderDate] and that your order # field is called [OrderNumber]
    Code:
    Sub GenerateBatchNumbers()
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        
        Dim count As Long
        Dim prevOrderNumber As Long
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT OrderNumber, Batch FROM DataTable ORDER BY OrderNumber, OrderDate")
        
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveFirst
            count = 1
            prevOrderNumber = rs!OrderNumber
            
            Do While Not rs.EOF
                If rs!OrderNumber <> prevOrderNumber Then
                    count = 1
                    prevOrderNumber = rs!OrderNumber
                End If
                
                If rs!batch <> count Or IsNull(rs!batch) Then
                    rs.Edit
                    rs!batch = count
                    rs.Update
                End If
                
                count = count + 1
                
                rs.MoveNext
            Loop
        End If
        
        rs.Close
        
    ExitHandler:
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox "Error generating batch numbers"
        Resume ExitHandler
        
    End Sub
    You're essentially creating a calculated field. We really don't like those. What is this being used for?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you have a unique batch number along with a Date/time stamp (1 field as kd017 suggested), it doesn't require a sequential number. The date/time stamp can always be used to order your records. No need to separate Date and Time---use MS Access Now() function.

    Get a complete description of the requirements for your project.

    Sounds like you need

    Batch--->Order--->OrderItems

    but we don't know your business nor requirement.
    ??Customer, supplier, vendor.....????

    Good luck and welcome to the forum.

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    If you do have a requirement for a sequential Batch number I wouldn't store it in a table. As kd2017 previously mentioned this is not good practice. You can do the sequential numbering in a query.

    Given this table;

    Click image for larger version. 

Name:	Image1.PNG 
Views:	17 
Size:	10.0 KB 
ID:	34512

    This query;

    Code:
    SELECT tblOrders.OrderNumber, tblOrders.OrderDate, (Select Count(OrderNumber)
    From tblOrders As Dupe Where Dupe.OrderNumber=tblOrders.OrderNumber And Dupe.OrderDate <=tblOrders.OrderDate) AS Batch
    FROM tblOrders;

    Returns this result;

    Click image for larger version. 

Name:	Image2.PNG 
Views:	17 
Size:	10.6 KB 
ID:	34513

    Then just use that query where you would otherwise use the table.

  5. #5
    tact8 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    3
    Thank you everyone for the quick replies.

    Quote Originally Posted by kd2017 View Post
    Just so you know your Date and Time columns can be one column.
    Good suggestion about Date and Time being in one column. I will implement that immediately.

    EDIT: I had written a longer reply. But using kd2017's code, it now works exactly as it should. My big problem was not the code per se, but embedding the SQL query into the VBA OpenRecordset function.

    So, thank you everybody.

  6. #6
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    If you choose to store this information in a table just be aware that if an existing record ever gets edited for Date/Time in such a way that it would change the order, there is no automatic way for the application to adjust the batch number accordingly. You could end up with incorrect data in your table and no intrinsic way to know about it. If you use a query, the batch number will always be correct even if existing records change.

    I saw your earlier post before you edited and you indicated that your hesitation was that you already have forms designed with the table as the record source. Keep in mind that you can just change the record source from the table to the query. The calculated field in the query will act exactly as the table field does now in that regard. As long as the fields are named the same as the ones in the table then you would not need to change anything in your form design. Just FYI.

  7. #7
    tact8 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    3
    Quote Originally Posted by Beetle View Post
    If you choose to store this information in a table just be aware that if an existing record ever gets edited for Date/Time in such a way that it would change the order, there is no automatic way for the application to adjust the batch number accordingly. You could end up with incorrect data in your table and no intrinsic way to know about it. If you use a query, the batch number will always be correct even if existing records change.

    I saw your earlier post before you edited and you indicated that your hesitation was that you already have forms designed with the table as the record source. Keep in mind that you can just change the record source from the table to the query. The calculated field in the query will act exactly as the table field does now in that regard. As long as the fields are named the same as the ones in the table then you would not need to change anything in your form design. Just FYI.
    Those are both good points. I think I will set it up both ways, and if either is just as good, I should choose using the query, as it is the better practice.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Orders to Be Automatically Grouped Based on Order Date
    By hellocng in forum Database Design
    Replies: 2
    Last Post: 10-25-2017, 02:12 AM
  2. Replies: 2
    Last Post: 11-01-2016, 09:04 AM
  3. Replies: 4
    Last Post: 03-01-2015, 11:31 AM
  4. Subtracting a date/time field from a number to get date/time
    By Lifeseeker1019 in forum Programming
    Replies: 4
    Last Post: 03-28-2014, 12:59 PM
  5. Sequentially number records in a table field
    By kenton.l.sparks@gmail.com in forum Programming
    Replies: 4
    Last Post: 04-08-2011, 08:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums