Union IIF statement

G

Guest

I have a form with a listbox that I am trying to populate with some
information to show two columns. I am trying to do use a Union from 3
tables, but for right now I am just trying to get 2 to work. In the 3 tables
one of the columns uses a number to identify to a different table, however if
the number is "0" then I want to it say "Overall" in the second column or the
actual text that I am trying to relate it to. I don't know what I could be
doing wrong. Here is the language could some assist me on this? Thanks in
advance.

1st query

SELECT tblA110Comments.A110ID AS ID,
tblArt110Incident.CausedArt110IncidentReason AS Incident,
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblArt110Incident INNER JOIN tblA110Comments ON tblArt110Incident.ID =
tblA110Comments.A110Name) ON tblPracticalData.PracticalIDNumber =
tblA110Comments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblA110Comments.PractCATID
WHERE
(((tblA110Comments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblA110Comments.A110Comments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID]))));

2nd query

SELECT tblUOComments.UOID AS ID, tblUODeductions.StandardDeduction AS
Incident,
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblUODeductions INNER JOIN tblUOComments ON tblUODeductions.STID =
tblUOComments.UODeduction) ON tblPracticalData.PracticalIDNumber =
tblUOComments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblUOComments.PractCATID
WHERE
(((tblUOComments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblUOComments.UOComments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID]))));


Union

TABLE [qryA110CommentsUnComplete]
UNION TABLE [qryUOCommentsUnComplete];
 
L

Lynn Trapp

Your UNION query is not syntactically correct. Try this instead

SELECT *
FROM [qryA110CommentsUnComplete]
UNION
SELECT *
FROM [qryUOCommentsUnComplete];

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Fysh said:
I have a form with a listbox that I am trying to populate with some
information to show two columns. I am trying to do use a Union from 3
tables, but for right now I am just trying to get 2 to work. In the 3
tables
one of the columns uses a number to identify to a different table, however
if
the number is "0" then I want to it say "Overall" in the second column or
the
actual text that I am trying to relate it to. I don't know what I could
be
doing wrong. Here is the language could some assist me on this? Thanks
in
advance.

1st query

SELECT tblA110Comments.A110ID AS ID,
tblArt110Incident.CausedArt110IncidentReason AS Incident,
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblArt110Incident INNER JOIN tblA110Comments ON tblArt110Incident.ID =
tblA110Comments.A110Name) ON tblPracticalData.PracticalIDNumber =
tblA110Comments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblA110Comments.PractCATID
WHERE
(((tblA110Comments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblA110Comments.A110Comments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID]))));

2nd query

SELECT tblUOComments.UOID AS ID, tblUODeductions.StandardDeduction AS
Incident,
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblUODeductions INNER JOIN tblUOComments ON tblUODeductions.STID =
tblUOComments.UODeduction) ON tblPracticalData.PracticalIDNumber =
tblUOComments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblUOComments.PractCATID
WHERE
(((tblUOComments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblUOComments.UOComments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID]))));


Union

TABLE [qryA110CommentsUnComplete]
UNION TABLE [qryUOCommentsUnComplete];
Click to expand...
 
G

Guest

Actually I finally got it. I had the tblPracticalCategory and tblUOComments
joined as well as the others in the other query. I deleted the joins and it
seems to work. I guess it was looking for the "0" and couldn't find it. But
I will try your method as well.

Lynn Trapp said:
Your UNION query is not syntactically correct. Try this instead

SELECT *
FROM [qryA110CommentsUnComplete]
UNION
SELECT *
FROM [qryUOCommentsUnComplete];

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Fysh said:
I have a form with a listbox that I am trying to populate with some
information to show two columns. I am trying to do use a Union from 3
tables, but for right now I am just trying to get 2 to work. In the 3
tables
one of the columns uses a number to identify to a different table, however
if
the number is "0" then I want to it say "Overall" in the second column or
the
actual text that I am trying to relate it to. I don't know what I could
be
doing wrong. Here is the language could some assist me on this? Thanks
in
advance.

1st query

SELECT tblA110Comments.A110ID AS ID,
tblArt110Incident.CausedArt110IncidentReason AS Incident,
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblArt110Incident INNER JOIN tblA110Comments ON tblArt110Incident.ID =
tblA110Comments.A110Name) ON tblPracticalData.PracticalIDNumber =
tblA110Comments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblA110Comments.PractCATID
WHERE
(((tblA110Comments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblA110Comments.A110Comments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID]))));

2nd query

SELECT tblUOComments.UOID AS ID, tblUODeductions.StandardDeduction AS
Incident,
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblUODeductions INNER JOIN tblUOComments ON tblUODeductions.STID =
tblUOComments.UODeduction) ON tblPracticalData.PracticalIDNumber =
tblUOComments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblUOComments.PractCATID
WHERE
(((tblUOComments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblUOComments.UOComments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID]))));


Union

TABLE [qryA110CommentsUnComplete]
UNION TABLE [qryUOCommentsUnComplete];
Click to expand...
Click to expand...
 
G

Guest

