compare and contrast of data from queries

  • Thread starter Thread starter Michele Stephenson
  • Start date Start date
M

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
 
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
 
Back
Top