Sub query insertion problem Access

Hello,
I have 2 queries that work quite well independently, however I would like to add them together to form one query for view purposes.  Here is the main query:

SELECT TotalYearlySales.BranchID, TotalYearlySales.Sales2011, (select Sum(Sales2011) From TotalYearlySales as T) AS TotalSales2011, ([Sales2011]/[TotalSales2011]) AS Percentage, [Percentage]*3000 AS FilterGoal, Sum(Spiff_Per_User.[Number Sold]) AS SOLD, [SOLD]/[FilterGoal] AS Goal
FROM TotalYearlySales INNER JOIN Spiff_Per_User ON TotalYearlySales.BranchID=Spiff_Per_User.BranchID
WHERE (((TotalYearlySales.BranchID)="1" Or (TotalYearlySales.BranchID)="01" Or (TotalYearlySales.BranchID)="2" Or (TotalYearlySales.BranchID)="02" Or (TotalYearlySales.BranchID)="4" Or (TotalYearlySales.BranchID)="04" Or (TotalYearlySales.BranchID)="6" Or (TotalYearlySales.BranchID)="06" Or (TotalYearlySales.BranchID)="9" Or (TotalYearlySales.BranchID)="09" Or (TotalYearlySales.BranchID)="10" Or (TotalYearlySales.BranchID)="12" Or (TotalYearlySales.BranchID)="20" Or (TotalYearlySales.BranchID)="21" Or (TotalYearlySales.BranchID)="25" Or (TotalYearlySales.BranchID)="27" Or (TotalYearlySales.BranchID)="28" Or (TotalYearlySales.BranchID)="29" Or (TotalYearlySales.BranchID)="45") AND ((Spiff_Per_User.[Type of Spiff])="Filter"))
GROUP BY TotalYearlySales.BranchID, TotalYearlySales.Sales2011;

and the resulting table:

BranchID      Sales2011      TotalSales2011      Percentage      FilterGoal      SOLD      Goal
21               $62,607.00      $8,002,812.00                        0.78%      23.47      18      76.70%
25                $90,612.00      $8,002,812.00                        1.13%      33.97      7      20.61%

this is one I would like to add, specifically the text in bold:

SELECT TotalYearlySales.BranchID, TotalYearlySales.Sales2011, (select Sum(Sales2011) From TotalYearlySales as T) AS TotalSales2011, ([Sales2011]/[TotalSales2011]) AS Percentage, [Percentage]*3000 AS FilterGoal, [FilterGoal]*0.12 AS SGoal, Sum(Spiff_Per_User.[Number Subscriptions]) AS SUBS, [SUBS]/[SUBGoal] AS SUBSGoal
FROM TotalYearlySales INNER JOIN Spiff_Per_User ON TotalYearlySales.BranchID=Spiff_Per_User.BranchID
GROUP BY TotalYearlySales.BranchID, TotalYearlySales.Sales2011;

and the resulting table:

BranchID      Sales2011      TotalSales2011      Percentage      FilterGoal      SUBGoal      SUBS      Goal
21                 $62,607.00      $8,002,812.00                0.78%      23.47      2.82               0     0.00%
25                 $90,612.00      $8,002,812.00                1.13%      33.97      4.08                     0      0.00%

The problem is this  ((Spiff_Per_User.[Type of Spiff])="Filter")) criteria statement, which is in the first query and not in the second.  In other words I would like the 3 columns in the second query (SGoal, SUBS, SUBSGoal) to display with out the "Filter" citeria of the first query.
Is this possblie with a sub select statement?  I have tried the following query but receive a syntax error in the bold text and I am not sure how to remove the "filter" statement for just that sub select statement:


SELECT TotalYearlySales.BranchID, TotalYearlySales.Sales2011, (select Sum(Sales2011) From TotalYearlySales as T) AS TotalSales2011, ([Sales2011]/[TotalSales2011]) AS Percentage, [Percentage]*3000 AS FilterGoal, (select  [FilterGoal]*0.12 AS SGoal, Sum(Spiff_Per_User.[Number Subscriptions]) AS SUBS, [SUBS]/[SUBGoal] AS SG) AS SUBSGoal, Sum(Spiff_Per_User.[Number Sold]) AS SOLD, [SOLD]/[FilterGoal] AS Goal
FROM TotalYearlySales INNER JOIN Spiff_Per_User ON TotalYearlySales.BranchID=Spiff_Per_User.BranchID
WHERE (((TotalYearlySales.BranchID)="1" Or (TotalYearlySales.BranchID)="01" Or (TotalYearlySales.BranchID)="2" Or (TotalYearlySales.BranchID)="02" Or (TotalYearlySales.BranchID)="4" Or (TotalYearlySales.BranchID)="04" Or (TotalYearlySales.BranchID)="6" Or (TotalYearlySales.BranchID)="06" Or (TotalYearlySales.BranchID)="9" Or (TotalYearlySales.BranchID)="09" Or (TotalYearlySales.BranchID)="10" Or (TotalYearlySales.BranchID)="12" Or (TotalYearlySales.BranchID)="20" Or (TotalYearlySales.BranchID)="21" Or (TotalYearlySales.BranchID)="25" Or (TotalYearlySales.BranchID)="27" Or (TotalYearlySales.BranchID)="28" Or (TotalYearlySales.BranchID)="29" Or (TotalYearlySales.BranchID)="45") AND ((Spiff_Per_User.[Type of Spiff])="Filter"))
GROUP BY TotalYearlySales.BranchID, TotalYearlySales.Sales2011;

I would greatly appreciate some insight.  I have learned alot from you guys so far but am stuck on this one.
thank you!
Matt_KennedyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SheilsCommented:
I am not quiet sure what you are trying to acheive but the way I see it you have two chioces.

1) Create a union query, or
2) Join the two query.

I believe that what you are after in the latter:

Select Query2.SGoal,Query2. SUBS, Query2.SUBSGoalQuery1.FilterGoal from Query1 INNER JOIN Query2 ON
Query1.BranchID=Query2.BranchID

NB: Query1 and Query are whatever name you have given to the queries  
Matt_KennedyAuthor Commented:
SB9
Thank you for the response.  Can you tell me where I would insert your statement into the final sql query?
SheilsCommented:
The simplest way is to first create the two queries which I understand you already have. Then create a third query in design view.

When the dialog box open for you to select table, click the query tab.
Select the two queries from the list.
Join the related fields and select the fields that you would like to appear in the their query just like you would if you where creating a query from two tables.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Matt_KennedyAuthor Commented:
SB9,
thank you so much for continuing to help me here.  I really appreciate it.

The only problem with this is the different criteria of the two queries.
The main query below only looks for the word "Filter" (the bold text),

SELECT TotalYearlySales.BranchID, TotalYearlySales.Sales2011, (select Sum(Sales2011) From TotalYearlySales as T) AS TotalSales2011, ([Sales2011]/[TotalSales2011]) AS Percentage, [Percentage]*3000 AS FilterGoal, Sum(Spiff_Per_User.[Number Sold]) AS SOLD, [SOLD]/[FilterGoal] AS Goal
FROM TotalYearlySales INNER JOIN Spiff_Per_User ON TotalYearlySales.BranchID=Spiff_Per_User.BranchID
WHERE (((TotalYearlySales.BranchID)="1" Or (TotalYearlySales.BranchID)="01" Or (TotalYearlySales.BranchID)="2" Or (TotalYearlySales.BranchID)="02" Or (TotalYearlySales.BranchID)="4" Or (TotalYearlySales.BranchID)="04" Or (TotalYearlySales.BranchID)="6" Or (TotalYearlySales.BranchID)="06" Or (TotalYearlySales.BranchID)="9" Or (TotalYearlySales.BranchID)="09" Or (TotalYearlySales.BranchID)="10" Or (TotalYearlySales.BranchID)="12" Or (TotalYearlySales.BranchID)="20" Or (TotalYearlySales.BranchID)="21" Or (TotalYearlySales.BranchID)="25" Or (TotalYearlySales.BranchID)="27" Or (TotalYearlySales.BranchID)="28" Or (TotalYearlySales.BranchID)="29" Or (TotalYearlySales.BranchID)="45") AND ((Spiff_Per_User.[Type of Spiff])="Filter"))
GROUP BY TotalYearlySales.BranchID, TotalYearlySales.Sales2011;

