Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 20th, 2006, 09:58 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Nesting queries???

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!



Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting Repeater controls Stricken ASP.NET 2.0 Basics 0 February 2nd, 2007 08:31 AM
Class Nesting Weirdness?? HotBlue Visual Basic 2005 Basics 3 November 8th, 2006 03:22 PM
Simple Nesting Loop PommyTom XSLT 2 June 9th, 2006 02:16 AM
Nesting ADO recordsets yossarian Pro VB Databases 1 January 12th, 2005 06:28 PM
Subreports nesting svenvandevelde Crystal Reports 2 February 4th, 2004 12:20 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.