query writing dilemma

  • Thread starter Thread starter Renee
  • Start date Start date
R

Renee

I have a table with a list of resource numbers (aka areas) (one relationship)
and a linking table that has a list of all the tree species (many
relationship) that links to each respective resource number. Someone asked
me if I could do a query that would produce a list of all the resource
numbers (areas) that had a douglas fir in it, but still list all the other
species in that resource number (area) as well. I've racked my brain, and
can't figure out how to do it. Help, please.

I'm on Windows XP Professional, Using Access 2007.

Thank you.
 
You need a subquery in the criteria using an IN clause.

Join the two tables in a query. In the resource numbers field of the Areas
table put the criteria of something like so with the proper table and field
names:

In (SELECT [resource numbers]
FROM tabletree
WHERE [tree species] = "douglas fir" )

In the future please post the proper field and table names and we can even
be of more help.
 
You have a table with the two fields:

AreaID TreeID


which link a tree specie to an area:


one Douglas Fir
one Black Spruce
.... ...


will tell that region one has Douglas Fir and Black Spruce. Such a table is
called a junction table. I assume there is no duplicated couple {areaID,
treeID}.


------------------------------
SELECT a.areaID, a.treeID
FROM junctionTable AS a INNER JOIN (SELECT AreaID
FROM jonctionTable AS b
WHERE TreeID="Douglas Fir") AS b
ON a.areaID = b.areaID
------------------------------

should then return all records from all areas having Douglas Fir among one
of their treeID values. Note that while I used the table junctionTable
twice, it is just one single table.




Vanderghast, Access MVP
 
Use your table and field names.
tblTrainee -- resource table
tblTrainReview -- tree species table
ReviewID -- tree species field
TrainID -- aka areas field

SELECT tblTrainReview.ReviewID, tblTrainReview.TrainID
FROM tblTrainReview
WHERE (((tblTrainReview.ReviewID)=[Enter species]));


SELECT tblTrainee.LName, tblTrainee.FName, tblTrainReview.*
FROM Renee_1 INNER JOIN (tblTrainee INNER JOIN tblTrainReview ON
tblTrainee.lTrainID = tblTrainReview.TrainID) ON Renee_1.TrainID =
tblTrainee.lTrainID;
 
Back
Top