Union Query help

[ryan.fitzpatrick3]

I have a union query that pairs up two tables. The first table has a
selection of items where upon selection it'll bring up items in the
2nd table. Some selections in the first table do not bring up items in
the 2nd table simply because they're no items to bring up. This works
fine. What I'd like to do it on my union query is to pull either or
data. The tables are setup in the same format, each has 3 fields, 1 is
autonum, 2 is the item, 3 is how the 2nd table links to the 1st table.
Currently the union pulls both field 2 on both tables and puts it into
one column, which is nice, but then it double counts the item; meaning
lets say I have these selections in table one

table 1:
Fruit
Veg
Nuts

table 2:
apple
orange - for fruits in table 1
grape

walnut
pecan - for nuts in table 1
almond

lets say Veg has no sub categories.

Right now the union will pull all data

fruit, veg, nuts, apple, orange, grape, walnut, pecan, almond, so its
doubling counting fruit and its subclass items. I want it only to
bring up the lowest subclass items, and if there are no subclass items
it'll bring up that class item like Veg (since it has no subclass
item). Is this possible or is there an easier way. Here is my union
code.

SELECT [itemclass].item
FROM [itemclass]
UNION select [itemsubclass].item
from [itemsubclass ];

Thanks in advance,
Ryan

 

[Douglas J. Steele]

SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Item FROM itemsubclass)
UNION
SELECT [itemsubclass].item
from [itemsubclass ];

 

[ryan.fitzpatrick3]

Thanks for the quick reply, but it pulled the same information all 93
records.

SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Item FROM itemsubclass)
UNION
SELECT [itemsubclass].item
from [itemsubclass ];

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have a union query that pairs up two tables. The first table has a
selection of items where upon selection it'll bring up items in the
2nd table. Some selections in the first table do not bring up items in
the 2nd table simply because they're no items to bring up. This works
fine. What I'd like to do it on my union query is to pull either or
data. The tables are setup in the same format, each has 3 fields, 1 is
autonum, 2 is the item, 3 is how the 2nd table links to the 1st table.
Currently the union pulls both field 2 on both tables and puts it into
one column, which is nice, but then it double counts the item; meaning
lets say I have these selections in table one

table 1:
Fruit
Veg
Nuts

table 2:
apple
orange - for fruits in table 1
grape

walnut
pecan - for nuts in table 1
almond

lets say Veg has no sub categories.

Right now the union will pull all data

fruit, veg, nuts, apple, orange, grape, walnut, pecan, almond, so its
doubling counting fruit and its subclass items. I want it only to
bring up the lowest subclass items, and if there are no subclass items
it'll bring up that class item like Veg (since it has no subclass
item). Is this possible or is there an easier way. Here is my union
code.

SELECT [itemclass].item
FROM [itemclass]
UNION select [itemsubclass].item
from [itemsubclass ];

Thanks in advance,
Ryan

 

[Douglas J. Steele]

You're right, it would, because I didn't know the actual names of the fields
in your table.

I'm assuming that there's a field in itemsubclass that contains the item
name to point back to the itemclass table. (In other words, the rows with
apple, orange and grape must all have a field saying fruit). Put that field
name rather than item in (SELECT DISTINCT Item FROM itemsubclass)

If you don't have such a field in itemsubclass, how do you know the
itemclass to which each item in itemsubclass belongs?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the quick reply, but it pulled the same information all 93
records.

SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Item FROM itemsubclass)
UNION
SELECT [itemsubclass].item
from [itemsubclass ];

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have a union query that pairs up two tables. The first table has a
selection of items where upon selection it'll bring up items in the
2nd table. Some selections in the first table do not bring up items in
the 2nd table simply because they're no items to bring up. This works
fine. What I'd like to do it on my union query is to pull either or
data. The tables are setup in the same format, each has 3 fields, 1 is
autonum, 2 is the item, 3 is how the 2nd table links to the 1st table.
Currently the union pulls both field 2 on both tables and puts it into
one column, which is nice, but then it double counts the item; meaning
lets say I have these selections in table one

table 1:
Fruit
Veg
Nuts

table 2:
apple
orange - for fruits in table 1
grape

walnut
pecan - for nuts in table 1
almond

lets say Veg has no sub categories.

Right now the union will pull all data

fruit, veg, nuts, apple, orange, grape, walnut, pecan, almond, so its
doubling counting fruit and its subclass items. I want it only to
bring up the lowest subclass items, and if there are no subclass items
it'll bring up that class item like Veg (since it has no subclass
item). Is this possible or is there an easier way. Here is my union
code.

SELECT [itemclass].item
FROM [itemclass]
UNION select [itemsubclass].item
from [itemsubclass ];

