Bakr said:
Hi to all,
I have an already query that uses SELECT ... UNION Statements to combine
fields in tow different tables( infos about Imported and Exported boards) in
this way:
Item No. imported boards No. exported boards Total
TRANSFORM SUM(amount) AS value
SELECT item, SUM(value) As theTotal
FROM
( SELECT item, NoImportedBoards As amount, "Imported" AS type FROM
imported
UNION ALL
SELECT item, -NoExportedBoards, "Exported" FROM exported ) As x
GROUP BY item
PIVOT type
I have made a filter of that statement by date (viewing report for each month)
...
the issue that I want to solved is I want the "Total" field of the previous
month to compare it with the recent month...
Is it be solved by making new query and combine it with the old one? or
altering the existed query???
The two tables have "Date" field ...
Although I wasn't sure exactly how you wanted to display your results, I
made some guesses and came up with the following. Suppose your Tables
contain the following transactions:
Table [Exported]:
Item NoExportedBoards When
---- ---------------- ----
Bag $15.00 6/20/2005
Bag $3.00 7/20/2005
Table [Imported]:
Item NoImportedBoards When
---- ---------------- ----
Bag $4.00 12/10/2004
Bag $3.00 1/20/2005
Box $10.00 6/5/2005
Bag $20.00 6/6/2005
Box $13.00 7/15/2005
Using these Tables, I defined the following Queries, that I think
produce the kinds of information you described. The first one combines
the transactions into one set:
Query [Q_ExImUnion]:
SELECT item, NoImportedBoards As amount,
"Imported" AS type, When
FROM imported
UNION ALL
SELECT item, -NoExportedBoards,
"Exported", When
FROM exported
ORDERBY item, when;
item amount type When
---- ------ -------- ----------
Bag $4.00 Imported 12/10/2004
Bag $3.00 Imported 1/20/2005
Bag $20.00 Imported 6/6/2005
Bag ($15.00) Exported 6/20/2005
Bag ($3.00) Exported 7/20/2005
Box $10.00 Imported 6/5/2005
Box $13.00 Imported 7/15/2005
For each item, this Query sums the transactions for each month:
Query [Q_ExImItemSumByMonth]:
SELECT Q_ExImUnion.item,
DateSerial(Year([Q_ExImUnion]![When]),
Month([Q_ExImUnion]![When]),1)
AS ThisMonth,
DateSerial(Year([Q_ExImUnion]![When]),
Month([Q_ExImUnion]![When])-1,1) AS LastMonth,
Sum(Q_ExImUnion.amount) AS SumOfamount
FROM Q_ExImUnion
GROUP BY Q_ExImUnion.item,
DateSerial(Year([Q_ExImUnion]![When]),
Month([Q_ExImUnion]![When]),1),
DateSerial(Year([Q_ExImUnion]![When]),
Month([Q_ExImUnion]![When])-1,1)
ORDER BY Q_ExImUnion.item,
DateSerial(Year([Q_ExImUnion]![When]),
Month([Q_ExImUnion]![When]),1);
item ThisMonth LastMonth SumOfamount
---- --------- --------- -----------
Bag 12/1/2004 11/1/2004 $4.00
Bag 1/1/2005 12/1/2004 $3.00
Bag 6/1/2005 5/1/2005 $5.00
Bag 7/1/2005 6/1/2005 ($3.00)
Box 6/1/2005 5/1/2005 $10.00
Box 7/1/2005 6/1/2005 $13.00
For each item, this Query lists the sums for the current month and the
previous month:
Query [Q_ExImItems2Months]:
SELECT Q_ExImItemSumByMonth.item,
Q_ExImItemSumByMonth.ThisMonth,
Q_ExImItemSumByMonth.SumOfamount,
Q_ExImItemSumByMonth.LastMonth,
Q_ExImItemSumByMonth_1.SumOfamount AS LastMonthsSum
FROM Q_ExImItemSumByMonth
LEFT JOIN Q_ExImItemSumByMonth AS Q_ExImItemSumByMonth_1
ON (Q_ExImItemSumByMonth.LastMonth = Q_ExImItemSumByMonth_1.ThisMonth)
AND (Q_ExImItemSumByMonth.item = Q_ExImItemSumByMonth_1.item);
item ThisMonth SumOfamount LastMonth LastMonthsSum
---- --------- ----------- --------- -------------
Bag 12/1/2004 $4.00 11/1/2004
Bag 1/1/2005 $3.00 12/1/2004 $4.00
Bag 6/1/2005 $5.00 5/1/2005
Bag 7/1/2005 ($3.00) 6/1/2005 $5.00
Box 6/1/2005 $10.00 5/1/2005
Box 7/1/2005 $13.00 6/1/2005 $10.00
