Results 1 to 12 of 12
  1. #1
    Sabrina87 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2022
    Posts
    6

    comparison of two tables

    Hello,




    I would consider myself a novice in VBA in Access despite having experience in VBA programming in Excel.




    I have two tables in my database.




    The first one is chronologically structured as follows (just an example for clarification):

    MaterialID PlannedProductionDate PlannedProductionQuantity
    255 27.12.2021 192
    255 04.01.2022 192
    300 15.01.2022 94
    300 12.04.2022 188
    300 19.04.2022 94
    87 19.06.2022 10


    MaterialID is the primary key and MaterialID with PlannedProductionDate is the alternate key.




    The second one is also chronological and has the following structure (just an example for clarification):

    MaterialID ActualProductionDate ActualProductionQuantity
    255 31.12.2021 200
    255 05.01.2022 5
    300 12.01.2022 100
    300 11.04.2022 500


    MaterialID is the primary key and MaterialID with ActualProductionDate is the alternate key.


    How does a procedure look like now, which sums up all production quantities in the actuals table with material ID 300, which were produced 14 days before the planned production date up to the planned production date, starting from the corresponding date in the planning table and outputs them in a new column in a new table?


    In addition, however, there should be another column that gives me the sum of the quantities that were actually produced in the following 5 days.


    But now comes the crux: These two calculations (period before and after) are supposed to 'fill up' the planned quantity and pass on the surplus to the next MaterialID 300, if this surplus falls into the respective periods of this next MaterialID 300. If there is no next MaterialID 300 with overlapping periods, no proportional quantity should be passed on and if there is a shortage, there is nothing to pass on anyway.


    So the final table would look like this:
    MaterialID PlannedProductionDate PlannedProductionQuantity Within14DaysWindowBeforeProduced Within5DaysWindowAfterProduced
    255 27.12.2021 192 192 (from 31.12.2021 pro rata)
    255 04.01.2022 192 8 (from 31.12.2021 pro rata) 5
    300 15.01.2022 94 100 (will be taken over from 12.01.2022, as the surplus of 6 cannot be passed on)
    300 12.04.2022 188 188 (from 11.04.2022 pro rata)
    300 19.04.2022 94 312 (pro-rated from 11/04/2022; Surplus cannot be passed on.)
    87 19.06.2022 10 0 0




    How do I implement this in VBA in Access?




    Kind regards


    Sabrina

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Can you provide a birds eye view of this process - what sort of production are you talking about? is it perishable goods? Just trying to get a picture of what this actually represents

    Also your example feels it may be a bit too simplistic...

    - are there rules around frequency of production (e.g. a minimum of 5 days apart - both planned and actual)
    - is this as 7 day a week operation?
    - can a large actual be passed on to more than one planned - e.g. if material 300 had another planned date of 22.04.2022 - would that change the 'surplus cannot be passed on' message for 19.04.2022 and/or what would be message for 22.04.2022 be?
    - is this supposed to be since the beginning of time? or do you anticipate using from/to dates - in which case how would they be applied to both planned and actual dates.

  3. #3
    Sabrina87 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2022
    Posts
    6
    Hi Ajax,

    I just want to track how many actuals were produced in the 14 days period before planning date and on the planning date in one column and how manys actuals were produced in the 5 days frame after the planning date in another column.

    It's an industrial production of non-perishable goods and the tracking is only for production success monitoring, i. e. a material whose requirements are predominantly covered by actual quantities from 14 days up to the planning date would receive a green flag, a material whose requirements are predominantly covered by actual quantities from the 5 days after the planning date would receive an orange flag and a material which does not have actual quantities in the two time frame before and after would get a red flag.
    Whether the requirements are covered or not is initially irrelevant for flagging, but whether production took place at all in the time windows, the quantity analysis comes later.

    To answer your questions:
    - There are no rules for production.
    - We assume that it's a 7 days a week operation.
    - If MaterialID 300 had another planned date of 22.04.2022 with a planned quantity of 94, MaterialID 300 of 19.04.2022 would get 94 as required and MaterialID 300 of 22.04.2022 would get the rest, so 312 - 94 = 218. Here, we have a surplus again which cannot be passed to another MaterialID 300 because it's the last one.
    - I don't understand this question. What do you mean by 'beginning of time' and by 'from/to dates'?


    Thanks a lot for your help!



    Sabrina

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    - I don't understand this question. What do you mean by 'beginning of time' and by 'from/to dates'?
    Basically how far back do your records go 6 months? a year? 10 years? Your example data is looking at a period of around 7 months. With regards the from date, presumably you have a date you don't want go before - perhaps you are only looking at those records which have a planned date where that date +5 is in the current year. Or perhaps it is based on the production date where production date +14 is in the current year.

    With regards the end date - same sort of logic would apply.

  5. #5
    Sabrina87 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2022
    Posts
    6
    Quote Originally Posted by Ajax View Post
    Basically how far back do your records go 6 months? a year? 10 years? Your example data is looking at a period of around 7 months. With regards the from date, presumably you have a date you don't want go before - perhaps you are only looking at those records which have a planned date where that date +5 is in the current year. Or perhaps it is based on the production date where production date +14 is in the current year.

    With regards the end date - same sort of logic would apply.

    The comparison will be done periodically, so the records will always start at the beginning of a period and go to its end, e. g. the beginning of April until the end of June. The reference date is always the planned date because this one determines where the two time windows lie.

    I still don't get why this is important because I just want to find out if there is production around a planned date of a MaterialID.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Sorry, but struggling to come up with your exact requirements and can't really devote any more time to this at present.

    Best I can advise is you need a look within a loop for two recordsets representing the two tables - along the lines of

    Code:
    dim rsP as dao.recordset
    dim rsA as dao.recordset
    dim issued as long 'how to use as a carry forward?
    
    set rsP=currentdb.openrecordset("SELECT * FROM tblPlanned ORDER BY materialID, PlannedProductionDate") 'include a date range criteria for period?
    while not rsp.eof 'outer loop
        set rsA =currentdb.openrecordset("SELECT * FROM tblActual WHERE materialID=" & rsP!materialID & " ORDER BY ActualProductionDate") 'include a date range criteria around production date?
        while not rsA.EOF 'inner loop
          if rsA!actualproductiondate>rsp!plannedproductiondate and rsA!actualproductiondate<=rsp!plannedproductiondate+5 then 'this is after
     
              'if this is the last record in the middle of a recordset - how to determine if the 'after' should be zero if there is no later record after the date range specified? perhaps a dlookup?
    
          elseif rsA!actualproductiondate>rsp!plannedproductiondate-14 and rsA!actualproductiondate<=rsp!plannedproductiondate then  'this is prior
             
              'if this is the first record in the middle of a recordset - how to determine if the prior is a 'leftover' from a planned record that precedes the date range specified
           'issue: to get off the starting block the prior value needs to be adjusted for any preceding record whilst the after record needs to be adjusted depending on whether there is any planned record within 14 days
           
           end if
           rsA.movenext
        wend 'inner loop
    
    
        rsP.movenext
    wend 'outer loop
    I've annotated the code, you will need to resolve the questions I've raised.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Sabrina87 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2022
    Posts
    6
    Quote Originally Posted by Ajax View Post
    Sorry, but struggling to come up with your exact requirements and can't really devote any more time to this at present.

    Best I can advise is you need a look within a loop for two recordsets representing the two tables - along the lines of

    Code:
    dim rsP as dao.recordset
    dim rsA as dao.recordset
    dim issued as long 'how to use as a carry forward?
    
    set rsP=currentdb.openrecordset("SELECT * FROM tblPlanned ORDER BY materialID, PlannedProductionDate") 'include a date range criteria for period?
    while not rsp.eof 'outer loop
        set rsA =currentdb.openrecordset("SELECT * FROM tblActual WHERE materialID=" & rsP!materialID & " ORDER BY ActualProductionDate") 'include a date range criteria around production date?
        while not rsA.EOF 'inner loop
          if rsA!actualproductiondate>rsp!plannedproductiondate and rsA!actualproductiondate<=rsp!plannedproductiondate+5 then 'this is after
     
              'if this is the last record in the middle of a recordset - how to determine if the 'after' should be zero if there is no later record after the date range specified? perhaps a dlookup?
    
          elseif rsA!actualproductiondate>rsp!plannedproductiondate-14 and rsA!actualproductiondate<=rsp!plannedproductiondate then  'this is prior
             
              'if this is the first record in the middle of a recordset - how to determine if the prior is a 'leftover' from a planned record that precedes the date range specified
           'issue: to get off the starting block the prior value needs to be adjusted for any preceding record whilst the after record needs to be adjusted depending on whether there is any planned record within 14 days
           
           end if
           rsA.movenext
        wend 'inner loop
    
    
        rsP.movenext
    wend 'outer loop
    I've annotated the code, you will need to resolve the questions I've raised.

    Hi Ajax,

    thank you for your suggestion.


    What do you think about this approach:

    I took the planning table and have extended it by two columns, so it looks like this:
    Click image for larger version. 