Thanks in advance,
Ryan

 

[ryan.fitzpatrick3]

I know what your saying, I have the field names identical in both
tables.

field1 is ID, field2 is Item and field3 is Group,

Group is what links table 2 to table 1.

You're right, it would, because I didn't know the actual names of the fields
in your table.

I'm assuming that there's a field in itemsubclass that contains the item
name to point back to the itemclass table. (In other words, the rows with
apple, orange and grape must all have a field saying fruit). Put that field
name rather than item in (SELECT DISTINCT Item FROM itemsubclass)

If you don't have such a field in itemsubclass, how do you know the
itemclass to which each item in itemsubclass belongs?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Thanks for the quick reply, but it pulled the same information all 93
records.

SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Item FROM itemsubclass)
UNION
SELECT [itemsubclass].item
from [itemsubclass ];
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have a union query that pairs up two tables. The first table has a
selection of items where upon selection it'll bring up items in the
2nd table. Some selections in the first table do not bring up items in
the 2nd table simply because they're no items to bring up. This works
fine. What I'd like to do it on my union query is to pull either or
data. The tables are setup in the same format, each has 3 fields, 1 is
autonum, 2 is the item, 3 is how the 2nd table links to the 1st table.
Currently the union pulls both field 2 on both tables and puts it into
one column, which is nice, but then it double counts the item; meaning
lets say I have these selections in table one
table 1:
Fruit
Veg
Nuts
table 2:
apple
orange - for fruits in table 1
grape
walnut
pecan - for nuts in table 1
almond
lets say Veg has no sub categories.
Right now the union will pull all data
fruit, veg, nuts, apple, orange, grape, walnut, pecan, almond, so its
doubling counting fruit and its subclass items. I want it only to
bring up the lowest subclass items, and if there are no subclass items
it'll bring up that class item like Veg (since it has no subclass
item). Is this possible or is there an easier way. Here is my union
code.
SELECT [itemclass].item
FROM [itemclass]
UNION select [itemsubclass].item
from [itemsubclass ];
Thanks in advance,
Ryan

 

[Douglas J. Steele]

So assuming that Group is where fruit is stored for the apple, orange and
grape entries, your query would be

SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I know what your saying, I have the field names identical in both
tables.

field1 is ID, field2 is Item and field3 is Group,

Group is what links table 2 to table 1.

You're right, it would, because I didn't know the actual names of the
fields
in your table.

I'm assuming that there's a field in itemsubclass that contains the item
name to point back to the itemclass table. (In other words, the rows with
apple, orange and grape must all have a field saying fruit). Put that
field
name rather than item in (SELECT DISTINCT Item FROM itemsubclass)

If you don't have such a field in itemsubclass, how do you know the
itemclass to which each item in itemsubclass belongs?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Thanks for the quick reply, but it pulled the same information all 93
records.

On Mar 17, 10:47 am, "Douglas J. Steele"
SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Item FROM itemsubclass)
UNION
SELECT [itemsubclass].item
from [itemsubclass ];

I have a union query that pairs up two tables. The first table has a
selection of items where upon selection it'll bring up items in the
2nd table. Some selections in the first table do not bring up items
in
the 2nd table simply because they're no items to bring up. This
works
fine. What I'd like to do it on my union query is to pull either or
data. The tables are setup in the same format, each has 3 fields, 1
is
autonum, 2 is the item, 3 is how the 2nd table links to the 1st
table.
Currently the union pulls both field 2 on both tables and puts it
into
one column, which is nice, but then it double counts the item;
meaning
lets say I have these selections in table one

table 1:
Fruit
Veg
Nuts

table 2:
apple
orange - for fruits in table 1
grape

walnut
pecan - for nuts in table 1
almond

lets say Veg has no sub categories.

Right now the union will pull all data

fruit, veg, nuts, apple, orange, grape, walnut, pecan, almond, so
its
doubling counting fruit and its subclass items. I want it only to
bring up the lowest subclass items, and if there are no subclass
items
it'll bring up that class item like Veg (since it has no subclass
item). Is this possible or is there an easier way. Here is my union
code.

SELECT [itemclass].item
FROM [itemclass]
UNION select [itemsubclass].item
from [itemsubclass ];

Thanks in advance,
Ryan

 

[ryan.fitzpatrick3]

It gives me data mismatch error with code below when I put in group
instead of item in the where statement.

So assuming that Group is where fruit is stored for the apple, orange and
grape entries, your query would be

SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I know what your saying, I have the field names identical in both
tables.

field1 is ID, field2 is Item and field3 is Group,

Group is what links table 2 to table 1.

