UNION ALL

[ftwguy]

Can anyone tell me what is wrong with this SQL for a UNION ALL. What I end
up with is a query that only shows the fields from the very First table in
the list. I also get input boxes asking for parameter value for STORE, MILK
GAL, MILK PINT.

I'm using 5 tables. 4 of the tables were built from previous queries.

 

[Douglas J. Steele]

It would help to see the SQL...

 

[Jeff Boyce]

Care to post the SQL statement?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ftwguy]

This is what the actual sql statement looks like. All the final query looks
like is a display of the fields from the FIRST TABLE in the statement, then
it will repeat (as row records) all duplicate stores with their data, but
that data is not under a correct field. I wrongly assumed a UNION ALL QUERY
would create proper fields for each and every table in the query.

I dont see where this UNION ALL will help me compile a report.

SELECT [IBC], [Store], [Location], [City], [Zip], [DP Acct], [1Q DP 20oz],
[Coke Acct], [1Q Coke], [Pepsi Acct], [1Q non carb], [1Q aquafina], [Pepsi EC
Payment 1], [NCI Acct], [1Q NCI]
FROM [2008 Houston Table]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Shelves],
[Ozarka Payment 1], [Ozarka Payment], Null, Null, Null, Null, Null, Null, Null
FROM [Ozarka HOU Rebate Query];
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Yumi Acct], [1Q Yumi],
[Yumi Rebate], Null, Null, Null, Null, Null, Null, Null
FROM [Yumi HOU Rebate Query]
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Frito Acct], [1Q Frito],
[Frito Rebate], [Frito Growth], Null, Null, Null, Null, Null, Null
FROM [Frito Lay Hou Rebate Query]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Milk Acct], [1Q
Gal], [1Q Pint], [Gal Rebate], [Pint Rebate], Null, Null, Null, Null, Null
FROM [IBC Milk HOU Rebate Query];

 

[Jeff Boyce]

We aren't there. We can't see what you're looking at.

We don't know what your data structure looks like.

We don't know what your desired report would contain.

We don't know what you mean by "create proper fields for each and every
table in the query".

If you'll provide more specific descriptions, the folks here may be able to
offer more specific suggestions.

By the way, the statement you provided would try to put [DP Acct] data in
the same column as [Shelves] data -- are those the same "thing"? And there
are more of what looks, from a casual glance, like mis-matches.

By the way, too, if you have a table named [2008 Houston Table], your data
is probably organized perfectly ... for a spreadsheet! Access is a
relational database. You won't get the best use of Access'
relationally-oriented features/functions if you feed it 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is what the actual sql statement looks like. All the final query
looks
like is a display of the fields from the FIRST TABLE in the statement,
then
it will repeat (as row records) all duplicate stores with their data, but
that data is not under a correct field. I wrongly assumed a UNION ALL
QUERY
would create proper fields for each and every table in the query.

I dont see where this UNION ALL will help me compile a report.

SELECT [IBC], [Store], [Location], [City], [Zip], [DP Acct], [1Q DP 20oz],
[Coke Acct], [1Q Coke], [Pepsi Acct], [1Q non carb], [1Q aquafina], [Pepsi
EC
Payment 1], [NCI Acct], [1Q NCI]
FROM [2008 Houston Table]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Shelves],
[Ozarka Payment 1], [Ozarka Payment], Null, Null, Null, Null, Null, Null,
Null
FROM [Ozarka HOU Rebate Query];
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Yumi Acct], [1Q Yumi],
[Yumi Rebate], Null, Null, Null, Null, Null, Null, Null
FROM [Yumi HOU Rebate Query]
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Frito Acct], [1Q
Frito],
[Frito Rebate], [Frito Growth], Null, Null, Null, Null, Null, Null
FROM [Frito Lay Hou Rebate Query]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Milk Acct],
[1Q
Gal], [1Q Pint], [Gal Rebate], [Pint Rebate], Null, Null, Null, Null, Null
FROM [IBC Milk HOU Rebate Query];