Name:	table.png 
Views:	19 
Size:	12.4 KB 
ID:	48116

    Here is the code I took from you. I have expanded it.
    Code:
    Option Compare Database
    Public Sub ComparePlanningWithActuals()
    
    
    Dim dbs As dao.database
    Dim rstP As dao.Recordset
    Dim rstA As dao.Recordset
    
    
    Set dbs = CurrentDb()
    Set rstP = CurrentDb.OpenRecordset("SELECT * FROM tblDailyAggregateOfMaterialPlanning ORDER BY PlannedStartingDate ASC")
    
    
    Do While Not rstP.EOF
        Set rstA = CurrentDb.OpenRecordset("SELECT * FROM tblDailyAggregateOfMaterialActuals WHERE MaterialID=" & rstP!MaterialID & " ORDER BY ActualLaunchDate ASC")
        Do While Not rstA.EOF
            If rstA!ActualLaunchDate <= rstP!PlannedStartingDate And rstA!ActualLaunchDate >= rstP!PlannedStartingDate - 14 Then
                If rstA!DailySumOfTotalOrderQuantity <= rstP!DailySumOfPlannedQuantity Then
                    rstP.Edit
                    rstP!ProducedTimely = rstP!ProducedTimely + rstA!DailySumOfTotalOrderQuantity
                    rstA.Edit
                    rstA!DailySumOfTotalOrderQuantity = 0
                Else
                    rstP.Edit
                    rstP!ProducedTimely = rstP!ProducedTimely + rstP!DailySumOfPlannedQuantity
                    rstA.Edit
                    rstA!DailySumOfTotalOrderQuantity = rstA!DailySumOfTotalOrderQuantity - rstP!DailySumOfPlannedQuantity
                End If
            ElseIf rstA!ActualLaunchDate > rstP!PlannedStartingDate And rstA!ActualLaunchDate <= rstP!PlannedStartingDate + 5 Then
                If rstA!DailySumOfTotalOrderQuantity <= rstP!DailySumOfPlannedQuantity Then
                    rstP.Edit
                    rstP!ProducedWithDelay = rstP!ProducedWithDelay + rstA!DailySumOfTotalOrderQuantity
                    rstA.Edit
                    rstA!DailySumOfTotalOrderQuantity = 0
                Else
                    rstP.Edit
                    rstP!ProducedWithDelay = rstP!ProducedWithDelay + rstP!DailySumOfPlannedQuantity
                    rstA.Edit
                    rstA!DailySumOfTotalOrderQuantity = rstA!DailySumOfTotalOrderQuantity - rstP!DailySumOfPlannedQuantity
                End If
            End If
            If rstA.EditMode <> dbEditNone Then
                rstA.Update
            End If
            rstA.MoveNext
        Loop
        If rstP.EditMode <> dbEditNone Then
            rstP.Update
        End If
        rstP.MoveNext
    Loop
    
    
    rstP.Close
    rstA.Close
    dbs.Close
    
    
    ExitProc:
        Set rstP = Nothing
        Set rstA = Nothing
        Set dbs = Nothing
    
    
    End Sub
    For clarification: DailySumOfTotalOrderQuantity = daily sum of actual production quantity


    The code works for most parts but for some parts, it doesn't work as expected, like for MaterialID 217:
    Click image for larger version. 

