Like Record Grouping

[NeonSky via AccessMonster.com]

Hello All!

Ok here is the scenerio.

I have table "TblZooA" and table "ZooB", they share the same structure. I am
trying to put together a query that returns "Bobo and Zoey" from both tables,
and groups according to Animal Name. Ideally my query would return the
following results....

"QryZooReturn" (sample query return, "AnimalName" and "ShipDate" are column
headings)
AnimalName ShipDate
Bobo 2006
Bobo 2007
Zoey 2006
Zoey 2007


"TblZooA" (sample table)
AnimalName ShipDate
Bobo 2006
Zoey 2006
Betty 2006

"TblZooB" (sample table)
Animal Name ShipDate
Bobo 2007
Zoey 2007
Francis 2007


Thanks SO MUCH!!

 

[Marshall Barton]

I have table "TblZooA" and table "ZooB", they share the same structure. I am
trying to put together a query that returns "Bobo and Zoey" from both tables,
and groups according to Animal Name. Ideally my query would return the
following results....

"QryZooReturn" (sample query return, "AnimalName" and "ShipDate" are column
headings)
AnimalName ShipDate
Bobo 2006
Bobo 2007
Zoey 2006
Zoey 2007


"TblZooA" (sample table)
AnimalName ShipDate
Bobo 2006
Zoey 2006
Betty 2006

"TblZooB" (sample table)
Animal Name ShipDate
Bobo 2007
Zoey 2007
Francis 2007

SELECT AnimalName, ShipDate
FROM TblZooA
WHERE AnimalName = "Bobo"
OR AnimalName = "Zoey"
UNION ALL
SELECT AnimalName, ShipDate
FROM TblZooB
WHERE AnimalName = "Bobo"
OR AnimalName = "Zoey"

 

[NeonSky via AccessMonster.com]

Thanks Marshall,

Though I do not think this will work with my record set. If I understand
correctly what you are suggesting is to return Zoey and Bobo as explicitly
specified. Which works in this case, though what I am attempting to do is a
little diferent.

I would like query to run something like this "IF Animal Name in Table A like
Animal Name in Table B and if ship date in table A is explicitly 2006, and
ship date in table b is explicitly 2007, then return those records and
grouped as previously stated within my initial posting." (So in this case I
do not have to define which animals I am looking for, I would like the query
to identify them, as related by the ship date of "2006" on one table and ship
date of "2007" on the other)

Thank you again!

I have table "TblZooA" and table "ZooB", they share the same structure. I am
trying to put together a query that returns "Bobo and Zoey" from both tables,

[quoted text clipped - 20 lines]

Zoey 2007
Francis 2007

SELECT AnimalName, ShipDate
FROM TblZooA
WHERE AnimalName = "Bobo"
OR AnimalName = "Zoey"
UNION ALL
SELECT AnimalName, ShipDate
FROM TblZooB
WHERE AnimalName = "Bobo"
OR AnimalName = "Zoey"

 

[Marshall Barton]

Oh, the troubles with working from an example instead of
good specs ;-)

I don't think there's any reason to return two records for
each animal name. There's probably no reason to return both
shipdates either. With those assumptions, try something
more like:

SELECT A.AnimalName, A.ShipDate
FROM TblZooA As A INNER JOIN TblZooB As B
ON A.AnimalName = B.AnimalName
WHERE A.ShipDate = 2006
AND B.ShipDate = 2007

If you really have to have two records for each name, then
use the UNION idea I posted earlier.
--
Marsh
MVP [MS Access]

Though I do not think this will work with my record set. If I understand
correctly what you are suggesting is to return Zoey and Bobo as explicitly
specified. Which works in this case, though what I am attempting to do is a
little diferent.

I would like query to run something like this "IF Animal Name in Table A like
Animal Name in Table B and if ship date in table A is explicitly 2006, and
ship date in table b is explicitly 2007, then return those records and
grouped as previously stated within my initial posting." (So in this case I
do not have to define which animals I am looking for, I would like the query
to identify them, as related by the ship date of "2006" on one table and ship
date of "2007" on the other)

I have table "TblZooA" and table "ZooB", they share the same structure. I am
trying to put together a query that returns "Bobo and Zoey" from both tables,

[quoted text clipped - 20 lines]

Zoey 2007
Francis 2007

SELECT AnimalName, ShipDate
FROM TblZooA
WHERE AnimalName = "Bobo"
OR AnimalName = "Zoey"
UNION ALL
SELECT AnimalName, ShipDate
FROM TblZooB
WHERE AnimalName = "Bobo"
OR AnimalName = "Zoey"

 

[NeonSky via AccessMonster.com]

Got it working! Thank you for the input Marshall I truly do appreciate it!

Oh, the troubles with working from an example instead of
good specs ;-)

I don't think there's any reason to return two records for
each animal name. There's probably no reason to return both
shipdates either. With those assumptions, try something
more like:

SELECT A.AnimalName, A.ShipDate
FROM TblZooA As A INNER JOIN TblZooB As B
ON A.AnimalName = B.AnimalName
WHERE A.ShipDate = 2006
AND B.ShipDate = 2007

If you really have to have two records for each name, then
use the UNION idea I posted earlier.

Though I do not think this will work with my record set. If I understand
correctly what you are suggesting is to return Zoey and Bobo as explicitly

[quoted text clipped - 24 lines]