You're right, it would, because I didn't know the actual names of the
fields
in your table.
I'm assuming that there's a field in itemsubclass that contains the item
name to point back to the itemclass table. (In other words, the rows with
apple, orange and grape must all have a field saying fruit). Put that
field
name rather than item in (SELECT DISTINCT Item FROM itemsubclass)
If you don't have such a field in itemsubclass, how do you know the
itemclass to which each item in itemsubclass belongs?
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Thanks for the quick reply, but it pulled the same information all 93
records.
On Mar 17, 10:47 am, "Douglas J. Steele"
SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Item FROM itemsubclass)
UNION
SELECT [itemsubclass].item
from [itemsubclass ];
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have a union query that pairs up two tables. The first table has a
selection of items where upon selection it'll bring up items in the
2nd table. Some selections in the first table do not bring up items
in
the 2nd table simply because they're no items to bring up. This
works
fine. What I'd like to do it on my union query is to pull either or
data. The tables are setup in the same format, each has 3 fields, 1
is
autonum, 2 is the item, 3 is how the 2nd table links to the 1st
table.
Currently the union pulls both field 2 on both tables and puts it
into
one column, which is nice, but then it double counts the item;
meaning
lets say I have these selections in table one
table 1:
Fruit
Veg
Nuts
table 2:
apple
orange - for fruits in table 1
grape
walnut
pecan - for nuts in table 1
almond
lets say Veg has no sub categories.
Right now the union will pull all data
fruit, veg, nuts, apple, orange, grape, walnut, pecan, almond, so
its
doubling counting fruit and its subclass items. I want it only to
bring up the lowest subclass items, and if there are no subclass
items
it'll bring up that class item like Veg (since it has no subclass
item). Is this possible or is there an easier way. Here is my union
code.
SELECT [itemclass].item
FROM [itemclass]
UNION select [itemsubclass].item
from [itemsubclass ];
Thanks in advance,
Ryan

 

[Douglas J. Steele]

Let me guess. Group is a lookup field, right? See
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for some of
the many reasons why most of us abhor that misfeature!

Assuming that I'm correct, and Group is a lookup field, what's actually be
stored there is the Id from itemclass, not the item. That means try

SELECT [itemclass].item
FROM [itemclass]
WHERE id NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

It gives me data mismatch error with code below when I put in group
instead of item in the where statement.

So assuming that Group is where fruit is stored for the apple, orange and
grape entries, your query would be

SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I know what your saying, I have the field names identical in both
tables.

field1 is ID, field2 is Item and field3 is Group,

Group is what links table 2 to table 1.

On Mar 17, 11:54 am, "Douglas J. Steele"
You're right, it would, because I didn't know the actual names of the
fields
in your table.

I'm assuming that there's a field in itemsubclass that contains the
item
name to point back to the itemclass table. (In other words, the rows
with
apple, orange and grape must all have a field saying fruit). Put that
field
name rather than item in (SELECT DISTINCT Item FROM itemsubclass)

If you don't have such a field in itemsubclass, how do you know the
itemclass to which each item in itemsubclass belongs?

Thanks for the quick reply, but it pulled the same information all
93
records.

On Mar 17, 10:47 am, "Douglas J. Steele"
SELECT [itemclass].item
FROM [itemclass]
WHERE item NOT IN (SELECT DISTINCT Item FROM itemsubclass)
UNION
SELECT [itemsubclass].item
from [itemsubclass ];

I have a union query that pairs up two tables. The first table has
a
selection of items where upon selection it'll bring up items in
the
2nd table. Some selections in the first table do not bring up
items
in
the 2nd table simply because they're no items to bring up. This
works
fine. What I'd like to do it on my union query is to pull either
or
data. The tables are setup in the same format, each has 3 fields,
1
is
autonum, 2 is the item, 3 is how the 2nd table links to the 1st
table.
Currently the union pulls both field 2 on both tables and puts it
into
one column, which is nice, but then it double counts the item;
meaning
lets say I have these selections in table one

table 1:
Fruit
Veg
Nuts

table 2:
apple
orange - for fruits in table 1
grape

walnut
pecan - for nuts in table 1
almond

lets say Veg has no sub categories.

Right now the union will pull all data

fruit, veg, nuts, apple, orange, grape, walnut, pecan, almond, so
its
doubling counting fruit and its subclass items. I want it only to
bring up the lowest subclass items, and if there are no subclass
items
it'll bring up that class item like Veg (since it has no subclass
item). Is this possible or is there an easier way. Here is my
union
code.

SELECT [itemclass].item
FROM [itemclass]
UNION select [itemsubclass].item
from [itemsubclass ];

Thanks in advance,
Ryan