while the other query below calculates the results on all [Type 0f Spiff] words (of which there are 6) So the criteria statement ((Spiff_Per_User.[Type of Spiff])="Filter")) has been removed:

SELECT TotalYearlySales.BranchID, TotalYearlySales.Sales2011, (select Sum(Sales2011) From TotalYearlySales as T) AS TotalSales2011, ([Sales2011]/[TotalSales2011]) AS Percentage, [Percentage]*3000 AS FilterGoal, [FilterGoal]*0.12 AS SGoal, Sum(Spiff_Per_User.[Number Subscriptions]) AS SUBS, [SUBS]/[SUBGoal] AS SUBSGoal
FROM TotalYearlySales INNER JOIN Spiff_Per_User ON TotalYearlySales.BranchID=Spiff_Per_User.BranchID
WHERE (((TotalYearlySales.BranchID)="1" Or (TotalYearlySales.BranchID)="01" Or (TotalYearlySales.BranchID)="2" Or (TotalYearlySales.BranchID)="02" Or (TotalYearlySales.BranchID)="4" Or (TotalYearlySales.BranchID)="04" Or (TotalYearlySales.BranchID)="6" Or (TotalYearlySales.BranchID)="06" Or (TotalYearlySales.BranchID)="9" Or (TotalYearlySales.BranchID)="09" Or (TotalYearlySales.BranchID)="10" Or (TotalYearlySales.BranchID)="12" Or (TotalYearlySales.BranchID)="20" Or (TotalYearlySales.BranchID)="21" Or (TotalYearlySales.BranchID)="25" Or (TotalYearlySales.BranchID)="27" Or (TotalYearlySales.BranchID)="28" Or (TotalYearlySales.BranchID)="29" Or (TotalYearlySales.BranchID)="45")
GROUP BY TotalYearlySales.BranchID, TotalYearlySales.Sales2011;

How do I over come this problem so that the end resulting table looks like this with the last 2 columns added in and calculate with the Filter criteria?

BranchID  Sales2011  TotalSales2011   Percentage   FilterGoal   SOLD    Goal  SGoal  SUBS  SUBSGoal
21               $62,607.00      $8,002,812.00        0.78%      23.47      18      76.70%  2.28    0       0.00%
25                $90,612.00      $8,002,812.00       1.13%      33.97      7      20.61%    4.08    0       0.00%

Thank you again for all you help!
SheilsCommented:
The creteria statement is irrelevant. When you join two queries all that matters are the fields just as when you are joining two table in a query.

So What you need is a field in each of the to queries to match. I believe that the BranchID is the one.

The fields are:

From qry1: BranchID,Sales2011,TotalSales2011,Percentage,FilterGoal,SOLD,Goal

From qry2: BranchID,Sales2011,TotalSales2011,Percentage,FilterGoal,SGoal,SUBS,SUBSGoal

That's all that you have to worry about when compiling the third query.

So your third query is simply:

Select qry1.Sales2011,qry1.TotalSales2011,qry1.Percentage,qry1.FilterGoal,qry1.SOLD,qry1.Goal,qry2.SGoal,qry2.SUBS,qry2.SUBSGoal
From qry1 INNER JOIN qry2 ON qry1.BranchID= qry2.BranchID


I am assumming that the two original queries work fine on their own. To test the sql above

1)create a new query in design view;
2)close the show table dialog box without selecting and table;
3)One the toolbar click SQL
4)Paste my sql in the sql box
5)Replace qry1 and qry2 by the name of your fist and second query respectively.

If it does not work send a sample of your database and i will have another look.


Matt_KennedyAuthor Commented:
sb9,

Thank you once again for the help.
Yes, that did work quite well!!
The only problem is that if there is no data in the [Number Sold] field for a particular branchID in one query  it will not display any data from the other query for that same branchID.  For example

The first query below looks for just "Filter" for the [Type of Spiff] field.  The data shows branchID 29 did not sell any "Filter" (so there is nothing in the [Number Sold] field for branchID 29) so the individual query only shows 18 of the 19 BranchIDs.

