[AccessD] Report from separated tables

Ervin Brindza viner at eunet.yu
Mon Dec 15 06:15:29 CST 2003


Hi,
the situation is: there are 3 tables:
tblMain(Id, fldDate)
tblFirst(Id, fldItemId, Amount1, Amount2)
tblSecond(Id, fldItemId, Amount3, Amount4)
The tblMain is on the 1 side, and tblFirst and tblSecond are on the many side. 
And there is a need for summing only those records (all of them) where are the sum of Amount1>0 or Amount2>0; and Amount3>0 or Amount4>0
E.g.
tblMain
1 01/01/2003
2 02/02/2003
(I'll use ItemName instead of ItemId)
tblFirst
1 sugar   1.00   2.00
1 salt       3.00   4.00
2 sugar 10.00 12.00
2 water  13.00 14.00
2 pepper 0.00   0.00

tblSecond
1 sugar 11.00   2.00
1 salt       3.00   4.00
2 sugar 10.00 12.00
2 vine   23.00 24.00

The report should show all used ingredients and sum the aomunts separately:
1 sugar 32.00 28.00
2 salt      6.00   8.00
3 water 13.00 14.00
4 vine   23.00 24.00
May I use union query, subreport or something else?
Many TIA,
    Ervin


More information about the AccessD mailing list