UNION query question

This question relates to an earlier post (7/9/04).
I am trying to combine all the records of Table1 with all the results of
Query 1 and Query2. The tables from the queries have some fields that are
not included in Table 1 but need to be included in the result. Also Table1
has columns that are not in the results of the queries.
Because a UNION query requires the same number of columns in each table and
the missing columns in Tables 2 & 3 are padded with "NULL," I get a type
mismatch error when trying to put a "NULL" in the first select statement.

Say field3 does not exist in Query1 and field4 does not exist in Table 1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1, field2, NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' generates a pop-up box requesting input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' runs for a few seconds and then generates
a "type mismatch" error.

Here is the text of the UNION query that works:

SELECT BotID, ProjectID, FieldID, Site, SampDate, SampTime, LogDate,
SampTyp, Request, Blank, SeqNo, [Weight (g)], Desc, [Referring LabID],
DigestDate, [Dilution Volume], InitialVol FROM [tbl Field Sample Log] UNION
SELECT BotID, ProjectID, NULL, NULL, NULL, NULL, NULL, NULL, NULL, Blank,
NULL, [Weight (g)], NULL, [Referring LabID], DigestDate, NULL, InitialVol
FROM [qry Metal Digestion Combined] UNION SELECT BotID, ProjectID, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, Blank, NULL, [Weight (g)], NULL,
[Referring LabID], DigestDate, NULL, NULL FROM [qry Hg Digestion Combined
Info];

The bottom line is I need to know how to add a field from the result of [qry
Metal Digestion Combined] and/or [qry Hg Digestion Combined Info] that do
not exist in [tbl Field Sample Log]

I know very little about SQL. Is there a solution to this problem?

Dale

You cannot use NULL in the first SELECT statement as it
does not then define the dataType of its column for the
subsequent elements. Instead, use a 'default' value
appropriate to the dataType e.g. "" for string, 0 for number.
In these cases where you know that not each element can
return all the columns, I also like to include a recType
column that tells me which element of the UNION produced
the row. This is easily done by

SELECT 1 As Rectype, etc
UNION
SELECT 2, etc
UNION
SELECT 3, etc

Depending upon how you use the results, you can incorporate
logic so that you know what what columns to process and
which contain no valid data based upon the rectype column.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
This question relates to an earlier post (7/9/04).
I am trying to combine all the records of Table1 with all
the results of
Query 1 and Query2. The tables from the queries have some
fields that are
not included in Table 1 but need to be included in the
result. Also Table1
has columns that are not in the results of the queries.
Because a UNION query requires the same number of columns
in each table and
the missing columns in Tables 2 & 3 are padded with
"NULL," I get a type
mismatch error when trying to put a "NULL" in the first
select statement.

Say field3 does not exist in Query1 and field4 does not
exist in Table 1.
'SELECT field1, field2, field3 from Table1 UNION SELECT
field1, field2, NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION
SELECT field1,
field2, NULL, field4 from Query1' generates a pop-up box
requesting input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION
SELECT field1,
field2, NULL, field4 from Query1' runs for a few seconds
and then generates
a "type mismatch" error.

Here is the text of the UNION query that works:

SELECT BotID, ProjectID, FieldID, Site, SampDate,
SampTime, LogDate,
SampTyp, Request, Blank, SeqNo, [Weight (g)], Desc,
[Referring LabID],
DigestDate, [Dilution Volume], InitialVol FROM [tbl Field
Sample Log] UNION
SELECT BotID, ProjectID, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, Blank,
NULL, [Weight (g)], NULL, [Referring LabID], DigestDate,
NULL, InitialVol
FROM [qry Metal Digestion Combined] UNION SELECT BotID,
ProjectID, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, Blank, NULL,
[Weight (g)], NULL,
[Referring LabID], DigestDate, NULL, NULL FROM [qry Hg
Digestion Combined
Info];

The bottom line is I need to know how to add a field from
the result of [qry
Metal Digestion Combined] and/or [qry Hg Digestion
Combined Info] that do
not exist in [tbl Field Sample Log]

I know very little about SQL. Is there a solution to this
problem?

Dale


.

Hi Dale,

I did not read your post "carefully",
but this may help:

Instead of using "NULL", a clever method Michel once demonstrated
uses IIF where final argument of IIF determines "type of Null":

-- to end up with date/time field, all nulls
IIf(True,Null,#1/1/1900#)

-- works for text and number also
IIf(True,Null," ") --get type text(255), all null
IIf(True,Null,0) --get type Long, all null

-- or use Cxxx functions
IIf(True,Null,CCur(0)) --get type Currency, all null
IIf(True,Null,CDbl(0)) --get type Double, all null

So...figure out what type those "NULL's" should be,
and replace with one of above.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

"Dale Peart" wrote
This question relates to an earlier post (7/9/04).
I am trying to combine all the records of Table1 with all the results of
Query 1 and Query2. The tables from the queries have some fields that are
not included in Table 1 but need to be included in the result. Also Table1
has columns that are not in the results of the queries.
Because a UNION query requires the same number of columns in each table and
the missing columns in Tables 2 & 3 are padded with "NULL," I get a type
mismatch error when trying to put a "NULL" in the first select statement.

Say field3 does not exist in Query1 and field4 does not exist in Table 1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1, field2, NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' generates a pop-up box requesting input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' runs for a few seconds and then generates
a "type mismatch" error.

Here is the text of the UNION query that works:

SELECT BotID, ProjectID, FieldID, Site, SampDate, SampTime, LogDate,
SampTyp, Request, Blank, SeqNo, [Weight (g)], Desc, [Referring LabID],
DigestDate, [Dilution Volume], InitialVol FROM [tbl Field Sample Log] UNION
SELECT BotID, ProjectID, NULL, NULL, NULL, NULL, NULL, NULL, NULL, Blank,
NULL, [Weight (g)], NULL, [Referring LabID], DigestDate, NULL, InitialVol
FROM [qry Metal Digestion Combined] UNION SELECT BotID, ProjectID, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, Blank, NULL, [Weight (g)], NULL,
[Referring LabID], DigestDate, NULL, NULL FROM [qry Hg Digestion Combined
Info];

The bottom line is I need to know how to add a field from the result of [qry
Metal Digestion Combined] and/or [qry Hg Digestion Combined Info] that do
not exist in [tbl Field Sample Log]

I know very little about SQL. Is there a solution to this problem?

Dale

Thanks Gary,

I'm not sure I understand what you are telling me. If I have a field that
is numeric double in Query 1 that doesn't exist in Table 1 or Query 2 are
you telling me to code something like:

SELECT BotID, ProjectID, [IIf(True,Null,Csng(0))]
FROM [tbl Field Sample Log]
UNION SELECT BotID, ProjectID, InitialVol
FROM [qry Metal Digestion Combined]
UNION SELECT BotID, ProjectID, NULL
FROM [qry Hg Digestion Combined Info];

Dale


"Gary Walter" wrote in message
...
Hi Dale,

I did not read your post "carefully",
but this may help:

Instead of using "NULL", a clever method Michel once demonstrated
uses IIF where final argument of IIF determines "type of Null":

-- to end up with date/time field, all nulls
IIf(True,Null,#1/1/1900#)

-- works for text and number also
IIf(True,Null," ") --get type text(255), all null
IIf(True,Null,0) --get type Long, all null

-- or use Cxxx functions
IIf(True,Null,CCur(0)) --get type Currency, all null
IIf(True,Null,CDbl(0)) --get type Double, all null

So...figure out what type those "NULL's" should be,
and replace with one of above.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

"Dale Peart" wrote
This question relates to an earlier post (7/9/04).
I am trying to combine all the records of Table1 with all the results of
Query 1 and Query2. The tables from the queries have some fields that
are
not included in Table 1 but need to be included in the result. Also
Table1
has columns that are not in the results of the queries.
Because a UNION query requires the same number of columns in each table
and
the missing columns in Tables 2 & 3 are padded with "NULL," I get a type
mismatch error when trying to put a "NULL" in the first select
statement.

Say field3 does not exist in Query1 and field4 does not exist in Table
1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1, field2,
NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' generates a pop-up box requesting
input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' runs for a few seconds and then
generates
a "type mismatch" error.

Here is the text of the UNION query that works:

SELECT BotID, ProjectID, FieldID, Site, SampDate, SampTime, LogDate,
SampTyp, Request, Blank, SeqNo, [Weight (g)], Desc, [Referring LabID],
DigestDate, [Dilution Volume], InitialVol FROM [tbl Field Sample Log]
UNION
SELECT BotID, ProjectID, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
Blank,
NULL, [Weight (g)], NULL, [Referring LabID], DigestDate, NULL,
InitialVol
FROM [qry Metal Digestion Combined] UNION SELECT BotID, ProjectID, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, Blank, NULL, [Weight (g)], NULL,
[Referring LabID], DigestDate, NULL, NULL FROM [qry Hg Digestion
Combined
Info];

The bottom line is I need to know how to add a field from the result of
[qry
Metal Digestion Combined] and/or [qry Hg Digestion Combined Info] that
do
not exist in [tbl Field Sample Log]

I know very little about SQL. Is there a solution to this problem?

Dale

Thanks Gary,

I'm not sure I understand what you are telling me to code. If I have a
field (InitialVol) that does not exist in Table1 or Query2 but does exist in
Query 1 are you suggesting a statement like:

SELECT BotID, ProjectID, Site, [IIf(True,Null,Csng(0))]
FROM [tbl Field Sample Log]
UNION SELECT BotID, ProjectID, NULL, InitialVol
FROM [qry Metal Digestion Combined]
UNION SELECT BotID, ProjectID, ,NULL, NULL
FROM [qry Hg Digestion Combined Info];

Dale


"Gary Walter" wrote in message
...
Hi Dale,

I did not read your post "carefully",
but this may help:

Instead of using "NULL", a clever method Michel once demonstrated
uses IIF where final argument of IIF determines "type of Null":

-- to end up with date/time field, all nulls
IIf(True,Null,#1/1/1900#)

-- works for text and number also
IIf(True,Null," ") --get type text(255), all null
IIf(True,Null,0) --get type Long, all null

-- or use Cxxx functions
IIf(True,Null,CCur(0)) --get type Currency, all null
IIf(True,Null,CDbl(0)) --get type Double, all null

So...figure out what type those "NULL's" should be,
and replace with one of above.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

"Dale Peart" wrote
This question relates to an earlier post (7/9/04).
I am trying to combine all the records of Table1 with all the results of
Query 1 and Query2. The tables from the queries have some fields that
are
not included in Table 1 but need to be included in the result. Also
Table1
has columns that are not in the results of the queries.
Because a UNION query requires the same number of columns in each table
and
the missing columns in Tables 2 & 3 are padded with "NULL," I get a type
mismatch error when trying to put a "NULL" in the first select
statement.

Say field3 does not exist in Query1 and field4 does not exist in Table
1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1, field2,
NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' generates a pop-up box requesting
input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' runs for a few seconds and then
generates
a "type mismatch" error.

Here is the text of the UNION query that works:

SELECT BotID, ProjectID, FieldID, Site, SampDate, SampTime, LogDate,
SampTyp, Request, Blank, SeqNo, [Weight (g)], Desc, [Referring LabID],
DigestDate, [Dilution Volume], InitialVol FROM [tbl Field Sample Log]
UNION
SELECT BotID, ProjectID, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
Blank,
NULL, [Weight (g)], NULL, [Referring LabID], DigestDate, NULL,
InitialVol
FROM [qry Metal Digestion Combined] UNION SELECT BotID, ProjectID, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, Blank, NULL, [Weight (g)], NULL,
[Referring LabID], DigestDate, NULL, NULL FROM [qry Hg Digestion
Combined
Info];

The bottom line is I need to know how to add a field from the result of
[qry
Metal Digestion Combined] and/or [qry Hg Digestion Combined Info] that
do
not exist in [tbl Field Sample Log]

I know very little about SQL. Is there a solution to this problem?

Dale

Hi Dale,

A "plain NULL" returns type **binary**

Focus on the fields in the 3 SELECT clauses;
line them up visually in 3 rows/4 columns,
and when you don't have a field
in a row, replace "NULL" with an
IIF that provides the same type
as the field that is not Null in the
same "column."

BotID, ProjectID, Site, NULL
BotID, ProjectID, NULL, InitialVol
BotID, ProjectID, , NULL, NULL

so if [Site] were type TEXT,
and [InitialVol] were type SINGLE,
the fields in the SELECT clauses would "align up" so:

BotID, ProjectID, Site, IIf(True,Null,CSng(0)) As InitialVol
BotID, ProjectID, IIf(True,Null," "), InitialVol
BotID, ProjectID, IIf(True,Null," "), IIf(True,Null,CSng(0))

so your UNION query would look like:


SELECT BotID, ProjectID, Site, IIf(True,Null,CSng(0)) As InitialVol
FROM [tbl Field Sample Log]
UNION
SELECT BotID, ProjectID, IIf(True,Null," "), InitialVol
FROM [qry Metal Digestion Combined]
UNION
SELECT BotID, ProjectID, IIf(True,Null," "), IIf(True,Null,CSng(0))
FROM [qry Hg Digestion Combined Info];

Good luck,

Gary Walter



"Dale Peart" wrote:

I'm not sure I understand what you are telling me to code. If I have a
field (InitialVol) that does not exist in Table1 or Query2 but does exist in
Query 1 are you suggesting a statement like:

SELECT BotID, ProjectID, Site, [IIf(True,Null,Csng(0))]
FROM [tbl Field Sample Log]
UNION SELECT BotID, ProjectID, NULL, InitialVol
FROM [qry Metal Digestion Combined]
UNION SELECT BotID, ProjectID, ,NULL, NULL
FROM [qry Hg Digestion Combined Info];

Dale


"Gary Walter" wrote in message
...
Hi Dale,

I did not read your post "carefully",
but this may help:

Instead of using "NULL", a clever method Michel once demonstrated
uses IIF where final argument of IIF determines "type of Null":

-- to end up with date/time field, all nulls
IIf(True,Null,#1/1/1900#)

-- works for text and number also
IIf(True,Null," ") --get type text(255), all null
IIf(True,Null,0) --get type Long, all null

-- or use Cxxx functions
IIf(True,Null,CCur(0)) --get type Currency, all null
IIf(True,Null,CDbl(0)) --get type Double, all null

So...figure out what type those "NULL's" should be,
and replace with one of above.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

"Dale Peart" wrote
This question relates to an earlier post (7/9/04).
I am trying to combine all the records of Table1 with all the results of
Query 1 and Query2. The tables from the queries have some fields that
are
not included in Table 1 but need to be included in the result. Also
Table1
has columns that are not in the results of the queries.
Because a UNION query requires the same number of columns in each table
and
the missing columns in Tables 2 & 3 are padded with "NULL," I get a type
mismatch error when trying to put a "NULL" in the first select
statement.

Say field3 does not exist in Query1 and field4 does not exist in Table
1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1, field2,
NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' generates a pop-up box requesting
input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' runs for a few seconds and then
generates
a "type mismatch" error.

Here is the text of the UNION query that works:

SELECT BotID, ProjectID, FieldID, Site, SampDate, SampTime, LogDate,
SampTyp, Request, Blank, SeqNo, [Weight (g)], Desc, [Referring LabID],
DigestDate, [Dilution Volume], InitialVol FROM [tbl Field Sample Log]
UNION
SELECT BotID, ProjectID, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
Blank,
NULL, [Weight (g)], NULL, [Referring LabID], DigestDate, NULL,
InitialVol
FROM [qry Metal Digestion Combined] UNION SELECT BotID, ProjectID, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, Blank, NULL, [Weight (g)], NULL,
[Referring LabID], DigestDate, NULL, NULL FROM [qry Hg Digestion
Combined
Info];

The bottom line is I need to know how to add a field from the result of
[qry
Metal Digestion Combined] and/or [qry Hg Digestion Combined Info] that
do
not exist in [tbl Field Sample Log]

I know very little about SQL. Is there a solution to this problem?

Dale

THANK YOU! That works great! Do you have a book you recommend for learning
this stuff?

Dale

"Gary Walter" wrote in message
...
Hi Dale,

A "plain NULL" returns type **binary**

Focus on the fields in the 3 SELECT clauses;
line them up visually in 3 rows/4 columns,
and when you don't have a field
in a row, replace "NULL" with an
IIF that provides the same type
as the field that is not Null in the
same "column."

BotID, ProjectID, Site, NULL
BotID, ProjectID, NULL, InitialVol
BotID, ProjectID, , NULL, NULL

so if [Site] were type TEXT,
and [InitialVol] were type SINGLE,
the fields in the SELECT clauses would "align up" so:

BotID, ProjectID, Site, IIf(True,Null,CSng(0)) As
InitialVol
BotID, ProjectID, IIf(True,Null," "), InitialVol
BotID, ProjectID, IIf(True,Null," "), IIf(True,Null,CSng(0))

so your UNION query would look like:


SELECT BotID, ProjectID, Site, IIf(True,Null,CSng(0)) As InitialVol
FROM [tbl Field Sample Log]
UNION
SELECT BotID, ProjectID, IIf(True,Null," "), InitialVol
FROM [qry Metal Digestion Combined]
UNION
SELECT BotID, ProjectID, IIf(True,Null," "), IIf(True,Null,CSng(0))
FROM [qry Hg Digestion Combined Info];

Good luck,

Gary Walter



"Dale Peart" wrote:

I'm not sure I understand what you are telling me to code. If I have a
field (InitialVol) that does not exist in Table1 or Query2 but does
exist in
Query 1 are you suggesting a statement like:

SELECT BotID, ProjectID, Site, [IIf(True,Null,Csng(0))]
FROM [tbl Field Sample Log]
UNION SELECT BotID, ProjectID, NULL, InitialVol
FROM [qry Metal Digestion Combined]
UNION SELECT BotID, ProjectID, ,NULL, NULL
FROM [qry Hg Digestion Combined Info];

Dale


"Gary Walter" wrote in message
...
Hi Dale,

I did not read your post "carefully",
but this may help:

Instead of using "NULL", a clever method Michel once demonstrated
uses IIF where final argument of IIF determines "type of Null":

-- to end up with date/time field, all nulls
IIf(True,Null,#1/1/1900#)

-- works for text and number also
IIf(True,Null," ") --get type text(255), all null
IIf(True,Null,0) --get type Long, all null

-- or use Cxxx functions
IIf(True,Null,CCur(0)) --get type Currency, all null
IIf(True,Null,CDbl(0)) --get type Double, all null

So...figure out what type those "NULL's" should be,
and replace with one of above.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

"Dale Peart" wrote
This question relates to an earlier post (7/9/04).
I am trying to combine all the records of Table1 with all the
results of
Query 1 and Query2. The tables from the queries have some fields
that
are
not included in Table 1 but need to be included in the result. Also
Table1
has columns that are not in the results of the queries.
Because a UNION query requires the same number of columns in each
table
and
the missing columns in Tables 2 & 3 are padded with "NULL," I get a
type
mismatch error when trying to put a "NULL" in the first select
statement.

Say field3 does not exist in Query1 and field4 does not exist in
Table
1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1,
field2,
NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION SELECT
field1,
field2, NULL, field4 from Query1' generates a pop-up box requesting
input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION SELECT
field1,
field2, NULL, field4 from Query1' runs for a few seconds and then
generates
a "type mismatch" error.

Here is the text of the UNION query that works:

SELECT BotID, ProjectID, FieldID, Site, SampDate, SampTime, LogDate,
SampTyp, Request, Blank, SeqNo, [Weight (g)], Desc, [Referring
LabID],
DigestDate, [Dilution Volume], InitialVol FROM [tbl Field Sample
Log]
UNION
SELECT BotID, ProjectID, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
Blank,
NULL, [Weight (g)], NULL, [Referring LabID], DigestDate, NULL,
InitialVol
FROM [qry Metal Digestion Combined] UNION SELECT BotID, ProjectID,
NULL,
NULL, NULL, NULL, NULL, NULL, NULL, Blank, NULL, [Weight (g)],
NULL,
[Referring LabID], DigestDate, NULL, NULL FROM [qry Hg Digestion
Combined
Info];

The bottom line is I need to know how to add a field from the result
of
[qry
Metal Digestion Combined] and/or [qry Hg Digestion Combined Info]
that
do
not exist in [tbl Field Sample Log]

I know very little about SQL. Is there a solution to this problem?

Dale