Append query (insert into) changes with Union query

[hannes]

Dear All,
I really struggle because I cannot find the reason for the behaviour of my
queries. When I create a normal select query and after that I open this
select query via an append query everything works as usual. However when I
change now my "normal" select query to an union query and open it from the
append query, suddenly records are missing which are still there when I run
the union query on its own. The record which are missing with querying the
Union query have the same output

Does anyone know what can be done so that my Append query appends ALL
records to the table, even if it looks at a union query???

Example:
Series: "Snow"; Category: "1/1/2000"; ChartValue: "10"; Average: "1"
Series: "Snow"; Category: "1/1/2000"; ChartValue: "10"; Average: "1"
Series: "Snow"; Category: "1/1/2001"; ChartValue: "20"; Average: "1"
Series: "Snow"; Category: "1/1/2001"; ChartValue: "30"; Average: "1"

--> with this example the first row would disapear with the Union query,
with the "normal" select query it will show




"Normal" select query
SELECT GroupList.GroupName AS Series,
format([LastReportingDate].[reportingDate],"yyyy/mm") AS Category,
([poolBalanceOriginalFX] / 100) * 100 AS ChartValue,
1 as Average
FROM GroupList ...


"UNION Query adds now the previous one and a Union statement":
SELECT GroupList.GroupName AS Series,
format([LastReportingDate].[reportingDate],"yyyy/mm") AS Category,
([poolBalanceOriginalFX] / 100) * 100 AS ChartValue,
1 as Average
FROM GroupList ...


UNION SELECT
GroupList.GroupName AS Series,
format([LastReportingDate].[reportingDate],"yyyy/mm") AS Category,
([poolBalanceOriginalFX] / 100) * 100 AS ChartValue,
1 AS Average
FROM GroupList ...


and finally the Append QUERY:
INSERT INTO PivotChartGetData ( Series, Category, ChartValue, Average )
SELECT PivotChartGetData_qsel.Series, PivotChartGetData_qsel.Category,
PivotChartGetData_qsel.ChartValue, PivotChartGetData_qsel.Average
FROM PivotChartGetData_qsel;

 

[John Spencer]

First try using UNION ALL instead of UNION in your union query.

UNION strips out duplicated rows.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[hannes]

perfect, I tried so many different ways but with UNION ALL (Select... it
worked. I was not aware that Union strips out duplicates. Thanks.
hannes

First try using UNION ALL instead of UNION in your union query.

UNION strips out duplicated rows.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Dear All,
I really struggle because I cannot find the reason for the behaviour of my
queries. When I create a normal select query and after that I open this
select query via an append query everything works as usual. However when I
change now my "normal" select query to an union query and open it from the
append query, suddenly records are missing which are still there when I run
the union query on its own. The record which are missing with querying the
Union query have the same output

Does anyone know what can be done so that my Append query appends ALL
records to the table, even if it looks at a union query???

Example:
Series: "Snow"; Category: "1/1/2000"; ChartValue: "10"; Average: "1"
Series: "Snow"; Category: "1/1/2000"; ChartValue: "10"; Average: "1"
Series: "Snow"; Category: "1/1/2001"; ChartValue: "20"; Average: "1"
Series: "Snow"; Category: "1/1/2001"; ChartValue: "30"; Average: "1"

--> with this example the first row would disapear with the Union query,
with the "normal" select query it will show




"Normal" select query
SELECT GroupList.GroupName AS Series,
format([LastReportingDate].[reportingDate],"yyyy/mm") AS Category,
([poolBalanceOriginalFX] / 100) * 100 AS ChartValue,
1 as Average
FROM GroupList ...


"UNION Query adds now the previous one and a Union statement":
SELECT GroupList.GroupName AS Series,
format([LastReportingDate].[reportingDate],"yyyy/mm") AS Category,
([poolBalanceOriginalFX] / 100) * 100 AS ChartValue,
1 as Average
FROM GroupList ...


UNION SELECT
GroupList.GroupName AS Series,
format([LastReportingDate].[reportingDate],"yyyy/mm") AS Category,
([poolBalanceOriginalFX] / 100) * 100 AS ChartValue,
1 AS Average
FROM GroupList ...


and finally the Append QUERY:
INSERT INTO PivotChartGetData ( Series, Category, ChartValue, Average )
SELECT PivotChartGetData_qsel.Series, PivotChartGetData_qsel.Category,
PivotChartGetData_qsel.ChartValue, PivotChartGetData_qsel.Average
FROM PivotChartGetData_qsel;