Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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
 
Old May 21st, 2004, 07:58 PM
Registered User
 
Join Date: May 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select with multiple queries problem

I currently have an access query that pulls data from four other queries. Each query counts the same column from the same table with different where criteria. I want to know if I can use just one single query to combine the functionality of all these queries.

I want to select a system_no and count the asset column of this table based on the where certain fields are whatever then select the system_no and count the asset column again but use different where criteria. I hope someone can help me out. I thank all that can help.

One query is:
SELECT ACP_Assets.SYSTEM_NO, Count(ACP_Assets.TPLNR) AS FLOC_Complete
FROM ACP_Assets INNER JOIN Assets ON ACP_Assets.TPLNR = Assets.Asset
WHERE ((((ACP_Assets.Status)<>"INAK" And (ACP_Assets.Status)<>"DLFL") AND ((Assets.ER_Type)>0 And (Assets.ER_Type) Is Not Null) AND ((Assets.Criteria_ModifiedBy)<>"79294" And (Assets.Criteria_ModifiedBy)<>"79297") AND ((Assets.Status)<>"NotInSAP")) OR (((ACP_Assets.Status) Is Null) AND ((Assets.ER_Type)>0 And (Assets.ER_Type) Is Not Null) AND ((Assets.Criteria_ModifiedBy)<>"79294" And (Assets.Criteria_ModifiedBy)<>"79297") AND ((Assets.Status)<>"NotInSAP")))
GROUP BY ACP_Assets.SYSTEM_NO

Another is:
SELECT ACP_Assets.SYSTEM_NO, Count(ACP_Assets.TPLNR) AS FLOC_SystemTotal
FROM ACP_Assets INNER JOIN Assets ON ACP_Assets.TPLNR = Assets.Asset
WHERE ((((ACP_Assets.Status)<>"INAK" And (ACP_Assets.Status)<>"DLFL") AND ((Assets.Status)<>"NotInSAP")) OR (((ACP_Assets.Status) Is Null) AND ((Assets.Status)<>"NotInSAP")))
GROUP BY ACP_Assets.SYSTEM_NO;

There are two more but the I have a query that takes the SYSTEM_NO from the first and the FLOC_Complete and the FLOC_SystemTotal from the second where the two queries are inner joined based on the SYSTEM_NO






Similar Threads
Thread Thread Starter Forum Replies Last Post
Help: Need to combine multiple IF queries scotts SQL Server 2005 1 April 14th, 2008 07:54 PM
multiple queries one form jeremy1048 Access 4 November 13th, 2007 12:45 PM
multiple select problem austinf HTML Code Clinic 0 June 19th, 2006 11:54 PM
Multiple queries at one time VBAHole22 SQL Server 2000 2 July 27th, 2003 06:59 PM
Send Multiple Queries to ONE Excel Spreadsheet dgarcia1128 Access VBA 2 June 23rd, 2003 08:31 AM





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