Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Merging 2 Access Queries Together

Posted on 2010-01-07
6
Medium Priority
?
202 Views
Last Modified: 2012-05-08
What would be the SQL if I want to merge these 2 queries that works separately now ?
Query 3 :
SELECT Sum(PEAKS.Done) AS PeakDone, Count(PEAKS.Done) AS PeakList, Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo
FROM PEAKS INNER JOIN (Lists INNER JOIN PeakAward ON Lists.ListNo = PeakAward.ListNo) ON PEAKS.PeakNo = PeakAward.PeakNo
GROUP BY Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo, PeakAward.ListNo
WHERE (((Sum(PEAKS.Done))=Yes) AND ((PeakAward.ListNo)=8))


Other query :
SELECT Query3.PeakDone, Query3.PeakList, Query3.ListNo, Query3.ListName, Query3.ListAcronym, Query3.ListCertificate, Query3.ListLogo, Lists.ListDescription, Lists.ListLink
FROM Query3 INNER JOIN Lists ON Query3.ListNo = Lists.ListNo;


Thanks
0
Comment
Question by:latza
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:cminear
ID: 26205501
If I understand your question, I believe the SQL given below is the answer.  However, your "other query" really is already doing that; you are just using the already created "query 3" instead of the raw SQL.

If you only have "query 3" as a means to get the results from "other query", that is, you never run "query 3" on its own, then yes, use the consolidated query given below and get rid of "query 3".  However, if "query 3" is useful in and of itself, then don't bother with the consolidated query; it will only cause trouble as you will need to maintain both "query 3" and "new other query".
# "new other query"
SELECT sq.PeakDone, sq.PeakList, sq.ListNo, sq.ListName, sq.ListAcronym, sq.ListCertificate, sq.ListLogo, Lists.ListDescription, Lists.ListLink
FROM (
  SELECT Sum(PEAKS.Done) AS PeakDone, Count(PEAKS.Done) AS PeakList, Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo
  FROM PEAKS INNER JOIN (Lists INNER JOIN PeakAward ON Lists.ListNo = PeakAward.ListNo) ON PEAKS.PeakNo = PeakAward.PeakNo
  GROUP BY Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo, PeakAward.ListNo
  WHERE (((Sum(PEAKS.Done))=Yes) AND ((PeakAward.ListNo)=8)) ) sq 
INNER JOIN Lists ON sq.ListNo = Lists.ListNo;

Open in new window

0
 

Author Comment

by:latza
ID: 26206157
It says "Syntaxt error (missing operator) in query expression 'WHERE (((Sum(PEAKS.Done))=Yes) AND ((PeakAward.ListNo)=8))'. Any ideas ? Thanks
0
 
LVL 12

Expert Comment

by:cminear
ID: 26210622
OK, I had blithely assumed that "Query 3" was a working query, so I didn't look at it carefully.  The first problem with "Query 3" is that the WHERE clause does not go after the GROUP BY, but would go before it (if needed).  Looking at what you have, I would keep the "PeakAward.ListNo = 8".  Take a look at pass 1 below.

That just leaves the "(Sum(PEAKS.Done))=YES" part of your original WHERE clause.  Here, we have a big problem.  Based on the column name ("Done"), I'm going to assume that this column has a Yes/No data type.  If that is the case, then performing a Sum on this column makes no sense, in the first place; and when I go ahead and try it myself, I get a negative integer as a result, so it would appear that it would never equal "YES", so you would never get results.  Were you trying to limit the records to ones which have "PEAKS.Done = YES"?  If so, that could go into the WHERE clause as well.  See pass 2.  I would also remove the "SUM(PEAKS.Done)" part from the SELECT column list.

If that is not what you want, then you will need to clarify what you are trying to get.

One final point: you include PeakAward.ListNo in the GROUP BY clause, but you don't include it in the list of columns to be displayed.  Thus it is possible that you could have "duplicate" records displayed, because 2 or more rows have the same ListName, ListAcronym, ListCertificate and ListLogo, but different ListNo; but since you don't display that, you don't see that they are different.  So I would suggest either adding "PeakAward.ListNo" to the SELECT column list, or drop it from the GROUP BY.
# query 3, pass 1
SELECT Sum(PEAKS.Done) AS PeakDone, Count(PEAKS.Done) AS PeakList, Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo
FROM PEAKS INNER JOIN (Lists INNER JOIN PeakAward ON Lists.ListNo = PeakAward.ListNo) ON PEAKS.PeakNo = PeakAward.PeakNo
WHERE PeakAward.ListNo = 8
GROUP BY Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo, PeakAward.ListNo