Now that I got this working how do I set the order of the Union by the
category, then by Overall? The reason I am asking is that once this info is
displayed in the listbox the user will select an uncompleted item, a pop-up
box will appear with the associated info, they will fill out the rest of the
info, close the pop-up and requery the listbox. However, I want to display
the info in order so they don't get confused. Thanks

Lynn Trapp said:
Your UNION query is not syntactically correct. Try this instead

SELECT *
FROM [qryA110CommentsUnComplete]
UNION
SELECT *
FROM [qryUOCommentsUnComplete];

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Fysh said:
I have a form with a listbox that I am trying to populate with some
information to show two columns. I am trying to do use a Union from 3
tables, but for right now I am just trying to get 2 to work. In the 3
tables
one of the columns uses a number to identify to a different table, however
if
the number is "0" then I want to it say "Overall" in the second column or
the
actual text that I am trying to relate it to. I don't know what I could
be
doing wrong. Here is the language could some assist me on this? Thanks
in
advance.

1st query

SELECT tblA110Comments.A110ID AS ID,
tblArt110Incident.CausedArt110IncidentReason AS Incident,
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblArt110Incident INNER JOIN tblA110Comments ON tblArt110Incident.ID =
tblA110Comments.A110Name) ON tblPracticalData.PracticalIDNumber =
tblA110Comments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblA110Comments.PractCATID
WHERE
(((tblA110Comments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblA110Comments.A110Comments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID]))));

2nd query

SELECT tblUOComments.UOID AS ID, tblUODeductions.StandardDeduction AS
Incident,
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID =
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblUODeductions INNER JOIN tblUOComments ON tblUODeductions.STID =
tblUOComments.UODeduction) ON tblPracticalData.PracticalIDNumber =
tblUOComments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblUOComments.PractCATID
WHERE
(((tblUOComments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblUOComments.UOComments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID]))));


Union

TABLE [qryA110CommentsUnComplete]
UNION TABLE [qryUOCommentsUnComplete];
Click to expand...
Click to expand...
 
J

JohnFol

Unions just append data from 1 tabe to another. If you want to do a grouping
use another query based on the union, ie

Select Category, . .. from MyUnionQuery group by Category . . . .

if you simply mean you want the data ordered by 2 fields, then do this

Select Category, Overall, FieldC . .. FieldN from MyUnionQuery Order by
Category, Overall




Fysh said:
Now that I got this working how do I set the order of the Union by the
category, then by Overall? The reason I am asking is that once this info
is
displayed in the listbox the user will select an uncompleted item, a
pop-up
box will appear with the associated info, they will fill out the rest of
the
info, close the pop-up and requery the listbox. However, I want to
display
the info in order so they don't get confused. Thanks

Lynn Trapp said:
Your UNION query is not syntactically correct. Try this instead

SELECT *
FROM [qryA110CommentsUnComplete]
UNION
SELECT *
FROM [qryUOCommentsUnComplete];

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Fysh said:
I have a form with a listbox that I am trying to populate with some
information to show two columns. I am trying to do use a Union from 3
tables, but for right now I am just trying to get 2 to work. In the 3
tables
one of the columns uses a number to identify to a different table,
however
if
the number is "0" then I want to it say "Overall" in the second column
or
the
actual text that I am trying to relate it to. I don't know what I
could
be
doing wrong. Here is the language could some assist me on this?
Thanks
in
advance.

1st query

SELECT tblA110Comments.A110ID AS ID,
tblArt110Incident.CausedArt110IncidentReason AS Incident,
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID
=
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblArt110Incident INNER JOIN tblA110Comments ON tblArt110Incident.ID =
tblA110Comments.A110Name) ON tblPracticalData.PracticalIDNumber =
tblA110Comments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblA110Comments.PractCATID
WHERE
(((tblA110Comments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblA110Comments.A110Comments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID]))));

2nd query

SELECT tblUOComments.UOID AS ID, tblUODeductions.StandardDeduction AS
Incident,
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID
=
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblUODeductions INNER JOIN tblUOComments ON tblUODeductions.STID =
tblUOComments.UODeduction) ON tblPracticalData.PracticalIDNumber =
tblUOComments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblUOComments.PractCATID
WHERE
(((tblUOComments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblUOComments.UOComments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID]))));


Union

TABLE [qryA110CommentsUnComplete]
UNION TABLE [qryUOCommentsUnComplete];
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Actually, they are in the same field. In the 2 queries one column determines
if a category is used or Overall is used by the IIF statement. I am trying
to get it displayed by the category then "Overall" in the same column, in
that order for the listbox. Is this possible? Thanks

JohnFol said:
Unions just append data from 1 tabe to another. If you want to do a grouping
use another query based on the union, ie

Select Category, . .. from MyUnionQuery group by Category . . . .

if you simply mean you want the data ordered by 2 fields, then do this

Select Category, Overall, FieldC . .. FieldN from MyUnionQuery Order by
Category, Overall




Fysh said:
Now that I got this working how do I set the order of the Union by the
category, then by Overall? The reason I am asking is that once this info
is
displayed in the listbox the user will select an uncompleted item, a
pop-up
box will appear with the associated info, they will fill out the rest of
the
info, close the pop-up and requery the listbox. However, I want to
display
the info in order so they don't get confused. Thanks