Name:	planning table.png 
Views:	19 
Size:	18.0 KB 
ID:	48117
    because of this (the original actuals table):
    Click image for larger version. 

Name:	actuals.png 
Views:	19 
Size:	23.6 KB 
ID:	48118

    Why are the 180 parts from 22.04.2022 not placed in the column ProducedTimely?



    Kind regards

    Sabrina

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    In what way does it ‘not work’?

    Suggest step through the code and see where it takes you - is it processing records in the right order to get the right result?

    From the description of your requirement there is a conundrum as to which to calculate first - the before value or the after value - which I tried to get clarity on and left notes in the example code

  10. #10
    Sabrina87 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2022
    Posts
    6
    Quote Originally Posted by Ajax View Post
    In what way does it ‘not work’?

    Suggest step through the code and see where it takes you - is it processing records in the right order to get the right result?

    From the description of your requirement there is a conundrum as to which to calculate first - the before value or the after value - which I tried to get clarity on and left notes in the example code
    Hi Ajax,

    the code works now.

    Code:
    If rstP.EditMode <> dbEditNone Then
    rstP.Update
    End If
    was missing in the inner loop.


    First the before values are credited to the MaterialID, then a copy of the actuals table, which I use for the calculation, is updated since the value which is credited in the comparison table to the MaterialID is subtracted in this table. If the planned quantity is greater than the actual quantity, all actuals are taken, if it is the other way around, then only the planned quantity is subtracted.
    The same is done for the after values.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK - so you got there

    Since you have cross posted, please post the solution - or at least advise a solution has been found on the other forum

  12. #12
    Sabrina87 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2022
    Posts
    6
    Ok, I will do that. Thank you for your help!

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

Similar Threads

  1. Control Value Comparison
    By CementCarver in forum Programming
    Replies: 24
    Last Post: 10-14-2013, 04:48 PM
  2. Comparison
    By raytackettsells in forum Queries
    Replies: 2
    Last Post: 08-06-2012, 05:30 PM
  3. Number comparison help
    By kgriff in forum Access
    Replies: 13
    Last Post: 02-17-2012, 07:41 PM
  4. Replies: 7
    Last Post: 01-28-2011, 11:15 AM
  5. Comparison
    By VICTOR HUGO in forum Access
    Replies: 8
    Last Post: 02-10-2010, 04:32 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