Hello,
First time poster, long time searcher...
I have these following tables:
Trips
TripsFeatures (linking table)
Features
ListFeatures (linking table)
List
Trips can include multiple features.
Features can belong to multiple lists.
So, some Features have been visited (during a Trip) and some have not.
I want to sum Features belonging to each List that have been visited.
So far the only way I can do it is to build one query called qryFeaturesVisited:
Code:
SELECT tblListFeatures.ListID, tblTripsFeatures.FeatureID
FROM tblListFeatures INNER JOIN tblTripsFeatures ON tblListFeatures.FeatureID = tblTripsFeatures.FeatureID
GROUP BY tblListFeatures.ListID, tblTripsFeatures.FeatureID;
This gives me a recordset that is a subset of Features, including only those that have been visited.
Then I do another query:
Code:
SELECT tblLists.ListName, Count(qryFeaturesVisited.FeatureID) AS CountOfFeatureID
FROM qryFeaturesVisited INNER JOIN tblLists ON qryFeaturesVisited.ListID = tblLists.ListID
GROUP BY tblLists.ListName;
This one gives me the result but is cumbersome b/c of using multiple queries. I of course have other considerations in the db so if possible, I'd like to do this in one slick query.
Can anyone help me combine the SQL into one query? I mostly write SQL by doing things in Access design view and then editing SQL. Needless to say, I'm a beginner but understand the importance of designing good queries upfront.
Thanks in advance!
Nesting queries???
