David McAfee
davidmcafee at gmail.com
Mon Jun 28 12:01:52 CDT 2010
wow. totally forgot I answered that. Stuart is right about the Left Join. The Black & Tan that I was drinking may have clouded my mind... ;) On Sun, Jun 27, 2010 at 11:28 AM, Stuart McLachlan <stuart at lexacorp.com.pg> wrote: > 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 >> > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >