Solved comparison of two tables (1 Viewer)

[Sabrina87]

Hello,

I have two tables in my database.

The first one is chronologically structured as follows:

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​

The second one is also chronological and has the following structure:

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

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, and outputs them in a new column in a new table?

In addition, however, there should be another column that gives out 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 the actual production date of the surplus falls into one of the two periods (before or after) 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.

In a nutshell: 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. This, however, with the condition that actually produced surplus quantities are passed on to the next identical MaterialID only if the date of the actually produced surplus quantity falls within its two time windows.

So the final table would look like this:

MaterialID​	PlannedProductionDate​	PlannedProductionQuantity​	ProducedWithin14DaysWindowBefore​	ProducedWithin5DaysWindowAfter
255​	27.12.2021​	192​		192 (from 31.12.2021)​
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

 

[Pat Hartman]

Before we get into some complicated solution, let's start with why the planned and actual fields are not in the same table and in the same row? Then you know the under/over for each batch and time period. Or, why don't you use a batch ID so the two tables could be joined?

Why does is the gap between the planned/produced dates relevant? How is the calculation affected if the items are produced outside the window? What if some items are produced earlier than 14 days? Should producing extra modify the plan?

I think there is too much we don't know about the actual business process and rules to come up with a rational solution.

 

[LarryE]

Sabrina:
Are you still interested in receiving help with this? I may have a partial solution for you.

 

[Gasman]

Crossposted, first at https://www.accessforums.net/showthread.php?t=86168

 

[Pat Hartman]

Looks like they're as confused as we are.

 

[Sabrina87]

I have just seen that crossposting is not allowed. I'm sorry for this. I didn't know.

 

[LarryE]

I have just seen that crossposting is not allowed. I'm sorry for this. I didn't know.

I may have at least a partial solution. I will attach a file a little later today for your review. I am still working on passing surpluses to a future production date. This is a difficult and complex issue, so it may take some time to solve to your satisfaction.

 

[CJ_London]

It’s not not allowed but a simple courtesy so responders can see what has worked, what hasn’t and don’t waste their time suggesting what had already been suggested

I note you have not responded to the partial solution offered in the other thread - basically needs more clarity from you as indicated in the comments. Looks like the same issue Larry is referring to

 

[Sabrina87]

Before we get into some complicated solution, let's start with why the planned and actual fields are not in the same table and in the same row? Then you know the under/over for each batch and time period. Or, why don't you use a batch ID so the two tables could be joined?

Why does is the gap between the planned/produced dates relevant? How is the calculation affected if the items are produced outside the window? What if some items are produced earlier than 14 days? Should producing extra modify the plan?

I think there is too much we don't know about the actual business process and rules to come up with a rational solution.

Unfortunately, the data comes from two separate transactions that output two tables that can only be related to each other only by the MaterialID.

I just want to track if the actual production of the MaterialID started timely (within the 14 days window before and on planned date), after the planned date with a max of 5 days delay or not at all (Here, not all means also with a delay of more than 5 days.)

If the items are actually produced earlier than 14 days, they are not to be assigned to the two time windows of the MaterialID, therefore fall out of consideration.

What do you mean by modification by producing extra?

 

[Sabrina87]

Sabrina:
Are you still interested in receiving help with this? I may have a partial solution for you.

Hello Larry,

what does the solution look like? Unfortunately, I couldn't find one yet.


Sabrina

 

[Sabrina87]

It’s not not allowed but a simple courtesy so responders can see what has worked, what hasn’t and don’t waste their time suggesting what had already been suggested

I note you have not responded to the partial solution offered in the other thread - basically needs more clarity from you as indicated in the comments. Looks like the same issue Larry is referring to

I will do this now and report my findings with the partial solution.

 

[LarryE]

Hello Larry,

what does the solution look like? Unfortunately, I couldn't find one yet.


Sabrina

I will attach a database later today for you to open a review.

 

[Sabrina87]

I don't want to crosspost further but what should I do now if want to continue the discussion with all members of the two forums?

 

[Sabrina87]

I replied to Ajax in the other forum with an approach that works for most parts but isn't quite there yet. Could you please look over it?

Thank you for your help! I appreciate it!


Sabrina

 

[LarryE]

Here is what I have come up with based on my understanding of what you want. The database does not use recordsets to calculate anything but rather queries to determine which production runs fall within the date criteria. It also has one table for materials definition and one table for inputting material production runs. Also, calculated surpluses are not kept in a separate table. They are (and should be) calculated on a form (and later a report).

The production input form is a subform of the material input form. Use the ARROW buttons to view different material records and related production records.

Please open the file and look at the tables, relationships window and queries so you understand how to determine which production runs are contributing to the prior 14 days and after 5 days. Once you understand how this model works, then we can go from there.

 

