date combination

  • Thread starter Bakr Z via AccessMonster.com
  • Start date
B

Bakr Z via AccessMonster.com

I have two tables... first one is about imported marble boards and second is
exported marble boards...each of two tables has a date and item fields ...
I want ot make a query for viewing the number imported and exported boards
acoording to each item between two dates....
the problem I am facing, the search for in date field is applied only for
either date field of the first table or for the second and not for the both?
How to solve this problem???
the result of report must be like this

item No. imported boards No. exported boards Total
 
M

Michel Walsh

Hi,


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 use a negation in front of exported boards, so the SUM will, indeed,
subtract the values.

If you have to limit the records through a condition on another field (for
the date, as example), add the required WHERE clause in each of the SELECT
of the UNION ALL statement.

Alternatively, you can write the UNION ALL query in a saved query, and use
that saved query inside the outermost crosstab query.


Other solutions are also possible, such as using joins on two total query,
as example.


Hoping it may help,
Vanderghast, Access MVP
 
Top