[AccessD] Report Puzzler - Melting MTD Report and YTD Report into one Combined Report

Brad Marks BradM at blackforestltd.com
Mon Jun 28 12:56:18 CDT 2010


Thanks for the help, I really appreciate it.

Brad

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Monday, June 28, 2010 12:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD]Report Puzzler - Melting MTD Report and YTD Report into one Combined Report

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
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.





More information about the AccessD mailing list