[Sabrina87]

Hi Larry,

Thank you very much for your effort. It looks very good! Nevertheless, this for the case in which we can bring together the planned and actual data as well as the quantity in one row but this is not the case with my data.

I wrote a macro in VBA in the other forum. I think you can better understand what I mean there.


Kind regards

Sabrina

 

[LarryE]

I have tried over many years to get permission to post to that other forum and they have never answered my requests, so I cannot do anything in that other forum. I can log in but that is all I can do. I have given up on it after so many years of trying. I have to stick to this forum. Good luck with your project though.

 

[Pat Hartman]

I just want to track if the actual production of the MaterialID started timely

If you don't have any way to tie the two records together aside from the MaterialID, you aren't going to get any meaningful results. How do you know which records match to which records? I suppose the best you could do would be to assume chronological order but you would be guessing.

 

[CJ_London]

Might be an idea to reconsider what your report needs to look like - perhaps apply a FIFO technique. Use a union query to combine the two tables - something like this instead of two recordset loops

SELECT "Actual" AS Rep, MaterialID, ActualProductionDate as RepDate, ActualProductionQuantity as Qty
FROM tblActual
UNION SELECT "Planned", MaterialID, PlannedProductionDate, PlannedProductionQuantity
FROM tblPLanned
ORDER BY MaterialID, RepDate

Then similar to the code in the other forum, step through the recordset comparing the Rep, date and quantity to decide values

finally convert the results to a crosstab to give the view you require.

Note you still have to address the issue of knowing where there is a relevant actual before the first planned and a relevant planned after the last actual

 

[Sabrina87]

Hello guys,

Thank you for your support, here's my solution:

Option Compare Database
Public Sub ComparePlanningWithActuals()

Dim dbs As DAO.database
Dim rstP As DAO.Recordset
Dim rstA As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb()
Set rstP = CurrentDb.OpenRecordset("SELECT * FROM tblMaterialComparison ORDER BY PlannedStartingDate ASC")

Do While Not rstP.EOF
    Set rstA = CurrentDb.OpenRecordset("SELECT * FROM tblMaterialActualsTransaction 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!Backlog) Then
                rstP.Edit
                rstP!ProducedTimely = rstP!ProducedTimely + rstA!DailySumOfTotalOrderQuantity
                rstA.Edit
                rstA!DailySumOfTotalOrderQuantity = 0
            Else
                rstP.Edit
                rstP!ProducedTimely = rstP!ProducedTimely + -(rstP!Backlog)
                rstA.Edit
                rstA!DailySumOfTotalOrderQuantity = rstA!DailySumOfTotalOrderQuantity - -(rstP!Backlog)
            End If
        ElseIf rstA!ActualLaunchDate > rstP!PlannedStartingDate And rstA!ActualLaunchDate <= rstP!PlannedStartingDate + 5 Then
            If rstA!DailySumOfTotalOrderQuantity <= -(rstP!Backlog) Then
                rstP.Edit
                rstP!ProducedWithDelay = rstP!ProducedWithDelay + rstA!DailySumOfTotalOrderQuantity
                rstA.Edit
                rstA!DailySumOfTotalOrderQuantity = 0
            Else
                rstP.Edit
                rstP!ProducedWithDelay = rstP!ProducedWithDelay + -(rstP!Backlog)
                rstA.Edit
                rstA!DailySumOfTotalOrderQuantity = rstA!DailySumOfTotalOrderQuantity - -(rstP!Backlog)
            End If
        End If
        If rstA.EditMode <> dbEditNone Then
            rstA.Update
        End If
        rstA.MoveNext
        If rstP.EditMode <> dbEditNone Then
            rstP.Update
        End If
    Loop
    If rstP.EditMode <> dbEditNone Then
        rstP.Update
    End If
    rstP.MoveNext
Loop

strSQL = "INSERT INTO tblMaterialActualsTransactionHistory ( MaterialID, ActualLaunchDate, DailySumOfTotalOrderQuantity ) SELECT tblMaterialActualsTransaction.MaterialID, tblMaterialActualsTransaction.ActualLaunchDate, tblMaterialActualsTransaction.DailySumOfTotalOrderQuantity FROM tblMaterialActualsTransaction"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "DELETE tblMaterialActualsTransaction.* FROM tblMaterialActualsTransaction"
CurrentDb.Execute strSQL, dbFailOnError

rstP.Close
rstA.Close
dbs.Close

ExitProc:
    Set rstP = Nothing
    Set rstA = Nothing
    Set dbs = Nothing

End Sub

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 backlog is greater than the actual quantity, all actuals are taken, if it is the other way around, then only the backlog is subtracted.
The same is done for the after values.

At the end, all remaining actuals are transfered to a history table and the table is emtpied for future calculations.


Kind regards

Sabrina