Stuart McLachlan
stuart at lexacorp.com.pg
Sun Jun 27 13:28:00 CDT 2010
Have another beer, it helps to clarify the mind :-) Since the report is based on Sales Info, it is likely that the queries are only returning the details of Customers with sales in the relevant periods. In that case, you should use a left join with YTD as the main table.: Select a.customer, a.mtdamount, b.ytdamount >From ytdquery as y Left join mtdquery as m On m.customer =y.customer That way you get all Customers with a YTD sales even if they have are no MTD sales. -- Stuart On 27 Jun 2010 at 7:24, David McAfee wrote: > Ottomh and I'm at a bar right now... > > Select a.customer, a.mtdamount, b.ytdamount > >From mtdquery as a > Inner join ytdquery as b > On a.customer =b.customer > > (Customerid would be a better join) > > Sent from my Droid phone. > > On Jun 26, 2010 10:28 AM, "Brad Marks" <brad.marks1 at gmail.com> wrote: > > I thought that this was going to be easy. Maybe it is, but I can´t figure > out how to do it. > > > > Background > > One table - Sales Info. 200,000 rows > > One query to return the appropriate rows based on date ranges (either MTD or > YTD) > > Two "almost identical" Reports. One for MTD, one for YTD. > > Reports do not show detail lines, they only show summary info, one report > line per Customer. This is done by Grouping on Customer and showing the > info in the Customer Footer. > > > > All of this works very nicely. > > > > Now, there is a need to "melt" the two reports into one report as I have > tried to illustrate by this simple example. > > > > ~ ~ ~ ~ ~ ~ ~ ~ ~ > > > > Customer MTD-Amt YTD-Amt > > > > Joe Smith $100.00 $1,000.00 > > Sue Smith $200.00 $1,620.00 > > John Jones $300.00 $2,938.00 > > > > > > ~ ~ ~ ~ ~ ~ ~ ~ ~ > > > > Is there an easy way to do this with Access 2007 Reports? > > > > Thanks, > > Brad > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >