How to find mutual differences

  • Thread starter Thread starter Steve Andler
  • Start date Start date
S

Steve Andler

Hello ;

I'd like to compare to month's data ;
Month1 ;
Part No Cost Qty
111 5 50
222 10 100
333 15 150

Month2 ;
Part No Cost Qty
111 50 500
333 15 150
444 200 2000

I want my query result to show ;

Month1 Month2 Diff
Part No Cost Qty Cost Qty Cost Qty
111 5 50 50 500 -45 -450
222 10 100 10 100
333 15 150 15 150 0 0
444 200 2000 -200 -2000


What kind of query needs to be written for this ?
I would greatly appreciate any help .

Thanks in advance
Steve
 
Sorry , i forgot to mention that .
Yes , they are two different tables created by
"make table query "

However the data for these two different table are kept
in one source table so if that easier i could build the
query based on that db type of table .

The source table is as follows ;

Part No Cost Qty Month
111 5 50 1
222 10 100 1
333 15 150 1
111 50 500 2
333 15 150 2
444 200 2000 2
 
Sorry, didn't see your Table at the end of the last post.

This Query, or rather a set of Query is complex since you want the full
outer join which is not available in Access. What I would suggest is to use
a Union Query to create the dummy Records for Products that missing on some
months.

* Query0:

SELECT DISTINCT [Part No]
FROM YourTable

This is to get the list of [Part No] covered by your Table (4 rows)

* Query1: (for Month 1) with dummy Record for [Part No] = 444

SELECT T.[Part No], T.Cost, T.Qty, T.Month
FROM YourTable As T
WHERE T.Month = 1
UNION
SELECT Q0.[Part No], 0, 0, 1
FROM Query0 As Q0
LEFT JOIN YourTable As TU
ON Q0.[Part No] = TU.[Part No]
WHERE (TU.[Part No] Is Null)

(4 rows including the dummy row for 444).


* Query2: (for Month 2) with dummy Record for [Part No] = 222

SELECT T.[Part No], T.Cost, T.Qty, T.Month
FROM YourTable As T
WHERE T.Month = 2
UNION
SELECT Q0.[Part No], 0, 0, 2
FROM Query0 As Q0
LEFT JOIN YourTable As TU
ON Q0.[Part No] = TU.[Part No]
WHERE (TU.[Part No] Is Null)

(4 rows including the dummy row for 222)

* Query3: combining Q2 & Q3 for the required results:

SELECT Q1.[Part No], Q1.Cost, Q1.Qty, Q2.Cost, Q2.Qty,
Q1.Cost - Q2.Cost As CostDiff, Q1.Qty - Q2.Qty As QtyDiff
FROM Query1 As Q1
INNER JOIN Query2 As Q2
ON Q1.[Part No] = Q2.[Part No]

This should give you the required result.

It is possible to combine all 4 Queries into one Query but it will be a
long a complex SQL String. I leave it for you to try if you want to.
 
Sorry, I've just realised that I omitted a condition in Q1
& Q2. The correct SQL Strings are:

* Query1:

SELECT T.[Part No], T.Cost, T.Qty, T.Month
FROM YourTable As T
WHERE T.Month = 1
UNION
SELECT Q0.[Part No], 0, 0, 1
FROM Query0 As Q0
LEFT JOIN YourTable As TU
ON Q0.[Part No] = TU.[Part No]
WHERE (TU.Month = 1)
AND(TU.[Part No] Is Null)


* Query2:

SELECT T.[Part No], T.Cost, T.Qty, T.Month
FROM YourTable As T
WHERE T.Month = 2
UNION
SELECT Q0.[Part No], 0, 0, 2
FROM Query0 As Q0
LEFT JOIN YourTable As TU
ON Q0.[Part No] = TU.[Part No]
WHERE (TU.Month = 2)
AND (TU.[Part No] Is Null)

HTH
Van T. Dinh
MVP (Access)
 
Thank you very much Van .
I needed to change few things but
I have managed to complete my query .

Steve
 
Hi Van;

I have one more question .

I have managed to join two tables with one union query and
now i can see both tables and the differences .

Now i need to calculate the differences to find out the
impact within a month .

Is it possible add that formula that calculates the
difference of one column , in the SQL string i have
written . If so how ?

Thanks
Steve
 
Not sure what you want with "Now i need to calculate the differences to find
out the impact within a month." since you already got the differences???

Describe what you want. Give example if possible. Also post your set of
Queries since you wrote you modified them.
 
Hi Van ;

I have that below data now ;
Month1 Month2
Part No Cost Cost
111 5 50
222 10
333 15 15
444 200
Click to expand...

and now i want the query also add a column for differences
which should like ;
Month1 Month2 Difference
Part No Cost Cost
111 5 50 -45
222 10 10
333 15 15 0
444 200 -200
Click to expand...

The query i have written is

(SELECT [Month1].*, [Month2].*
FROM [Month1] LEFT JOIN [Month2]
ON [Month1].[PART NO] = [Month2].[PART NO]

UNION ALL SELECT [Month1].*, [Month2].*
FROM [Month1] RIGHT JOIN [Month2]
ON [Month1].[PART NO] = [Month2].[PART NO]
WHERE [Month1].[PART NO] is null;

Now i want to add the string to that to calculate the
differences between cost .

I hope that explains .

Thanks
Steve
 
It was in the Query I posted.

--
HTH
Van T. Dinh
MVP (Access)



Steve Andler said:
Hi Van ;

I have that below data now ;
Month1 Month2
Part No Cost Cost
111 5 50
222 10
333 15 15
444 200
Click to expand...

and now i want the query also add a column for differences
which should like ;
Month1 Month2 Difference
Part No Cost Cost
111 5 50 -45
222 10 10
333 15 15 0
444 200 -200
Click to expand...

The query i have written is

(SELECT [Month1].*, [Month2].*
FROM [Month1] LEFT JOIN [Month2]
ON [Month1].[PART NO] = [Month2].[PART NO]

UNION ALL SELECT [Month1].*, [Month2].*
FROM [Month1] RIGHT JOIN [Month2]
ON [Month1].[PART NO] = [Month2].[PART NO]
WHERE [Month1].[PART NO] is null;

Now i want to add the string to that to calculate the
differences between cost .

I hope that explains .

Thanks
Steve
Click to expand...
 
Back
Top