UNION query question

D

Dale Peart

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
 
G

Gerald Stanley

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
 
G

Gary Walter

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
 
D

Dale Peart

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 said:
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

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
Click to expand...
Click to expand...
 
D

Dale Peart

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 said:
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

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
Click to expand...
Click to expand...
 
G

Gary Walter

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 said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Peart

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

Dale

Gary Walter said:
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 said:
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 said:
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

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
Click to expand...
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
Click to expand...
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
Click to expand...
Table
1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1,
Click to expand...
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
Click to expand...
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
Click to expand...
of
[qry
Metal Digestion Combined] and/or [qry Hg Digestion Combined Info]
Click to expand...
that
do
not exist in [tbl Field Sample Log]

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

Dale
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Missing data in report 5
MS ACCESS query 5
Update Query 3
Select to reference an other record or row 1
Query to order lines a certain way 3
Union query, combine all 4 fields into 1 field 5
Comlex union query question 5
Search any field 10

Top