reference unrelated tables for calculation

[john33john33]

Table RecycleRecord
ItemName, RecordDate, Amount
Paper, 1/7/2013, 100

Table RecycleItem
ItemName, UnitInKg
Paper 1

Table PaperType
PaperType, KgPerUnit
A4 Paper, 2.5

Table PaperUsage
PaperType, RecordDate, Amount
A4 Paper, 2/7/2013, 100

I want results like PaperUsage.Amount*PaperType.KgPerUnit - RecycleItem.UnitInKg*RecycleRecord.Amount
If in the same month, then group it to see the net paper usage.

Trial:

SELECT Sum(PaperUsage.Amount*PaperType.KgPerUnit-RecycleItem.UnitInKg*RecycleRecord.RecordDate) AS Kg
FROM PaperType INNER JOIN PaperUsage ON PaperType.PaperType = PaperUsage.PaperType
INNER JOIN RecycleRecord ON Month(RecycleRecord.RecordDate) = Month(PaperUsage.RecordDate)
INNER JOIN RecycleItem ON RecycleRecord.ItemName = RecycleItem.ItemName
GROUP BY Month(PaperUsage.RecordDate), PaperUsage.RecordDate
ORDER BY PaperUsage.RecordDate;

As RecycleRecord.RecordDate is not necessary equal to PaperUsage.RecordDate, I have to use Month(RecycleRecord.RecordDate) = Month(PaperUsage.RecordDate).
However, it shows lack of operand.

Thanks for all feedback.

 

[plog]

Divide and conquer. Make 2 sub-queries--one for each pair of tables that actually link. In those subqueries also include a calculated field to determine the month. Then in a main query, link those 2 sub-queries via the month field.