[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with MS Access nested queries...

Posted on 2009-04-17
8
Medium Priority
?
1,134 Views
Last Modified: 2012-05-08
I designed 2 subqueries and 1 "main" query in MS Access. I'm trying to combine them into 1 query. I copied and pasted the 2 subqueries where I needed them in the main query, but when I execute I'm getting a message saying "you have written a subquery that can return more than one field without using the EXISTS reserve word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."
I believe the error is with my "IN" statements. Can someone look at my syntax and let me know what they think?
Thanks!
SELECT CA_CTRL_PUBLISHEDCONTROLFORECASTS.INTERVAL, CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR, Sum(CA_CTRL_PUBLISHEDCONTROLFORECASTS.FORECAST) AS SumOfFORECAST
FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS
WHERE (CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR, CA_CTRL_PUBLISHEDCONTROLFORECASTS.FCSTTYPE)
 
IN
 
(SELECT CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR, Min(CA_CTRL_PUBLISHEDCONTROLFORECASTS.FCSTTYPE) AS MinOfFCSTTYPE
FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS
GROUP BY CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR
HAVING (((CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY)>=#4/15/2009# And (CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY)<=#4/15/2009#)))
 
AND (((CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY)>=#4/15/2009# And (CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY)<=#4/15/2009#))
AND 
CA_CTRL_PUBLISHEDCONTROLFORECASTS.CONTROLZONEID=0
AND (CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR, CA_CTRL_PUBLISHEDCONTROLFORECASTS.INTERVAL, CA_CTRL_PUBLISHEDCONTROLFORECASTS.BOD)
 
IN
 
(SELECT CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR, CA_CTRL_PUBLISHEDCONTROLFORECASTS.INTERVAL, Max(CA_CTRL_PUBLISHEDCONTROLFORECASTS.BOD) AS MaxOfBOD
FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS
GROUP BY CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR, CA_CTRL_PUBLISHEDCONTROLFORECASTS.INTERVAL
HAVING (((CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY)>=#4/15/2009# And (CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY)<=#4/15/2009#)))
 
GROUP BY CA_CTRL_PUBLISHEDCONTROLFORECASTS.INTERVAL, CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR
ORDER BY CA_CTRL_PUBLISHEDCONTROLFORECASTS.INTERVAL, CA_CTRL_PUBLISHEDCONTROLFORECASTS.DTONLY, CA_CTRL_PUBLISHEDCONTROLFORECASTS.HR;

Open in new window

0
Comment
Question by:Bill543
  • 4
  • 3
8 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 900 total points
ID: 24172330
As it is written in error message the IN can compare just one column but you are trying to process three columns...
SELECT t0.INTERVAL, t0.DTONLY, t0.HR, Sum(t0.FORECAST) AS SumOfFORECAST
FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS t0
INNER JOIN (SELECT s1.DTONLY, s1.HR, Min(s1.FCSTTYPE) AS MinOfFCSTTYPE
  FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS s1
  GROUP BY s1.DTONLY, s1.HR
  HAVING (((s1.DTONLY)>=#4/15/2009# And (s1.DTONLY)<=#4/15/2009#))) t1 ON 
  t1.DTONLY = t0.DTONLY AND t1.HR = t0.HR AND t1.MinOfFCSTTYPE = t0.FCSTTYPE
INNER JOIN (SELECT s2.DTONLY, s2.HR, s2.INTERVAL, Max(s2.BOD) AS MaxOfBOD
  FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS s2
  GROUP BY s2.DTONLY, s2.HR, s2.INTERVAL
  HAVING (((s2.DTONLY)>=#4/15/2009# And (s2.DTONLY)<=#4/15/2009#))) t2 ON 
  t2.DTONLY = t0.DTONLY AND t2.HR = t0.HR AND t2.INTERVAL = t0.INTERVAL AND 
  t2.MaxOfBOD = t0.BOD
WHERE (((t0.DTONLY)>=#4/15/2009# And (t0.DTONLY)<=#4/15/2009#))
  AND t0.CONTROLZONEID=0
GROUP BY t0.INTERVAL, t0.DTONLY, t0.HR
ORDER BY t0.INTERVAL, t0.DTONLY, t0.HR;

Open in new window

0
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 900 total points
ID: 24172364
So, as you may see, instead of IN operator INNER JOINs are used. Problem with multiple rows would appear when using INNER JOINs and some records are more times in subquery. (E.g. I don't know why the first IN opereator does not contain INTERVAL column for comparison wich could create duplicate rows for SUM).

I am not sure if Access supports this kind of query. Local aliases were added to simplify the command.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 600 total points
ID: 24176664
Another approach - concatenate the fields in the sub queries to create a single field.  Note how I used aliases to distinguish between the main query and the sub queries.
SELECT a.INTERVAL, a.DTONLY, a.HR, Sum(a.FORECAST) AS SumOfFORECAST
FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS a
WHERE (a.DTONLY & a.HR & a.FCSTTYPE)
 
IN
 
(SELECT b.DTONLY & b.HR & Min(b.FCSTTYPE) AS MinOfFCSTTYPE
FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS b
GROUP BY b.DTONLY & b.HR
HAVING (((b.DTONLY)>=#4/15/2009# And (b.DTONLY)<=#4/15/2009#)))
 
AND (((b.DTONLY)>=#4/15/2009# And (b.DTONLY)<=#4/15/2009#))
AND 
b.CONTROLZONEID=0
AND (b.DTONLY & b.HR & b.INTERVAL, b.BOD)
 
IN
 
(SELECT c.DTONLY & c.HR & c.INTERVAL & Max(c.BOD) AS MaxOfBOD
FROM CA_CTRL_PUBLISHEDCONTROLFORECASTS c
GROUP BY c.DTONLY & c.HR & c.INTERVAL
HAVING (((c.DTONLY)>=#4/15/2009# And (c.DTONLY)<=#4/15/2009#)))
 
GROUP BY c.INTERVAL & c.DTONLY & c.HR
ORDER BY c.INTERVAL, c.DTONLY, c.HR;

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 900 total points
ID: 24176736
Are you sure this will work? Is it possible to concatenate result of aggregate function?

SELECT b.DTONLY & b.HR & Min(b.FCSTTYPE) AS MinOfFCSTTYPE

And DTONLY is a date. Is it possible to conctenate it with texts?

I am not so good in Access.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 600 total points
ID: 24176794
With this table:

ID      Item      Cost      Item1
1      Hammer      $25.00      1
2      Wrench      $12.00      1
3      Saw      $30.00      1
4      Screw      $2.00      1
5      Wood      $75.00      1
6      Nails      $4.00      1
7      Screw      $8.00      1
8      Drill      $40.00      1
9      Drill      $15.00      1
10      Blade      $35.00      1

Using this query:

SELECT Item & Sum(Cost) FROM costs Group By Item;

Produced this result in A2003:

Expr1000
Blade35
Drill55
Hammer25
Nails4
Saw30
Screw10
Wood75
Wrench12
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24176801
I don't know if Jet will see the concatenated fields in the sub queries as one field or three.  You have the data - over to you.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24178314
Wow, I did not suppose Access is so good. It seems I should start to learn it. Thanks.
0
 

Author Comment

by:Bill543
ID: 24220433
Thanks for all the inputs, guys. I tried one of the solutions and it threw an error. I originally was hoping to convert the query to MS Excel query builder format since I need to plot the "forecast" values on a chart y axis with dates/times on the x axis. And MS Access sucks at chars.

The query (well, actually there's 2 but the only difference is they use 2 different tables) I was tasked to convert is below. I guess I should have included this in my initial question, but its Oracle based.
-- MOST CURRENT FORECASTS WITH PUMP 
 SELECT 
    interval, 
    dtonly, hr, 
    SUM(forecast) forecast
 FROM ca_ctrl.PUBLISHEDCONTROLFORECASTS   
 WHERE dtonly||hr||fcsttype in 
    (SELECT dtonly||hr||MIN(fcsttype) 
         FROM ca_ctrl.PUBLISHEDCONTROLFORECASTS
         WHERE dtonly >= TO_DATE('&dt_from', 'MM/DD/YYYY') 
         AND dtonly <= TO_DATE('&dt_to', 'MM/DD/YYYY') 
         GROUP BY dtonly, hr) 
     AND dtonly >= TO_DATE('&dt_from', 'MM/DD/YYYY') 
     AND dtonly <= TO_DATE('&dt_to', 'MM/DD/YYYY') 
     AND controlzoneid = 0 
     AND dtonly||hr||interval||TO_CHAR(bod, 'MM/DD/YY HH24:MI:SS') in 
    (SELECT dtonly||hr||interval||MAX(TO_CHAR(bod, 'MM/DD/YY HH24:MI:SS')) 
         FROM ca_ctrl.PUBLISHEDCONTROLFORECASTS 
         WHERE dtonly >= TO_DATE('&dt_from', 'MM/DD/YYYY') 
         AND dtonly <= TO_DATE('&dt_to', 'MM/DD/YYYY') 
         AND controlzoneid = 0 
         GROUP BY dtonly, hr, interval) 
    AND interval IN ( &intrval )
 GROUP BY dtonly, hr, interval
 ORDER BY interval, dtonly, hr
 
 
-- MOST CURRENT FORECASTS WITHOUT PUMP
 SELECT 
    interval, dtonly, hr, 
    SUM(forecast) forecast
 FROM ca_ctrl.PUBLISHEDFORECASTSWITHOUTPUMP   
 WHERE 
    --dtonly||hr||fcsttype in 
    --(SELECT dtonly||hr||MIN(fcsttype) 
    --     FROM ca_ctrl.PUBLISHEDFORECASTSWITHOUTPUMP 
    --     WHERE dtonly >= TO_DATE('&dt_from', 'MM/DD/YYYY') 
     --    AND dtonly <= TO_DATE('&dt_to', 'MM/DD/YYYY') 
     --    GROUP BY dtonly, hr) 
     --AND 
     dtonly >= TO_DATE('&dt_from', 'MM/DD/YYYY') 
     AND dtonly <= TO_DATE('&dt_to', 'MM/DD/YYYY') 
     AND controlzoneid = 0 
     AND dtonly||hr||interval||TO_CHAR(bod, 'MM/DD/YY HH24:MI:SS') in 
    (SELECT dtonly||hr||interval||MAX(TO_CHAR(bod, 'MM/DD/YY HH24:MI:SS')) 
         FROM ca_ctrl.PUBLISHEDFORECASTSWITHOUTPUMP 
         WHERE dtonly >= TO_DATE('&dt_from', 'MM/DD/YYYY') 
         AND dtonly <= TO_DATE('&dt_to', 'MM/DD/YYYY') 
         AND controlzoneid = 0 
         GROUP BY dtonly, hr, interval) 
    AND   interval IN ( &intrval )
 GROUP BY dtonly, hr, interval
 ORDER BY interval, dtonly, hr

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question