compare and contrast of data from queries

[Michele Stephenson]

Hi. I have a table that has genus and species info. genus
and species are in separate fields with a primary key of
SPID(species id). this table is linked to another table
that has country and continent as separate fields and is
linked with SPID.

i can run a query by typing =spain or =france and get the
info for those countries and can also generate a report
for all countries and have a combo box user can choose
one country and it generate a report for that country.

problem.. they would like the user to choose 2 different
countries and then (1)list separately the genus/species
found in those two countries, then (2) list which are the
same in each country and (3)which are unique in each
country. i have tried some union queries and not gotten
very far. thanks for the help.

michele

 

[Michel Walsh]

Hi,


For the first problem, it seems trivial. Run two subreports, each is the
same sub-report, but in one case, base the recordsource to SELECT * FROM
yourTable WHERE country=country1, and in the second case, to SELECT * FROM
yourTable WHERE country=country2. You can change the recordsource at the
Open event of the report (or sub-report)

For the second problem, base the report on the query:

SELECT a.*
FROM yourTable As a INNER JOIN yourTable As b
ON a.genus = b.genus
WHERE a.country=country1 AND b.country=country2


For the third problem, that is indeed a union (Access 2000 or later)




SELECT a.*
FROM yourTable As a LEFT JOIN
( SELECT genus FROM yourTable WHERE country=country2) As b
ON a.genus = b.genus
WHERE b.genus Is NULL AND a.country=country1

UNION ALL

SELECT a.*
FROM yourTable As a LEFT JOIN
( SELECT genus FROM yourTable WHERE country=country1) As b
ON a.genus = b.genus
WHERE b.genus Is NULL AND a.country=country2




Hoping it may help,
Vanderghast, Access MVP