Can anyone tell me what is wrong with this SQL for a UNION ALL. What I
end
up with is a query that only shows the fields from the very First table
in
the list. I also get input boxes asking for parameter value for STORE,
MILK
GAL, MILK PINT.

I'm using 5 tables. 4 of the tables were built from previous queries.

 

[Conan Kelly]

ftwguy,

Are you getting an error? If so, post the error message. Are you getting
results, but the wrong results? How is it not working?

One thing I noticed, there is a semicolon after the second select statement.
Change...

FROM [Ozarka HOU Rebate Query];

....to...

FROM [Ozarka HOU Rebate Query]

HTH,

Conan

This is what the actual sql statement looks like. All the final query
looks
like is a display of the fields from the FIRST TABLE in the statement,
then
it will repeat (as row records) all duplicate stores with their data, but
that data is not under a correct field. I wrongly assumed a UNION ALL
QUERY
would create proper fields for each and every table in the query.

I dont see where this UNION ALL will help me compile a report.

SELECT [IBC], [Store], [Location], [City], [Zip], [DP Acct], [1Q DP 20oz],
[Coke Acct], [1Q Coke], [Pepsi Acct], [1Q non carb], [1Q aquafina], [Pepsi
EC
Payment 1], [NCI Acct], [1Q NCI]
FROM [2008 Houston Table]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Shelves],
[Ozarka Payment 1], [Ozarka Payment], Null, Null, Null, Null, Null, Null,
Null
FROM [Ozarka HOU Rebate Query];
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Yumi Acct], [1Q Yumi],
[Yumi Rebate], Null, Null, Null, Null, Null, Null, Null
FROM [Yumi HOU Rebate Query]
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Frito Acct], [1Q
Frito],
[Frito Rebate], [Frito Growth], Null, Null, Null, Null, Null, Null
FROM [Frito Lay Hou Rebate Query]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Milk Acct],
[1Q
Gal], [1Q Pint], [Gal Rebate], [Pint Rebate], Null, Null, Null, Null, Null
FROM [IBC Milk HOU Rebate Query];

Can anyone tell me what is wrong with this SQL for a UNION ALL. What I
end
up with is a query that only shows the fields from the very First table
in
the list. I also get input boxes asking for parameter value for STORE,
MILK
GAL, MILK PINT.

I'm using 5 tables. 4 of the tables were built from previous queries.

 

[John W. Vinson]

This is what the actual sql statement looks like. All the final query looks
like is a display of the fields from the FIRST TABLE in the statement, then
it will repeat (as row records) all duplicate stores with their data, but
that data is not under a correct field. I wrongly assumed a UNION ALL QUERY
would create proper fields for each and every table in the query.

I dont see where this UNION ALL will help me compile a report.

SELECT [IBC], [Store], [Location], [City], [Zip], [DP Acct], [1Q DP 20oz],
[Coke Acct], [1Q Coke], [Pepsi Acct], [1Q non carb], [1Q aquafina], [Pepsi EC
Payment 1], [NCI Acct], [1Q NCI]
FROM [2008 Houston Table]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Shelves],
[Ozarka Payment 1], [Ozarka Payment], Null, Null, Null, Null, Null, Null, Null
FROM [Ozarka HOU Rebate Query];
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Yumi Acct], [1Q Yumi],
[Yumi Rebate], Null, Null, Null, Null, Null, Null, Null
FROM [Yumi HOU Rebate Query]
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Frito Acct], [1Q Frito],
[Frito Rebate], [Frito Growth], Null, Null, Null, Null, Null, Null
FROM [Frito Lay Hou Rebate Query]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Milk Acct], [1Q
Gal], [1Q Pint], [Gal Rebate], [Pint Rebate], Null, Null, Null, Null, Null
FROM [IBC Milk HOU Rebate Query];

The query is operating as designed.

A UNION query uses the fieldnames specified in the first SELECT statement as
the fieldnames for the resulting composite recordset.

