Creating a row based on difference between tables (1 Viewer)

[davey4444]

Hi there,

I am using data from a sales and a finance system and essentially need to understand the difference between them and account for this "balancing" row.

My data is as follows

TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, A, F123, 100, 1000, 10
January, A, F123, 800, 8000, 80
January, B, F123, 10, 100, 1
January, A, F124, 500, 10000, 1000
February, A, F123, 100, 1000, 10
February, A, F123, 800, 8000, 80
February, B, F123, 10, 100, 1
February, A, F124, 500, 10000, 1000

TBL_Finance
Month, Factory, Quantity, Sales, Margin
January, F123, 1000, 10000, 100
January, F124, 550, 10500, 1000
February, F123, 950, 9500, 95
February, F124, 600, 10000, 1000

I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -

TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9
January, C, F124, 50, 500, 0
February, C, F123, 40, 400, 4
February, C, F124, 100, 0, 0

Thanks in advance.

 

[Mihail]

What you mean when you speak about differences between tables ?
I see a lot of differences here.

 

[davey4444]

Sorry, I mean the difference in the total Quantity, Sales and Margin for each factory in each month in TBL_Sales vs the total for those values in TBL_Finance.

The value in TBL_Finance will always be on one row per month per factory whereas TBL_Sales will show lots of values.

In my first row of desired figures (TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9)

This is the figure in TBL_Finance less the totals for that month and factory in TBL_Sales.

 

[Mihail]

Just I have starting to design a solution for you and I have a little doubt:
While a month correspond to many years, how you select a certain month from a certain year ?

 

[Mihail]

This is the answer for you.
Make more tests with real data.
Cheers !

PS:
I have used "Monthes" because Month is a reserved word.
And now I see that is not correct in English

 

[davey4444]

This is brilliant, thank you.