# query 3, pass 2
SELECT Count(PEAKS.Done) AS PeakList, Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo
FROM PEAKS INNER JOIN (Lists INNER JOIN PeakAward ON Lists.ListNo = PeakAward.ListNo) ON PEAKS.PeakNo = PeakAward.PeakNo
WHERE PeakAward.ListNo = 8 and PEAKS.Done = YES
GROUP BY Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo, PeakAward.ListNo

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:latza
ID: 26210741
Query 3 is a working query in Access. Anyway, I'm trying to retrieve the count of  "PEAKS.Done = YES" which are the total number of peaks climbed but also the total number of peaks in the PeakAward table (see query below which retrieves the correct total) where "PeakAward.ListNo = 8". Now the reason of all this is because Lists.ListDescription and Lists.ListLink are Memos which get truncated when used inside a groupby clause. Any suggestions ?
SELECT Count(PeakAward.PeakNo) AS CountOfPeakNo
FROM Lists INNER JOIN PeakAward ON Lists.ListNo = PeakAward.ListNo
WHERE (((PeakAward.ListNo)=8));

Open in new window

0
 
LVL 12

Accepted Solution

by:
cminear earned 2000 total points
ID: 26285117
OK the last query you give would appear to be the answer to "Find the number of Peaks which have a PeakAward with ListNo = 8."  To just get the count of peaks which are done, the first query below will give that.

Now, if you really want both pieces of information presented at the same time, (like
 CountOfPeakNo |  Count of PeaksDone
---------------------------------------------------
         <x>            |         <y>

) use a CROSS JOIN.  A cross join will pair every record from the first table to every record in the second table, so the resultant table will have <size A> * <size B> records.  Since each of these queries have only one record, the resultant table after the cross join is still just one record.

If you want something else, please clarify.

Going back to your original question with "Other query", I just noticed that "ListNo" is not included in the SELECT listing for Query 3; this means that there's nothing to compare with the Lists table as part of the INNER JOIN.  So ultimately that is why that query didn't work.

Based on what you were doing with Query 3 (originally), I took another stab at your "other query".  The inner query is getting the count of peaks which are done (i.e., have "PeakDone = Yes") for each ListNo.  Then I join the Lists table to the result of that query to get the information for each ListNo from the subquery.

In any case, ask questions about either one.
# count of peaks done
SELECT COUNT(*) as CountofPeaksDone FROM PEAKS WHERE PeakDone = Yes;

# both items together
SELECT CountOfPeakNo, CountofPeaksDone 
FROM (
  SELECT Count(PeakAward.PeakNo) AS CountOfPeakNo
  FROM Lists INNER JOIN PeakAward ON Lists.ListNo = PeakAward.ListNo
  WHERE (((PeakAward.ListNo)=8))
) a
CROSS JOIN (
  SELECT COUNT(*) as CountofPeaksDone FROM PEAKS WHERE PeakDone = Yes
) b;

# new Other Query
SELECT PeakDoneCount, Lists.ListName, Lists.ListAcronym, Lists.ListCertificate, Lists.ListLogo
FROM (
  SELECT PeakAward.ListNo, COUNT(*) As PeakDoneCount
  FROM PeakAward INNER JOIN PEAKS ON (PeakAward.PeakNo = PEAKS.PeakNo)
  WHERE PEAKS.PeakDone = Yes
  GROUP BY PeakAward.ListNo
) PeakCount INNER JOIN Lists ON (PeakCount.ListNo = Lists.ListNo)

Open in new window

0
 

Author Comment

by:latza
ID: 26285614
Ah, you put me on the right track, here is the query that did it for me. thanks a lot.
SELECT Total.CountOfPeakNo, Climbed.CountofPeaksDone, List.*
FROM (SELECT Count(PeakAward.PeakNo) AS CountOfPeakNo
FROM Lists INNER JOIN PeakAward ON Lists.ListNo=PeakAward.ListNo
WHERE (((PeakAward.ListNo)=8))) AS Total, (SELECT Count(*) AS CountofPeaksDone
FROM PEAKS INNER JOIN PeakAward ON PEAKS.PeakNo=PeakAward.PeakNo
WHERE (((PEAKS.Done)=Yes) And ((PeakAward.ListNo)=8))) AS Climbed, (SELECT Lists.*
FROM Lists WHERE (((Lists.ListNo)=8))) AS List

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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