That is, the field [1Q DP 20 Oz] will contain whatever is in the sixth field
of each query - whether that was originally called [1Q DP 20 Oz], [Shelves],
[1Q Yumi] or [Milk acct].

Mixiing Diet Pepsi and milk sounds rather unappetizing to me, but that's
basically what you're doing with this query!

Clearly that isn't your intent; what result do you WANT to see?

 

[ftwguy]

Conan...I don't put those semi colons in, Access does when I save the Union
Query.

ftwguy,

Are you getting an error? If so, post the error message. Are you getting
results, but the wrong results? How is it not working?

One thing I noticed, there is a semicolon after the second select statement.
Change...

FROM [Ozarka HOU Rebate Query];

....to...

FROM [Ozarka HOU Rebate Query]

HTH,

Conan

This is what the actual sql statement looks like. All the final query
looks
like is a display of the fields from the FIRST TABLE in the statement,
then
it will repeat (as row records) all duplicate stores with their data, but
that data is not under a correct field. I wrongly assumed a UNION ALL
QUERY
would create proper fields for each and every table in the query.

I dont see where this UNION ALL will help me compile a report.

SELECT [IBC], [Store], [Location], [City], [Zip], [DP Acct], [1Q DP 20oz],
[Coke Acct], [1Q Coke], [Pepsi Acct], [1Q non carb], [1Q aquafina], [Pepsi
EC
Payment 1], [NCI Acct], [1Q NCI]
FROM [2008 Houston Table]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Shelves],
[Ozarka Payment 1], [Ozarka Payment], Null, Null, Null, Null, Null, Null,
Null
FROM [Ozarka HOU Rebate Query];
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Yumi Acct], [1Q Yumi],
[Yumi Rebate], Null, Null, Null, Null, Null, Null, Null
FROM [Yumi HOU Rebate Query]
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Frito Acct], [1Q
Frito],
[Frito Rebate], [Frito Growth], Null, Null, Null, Null, Null, Null
FROM [Frito Lay Hou Rebate Query]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Milk Acct],
[1Q
Gal], [1Q Pint], [Gal Rebate], [Pint Rebate], Null, Null, Null, Null, Null
FROM [IBC Milk HOU Rebate Query];

Can anyone tell me what is wrong with this SQL for a UNION ALL. What I
end
up with is a query that only shows the fields from the very First table
in
the list. I also get input boxes asking for parameter value for STORE,
MILK
GAL, MILK PINT.

I'm using 5 tables. 4 of the tables were built from previous queries.

 

[ftwguy]

John...I have stores in all 5 tables. You can tell by the SQL statement that
there are products in each table starting with DP in the first table and
ending with Milk in the last table. My customers are all listed with their
unique customer number such as HOU4567, HOU9384. They could be found in each
table because their may buy ALL products or just products from the 4th and
5th table.

I have to send them a statement of purchases and their respective rebates in
April.
Therefore, page one of my report will include everything for HOU1044...might
be from all five table products or just 3. Then it provides a TOTAL rebate
amount at the bottom of the report based on SUM of the listed product
rebates/purchases.

Problem is if HOU1044 only bought product from the first 3 tables and none
from the last 2 tables, then noting appears in the report...just a blank
field so that breaks my formula to sum the rebate total.

Example:
1Q DP 20oz x .10 = 25.00
1Q Coke x. 05 = 10.00
Shelves x 1.00 = 20.00
1Q Frito x .15 = 15.00
1Q Gal =blank

Customer didn't buy milk....so no integer value for milk and my forumla is
then busted for a total in my report. If it just returned a ZERO integer
then I could still total, but a blank kills it

This is what the actual sql statement looks like. All the final query looks
like is a display of the fields from the FIRST TABLE in the statement, then
it will repeat (as row records) all duplicate stores with their data, but
that data is not under a correct field. I wrongly assumed a UNION ALL QUERY
would create proper fields for each and every table in the query.