Lynn Trapp said:
Your UNION query is not syntactically correct. Try this instead

SELECT *
FROM [qryA110CommentsUnComplete]
UNION
SELECT *
FROM [qryUOCommentsUnComplete];

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


I have a form with a listbox that I am trying to populate with some
information to show two columns. I am trying to do use a Union from 3
tables, but for right now I am just trying to get 2 to work. In the 3
tables
one of the columns uses a number to identify to a different table,
however
if
the number is "0" then I want to it say "Overall" in the second column
or
the
actual text that I am trying to relate it to. I don't know what I
could
be
doing wrong. Here is the language could some assist me on this?
Thanks
in
advance.

1st query

SELECT tblA110Comments.A110ID AS ID,
tblArt110Incident.CausedArt110IncidentReason AS Incident,
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID
=
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblArt110Incident INNER JOIN tblA110Comments ON tblArt110Incident.ID =
tblA110Comments.A110Name) ON tblPracticalData.PracticalIDNumber =
tblA110Comments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblA110Comments.PractCATID
WHERE
(((tblA110Comments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblA110Comments.A110Comments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID]))));

2nd query

SELECT tblUOComments.UOID AS ID, tblUODeductions.StandardDeduction AS
Incident,
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID
=
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblUODeductions INNER JOIN tblUOComments ON tblUODeductions.STID =
tblUOComments.UODeduction) ON tblPracticalData.PracticalIDNumber =
tblUOComments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblUOComments.PractCATID
WHERE
(((tblUOComments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblUOComments.UOComments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID]))));


Union

TABLE [qryA110CommentsUnComplete]
UNION TABLE [qryUOCommentsUnComplete];
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Never mind it's not exactly what I want, but it will do. Instead of putting
Overall to show up last I ended up showing it first in the column by changing
it to - Overall -, which might show up better anyway.

JohnFol said:
Unions just append data from 1 tabe to another. If you want to do a grouping
use another query based on the union, ie

Select Category, . .. from MyUnionQuery group by Category . . . .

if you simply mean you want the data ordered by 2 fields, then do this

Select Category, Overall, FieldC . .. FieldN from MyUnionQuery Order by
Category, Overall




Fysh said:
Now that I got this working how do I set the order of the Union by the
category, then by Overall? The reason I am asking is that once this info
is
displayed in the listbox the user will select an uncompleted item, a
pop-up
box will appear with the associated info, they will fill out the rest of
the
info, close the pop-up and requery the listbox. However, I want to
display
the info in order so they don't get confused. Thanks

Lynn Trapp said:
Your UNION query is not syntactically correct. Try this instead

SELECT *
FROM [qryA110CommentsUnComplete]
UNION
SELECT *
FROM [qryUOCommentsUnComplete];

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


I have a form with a listbox that I am trying to populate with some
information to show two columns. I am trying to do use a Union from 3
tables, but for right now I am just trying to get 2 to work. In the 3
tables
one of the columns uses a number to identify to a different table,
however
if
the number is "0" then I want to it say "Overall" in the second column
or
the
actual text that I am trying to relate it to. I don't know what I
could
be
doing wrong. Here is the language could some assist me on this?
Thanks
in
advance.

1st query

SELECT tblA110Comments.A110ID AS ID,
tblArt110Incident.CausedArt110IncidentReason AS Incident,
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID
=
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblArt110Incident INNER JOIN tblA110Comments ON tblArt110Incident.ID =
tblA110Comments.A110Name) ON tblPracticalData.PracticalIDNumber =
tblA110Comments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblA110Comments.PractCATID
WHERE
(((tblA110Comments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblA110Comments.A110Comments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblA110Comments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblA110Comments].[PractCATID]))));

2nd query

SELECT tblUOComments.UOID AS ID, tblUODeductions.StandardDeduction AS
Incident,
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID])))) AS PractCATID
FROM (tblCategory INNER JOIN tblPracticalCategory ON tblCategory.CATID
=
tblPracticalCategory.CATID) INNER JOIN (tblPracticalData INNER JOIN
(tblUODeductions INNER JOIN tblUOComments ON tblUODeductions.STID =
tblUOComments.UODeduction) ON tblPracticalData.PracticalIDNumber =
tblUOComments.PracticalIDNumber) ON tblPracticalCategory.PRACTCATID =
tblUOComments.PractCATID
WHERE
(((tblUOComments.PracticalIDNumber)=[forms]![frmEvaluation].[PracticalIDNumber])
AND ((tblUOComments.UOComments) Is Null) AND
((tblPracticalData.StudentOne)=[forms]![frmEvaluation].[txtStu1]))
ORDER BY
IIf("tblUOComments.PractCatID"="0","Overall",DLookUp("Category","tblCategory","CATID="
& (DLookUp("PRACTCATID","tblPracticalCategory","PRACTCATID=" &
[tblUOComments].[PractCATID]))));


Union

TABLE [qryA110CommentsUnComplete]
UNION TABLE [qryUOCommentsUnComplete];
Click to expand...
Click to expand...
Click to expand...
 
Top