Pivot table using External data

Hi

I am generating a pivot table using the following query

SELECT `'1100 REG$'`.`Ap/ArID`, `'1100 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1100
REG$'` `'1100 REG$'`
UNION
SELECT `'1157 REG$'`.`Ap/ArID`, `'1157 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1157
REG$'` `'1157 REG$'`
UNION
SELECT `'1165 REG$'`.`Ap/ArID`, `'1165 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1165
REG$'` `'1165 REG$'`

The problem I am having is that I am getting a difference in the total
amount when the same query is generated individually for 1100 REG, 1157 REG
and 1165REG and aggregated. Why?

Try UNION ALL instead of UNION.

nc wrote:
> Hi
>
> I am generating a pivot table using the following query
>
> SELECT `'1100 REG$'`.`Ap/ArID`, `'1100 REG$'`.Amount
> FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1100
> REG$'` `'1100 REG$'`
> UNION
> SELECT `'1157 REG$'`.`Ap/ArID`, `'1157 REG$'`.Amount
> FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1157
> REG$'` `'1157 REG$'`
> UNION
> SELECT `'1165 REG$'`.`Ap/ArID`, `'1165 REG$'`.Amount
> FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1165
> REG$'` `'1165 REG$'`
>
> The problem I am having is that I am getting a difference in the total
> amount when the same query is generated individually for 1100 REG, 1157 REG
> and 1165REG and aggregated. Why?
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra

Thanks, it works. What is the differene between the two?

"Debra Dalgleish" wrote:

> Try UNION ALL instead of UNION.
>
> nc wrote:
> > Hi
> >
> > I am generating a pivot table using the following query
> >
> > SELECT `'1100 REG$'`.`Ap/ArID`, `'1100 REG$'`.Amount
> > FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1100
> > REG$'` `'1100 REG$'`
> > UNION
> > SELECT `'1157 REG$'`.`Ap/ArID`, `'1157 REG$'`.Amount
> > FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1157
> > REG$'` `'1157 REG$'`
> > UNION
> > SELECT `'1165 REG$'`.`Ap/ArID`, `'1165 REG$'`.Amount
> > FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1165
> > REG$'` `'1165 REG$'`
> >
> > The problem I am having is that I am getting a difference in the total
> > amount when the same query is generated individually for 1100 REG, 1157 REG
> > and 1165REG and aggregated. Why?
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

By default, UNION excludes duplicate records. UNION ALL retains the
duplicate values.

nc wrote:
> Hi Debra
>
> Thanks, it works. What is the differene between the two?
>
> "Debra Dalgleish" wrote:
>
>
>>Try UNION ALL instead of UNION.
>>
>>nc wrote:
>>
>>>Hi
>>>
>>>I am generating a pivot table using the following query
>>>
>>>SELECT `'1100 REG$'`.`Ap/ArID`, `'1100 REG$'`.Amount
>>>FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1100
>>>REG$'` `'1100 REG$'`
>>>UNION
>>>SELECT `'1157 REG$'`.`Ap/ArID`, `'1157 REG$'`.Amount
>>>FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1157
>>>REG$'` `'1157 REG$'`
>>>UNION
>>>SELECT `'1165 REG$'`.`Ap/ArID`, `'1165 REG$'`.Amount
>>>FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1165
>>>REG$'` `'1165 REG$'`
>>>
>>>The problem I am having is that I am getting a difference in the total
>>>amount when the same query is generated individually for 1100 REG, 1157 REG
>>>and 1165REG and aggregated. Why?
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html