I dont see where this UNION ALL will help me compile a report.

SELECT [IBC], [Store], [Location], [City], [Zip], [DP Acct], [1Q DP 20oz],
[Coke Acct], [1Q Coke], [Pepsi Acct], [1Q non carb], [1Q aquafina], [Pepsi EC
Payment 1], [NCI Acct], [1Q NCI]
FROM [2008 Houston Table]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Shelves],
[Ozarka Payment 1], [Ozarka Payment], Null, Null, Null, Null, Null, Null, Null
FROM [Ozarka HOU Rebate Query];
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Yumi Acct], [1Q Yumi],
[Yumi Rebate], Null, Null, Null, Null, Null, Null, Null
FROM [Yumi HOU Rebate Query]
UNION ALL
SELECT [IBC], [Store], [Location], [City], [Zip], [Frito Acct], [1Q Frito],
[Frito Rebate], [Frito Growth], Null, Null, Null, Null, Null, Null
FROM [Frito Lay Hou Rebate Query]
UNION ALL SELECT [IBC], [Store], [Location], [City], [Zip], [Milk Acct], [1Q
Gal], [1Q Pint], [Gal Rebate], [Pint Rebate], Null, Null, Null, Null, Null
FROM [IBC Milk HOU Rebate Query];

The query is operating as designed.

A UNION query uses the fieldnames specified in the first SELECT statement as
the fieldnames for the resulting composite recordset.

That is, the field [1Q DP 20 Oz] will contain whatever is in the sixth field
of each query - whether that was originally called [1Q DP 20 Oz], [Shelves],
[1Q Yumi] or [Milk acct].

Mixiing Diet Pepsi and milk sounds rather unappetizing to me, but that's
basically what you're doing with this query!

Clearly that isn't your intent; what result do you WANT to see?

 

[John W. Vinson]

John...I have stores in all 5 tables. You can tell by the SQL statement that
there are products in each table starting with DP in the first table and
ending with Milk in the last table. My customers are all listed with their
unique customer number such as HOU4567, HOU9384. They could be found in each
table because their may buy ALL products or just products from the 4th and
5th table.

Again:

You are missing the point of how UNION queries work.

The sixth field in the UNION query will consist of all of the values (null or
not) from the sixth field in any of the queries. The sixth field of the first
query is [1Q DP 20 oz]; the sixth field of the third query is [Yumi Acct]. It
makes NO DIFFERENCE WHATSOEVER what the fieldname is in each query - just the
relative position of the field! The sixth field in your UNION query will have
values from [1Q DP 20 oz], [Shelves], [Yumi Acct] and so on, all mixed
together higgledy-piggledy. If that's not the result you want - Don't use a
UNION query!!!!

I have to send them a statement of purchases and their respective rebates in
April.
Therefore, page one of my report will include everything for HOU1044...might
be from all five table products or just 3. Then it provides a TOTAL rebate
amount at the bottom of the report based on SUM of the listed product
rebates/purchases.

Problem is if HOU1044 only bought product from the first 3 tables and none
from the last 2 tables, then noting appears in the report...just a blank
field so that breaks my formula to sum the rebate total.

Example:
1Q DP 20oz x .10 = 25.00
1Q Coke x. 05 = 10.00
Shelves x 1.00 = 20.00
1Q Frito x .15 = 15.00
1Q Gal =blank

Customer didn't buy milk....so no integer value for milk and my forumla is
then busted for a total in my report. If it just returned a ZERO integer
then I could still total, but a blank kills it

Even if a UNION query is appropriate - and I don't think it is, since there is
nothing whatsoever in this query to identify a purchase as Coke or Milk or
Fritos - you will need to use the NZ() function to convert null to zero. The
1Q Gal value isn't blank (a zero length Text string); it's NULL, meaning "no
value assigned, undefined, uninitialized". To sum NULL values along with other
values use NZ([fieldname]) - this will convert the NULL to a zero.