[AccessD] Report from separated tables

Gustav Brock gustav at cactus.dk
Mon Dec 15 08:05:21 CST 2003


Hi Ervin

You should be able to use a Union All query for tblFirst and tblSecond
filtering for records with amounts larger than zero. Then use this as
the source for a totals query where you Group By ItemID and Sum the
amounts. 

/gustav

> 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?



More information about the AccessD mailing list