[AccessD] Report from separated tables

Ok, I guess so, and need only a confirmation ;-)
Many thanks,
    Ervin
----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: 15 December, 2003 3:05 PM
Subject: Re: [AccessD] Report from separated tables


> 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?
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com