SELECT TotalYearlySales.BranchID, TotalYearlySales.Sales2011, (select Sum(Sales2011) From TotalYearlySales as T) AS TotalSales2011, ([Sales2011]/[TotalSales2011]) AS Percentage, [Percentage]*3000 AS FGoal, Sum(Spiff_Per_User.[Number Sold]) AS FilterSOLD, [FilterSOLD]/[FGoal] AS FilterGoal
FROM TotalYearlySales INNER JOIN Spiff_Per_User ON TotalYearlySales.BranchID = Spiff_Per_User.BranchID
WHERE (((TotalYearlySales.BranchID)="1" Or (TotalYearlySales.BranchID)="01" Or (TotalYearlySales.BranchID)="2" Or (TotalYearlySales.BranchID)="02" Or (TotalYearlySales.BranchID)="4" Or (TotalYearlySales.BranchID)="04" Or (TotalYearlySales.BranchID)="6" Or (TotalYearlySales.BranchID)="06" Or (TotalYearlySales.BranchID)="9" Or (TotalYearlySales.BranchID)="09" Or (TotalYearlySales.BranchID)="10" Or (TotalYearlySales.BranchID)="12" Or (TotalYearlySales.BranchID)="20" Or (TotalYearlySales.BranchID)="21" Or (TotalYearlySales.BranchID)="25" Or (TotalYearlySales.BranchID)="27" Or (TotalYearlySales.BranchID)="28" Or (TotalYearlySales.BranchID)="29" Or (TotalYearlySales.BranchID)="45") AND ((Spiff_Per_User.[Type of Spiff])="Filter"))
GROUP BY TotalYearlySales.BranchID, TotalYearlySales.Sales2011;

The second query does not care about the "Filter" criteria and therefore shows results for all 19 branchID:

SELECT TotalYearlySales.BranchID, TotalYearlySales.Sales2011, (select Sum(Sales2011) From TotalYearlySales as T) AS TotalSales2011, ([Sales2011]/[TotalSales2011]) AS Percentage, [Percentage]*3000 AS FilterGoal, [FilterGoal]*0.12 AS SGoal, Sum(Spiff_Per_User.[Number Subscriptions]) AS SUBSOLD, [SUBSOLD]/[SGoal] AS SUBSGoal
FROM TotalYearlySales INNER JOIN Spiff_Per_User ON TotalYearlySales.BranchID = Spiff_Per_User.BranchID
WHERE (((TotalYearlySales.BranchID)="1" Or (TotalYearlySales.BranchID)="01" Or (TotalYearlySales.BranchID)="2" Or (TotalYearlySales.BranchID)="02" Or (TotalYearlySales.BranchID)="4" Or (TotalYearlySales.BranchID)="04" Or (TotalYearlySales.BranchID)="6" Or (TotalYearlySales.BranchID)="06" Or (TotalYearlySales.BranchID)="9" Or (TotalYearlySales.BranchID)="09" Or (TotalYearlySales.BranchID)="10" Or (TotalYearlySales.BranchID)="12" Or (TotalYearlySales.BranchID)="20" Or (TotalYearlySales.BranchID)="21" Or (TotalYearlySales.BranchID)="25" Or (TotalYearlySales.BranchID)="27" Or (TotalYearlySales.BranchID)="28" Or (TotalYearlySales.BranchID)="29" Or (TotalYearlySales.BranchID)="45"))
GROUP BY TotalYearlySales.BranchID, TotalYearlySales.Sales2011;
 
How do I overcome this discrepancy?
thank you again!
SheilsCommented:
Instead of inner join use left join

Select qry1.Sales2011,qry1.TotalSales2011,qry1.Percentage,qry1.FilterGoal,qry1.SOLD,qry1.Goal,qry2.SGoal,qry2.SUBS,qry2.SUBSGoal
From qry1 LEFT JOIN qry2 ON qry1.BranchID= qry2.BranchID

Make sure the qry1 is the one with all the data. The query will bring all the records from qry1 and the ones in qry2 that matches qry1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt_KennedyAuthor Commented:
That did the job!
thank you for teaching me this stuff!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.