Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combine three query's into one-keeping totals separate

Status
Not open for further replies.
netrusher

netrusher

Technical User
Joined
Feb 13, 2005
Messages
952
Location
US
The code below shows me a top 3 faults based on certain BuildID. The BuildID's in this query represent the 1-3Ton feature. I also have this same query with different BuildID's that represent our 4-7Ton feature. There is another query with different BuildID's that represent our 6-9Ton feature. What I am trying to accomplish is one Query that combines all three of these features but show the top 3 faults for each feature separately. I am definitely stumped and urgently seeking assistance.

1-3Ton Feature BuildID's
"E010","C809","F001","C810","F187","A910","M173","M174"

4-7Ton Feature BuildID's
"G004","E818","N005","F813","D024","C879"

6-9Ton Feature BuildID's
"H006","C878"
Click to expand...

Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Count(*) DESC
 
Sort by date Sort by votes
LesPaul

Below is the code for the three separate queries. How do I go about starting to join them with the Union query feature? I do not understand how to proceed.

Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) 
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Count(*) DESC;
------------
------------

SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate) 
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Count(*) DESC;
------------
------------

SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate) 
Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory
ORDER BY Count(*) DESC;
 
Upvote 0 Downvote
Here's one way:
Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "1-3" As Type, As Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
UNION
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem,
WorkUnitsFaultsMainTBL.FaultCategory, "4-7", Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
UNION
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "6-9", Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate)
Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
ORDER BY 4, Count(*) DESC;

Another is just as I showed you above. I'm assuming each of these queries has a name, qry1-3, qry4-7 and qry6-9. Create a new query: qryUnion:

Code: 
SELECT * FROM qry1-3
UNION
SELECT * FROM qry4-7
UNION
SELECT * FROM qry6-9
 
Upvote 0 Downvote
Thanks for your time Lespaul:

I get the following error when running the query:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
Click to expand...
 
Upvote 0 Downvote
Lespaul,

I get the following error from the code below:

SYNTAX error in FROM clause
Click to expand...


Code: 
SELECT * FROM TOP-003-ALL-Problem-1-3T-TOTALS
UNION
SELECT * FROM TOP-003-ALL-Problem-4-7T-TOTALS
UNION
SELECT * FROM TOP-003-ALL-Problem-7-9PT-TOTALS
 
Upvote 0 Downvote
SELECT * FROM [!][[/!]TOP-003-ALL-Problem-1-3T-TOTALS[!]][/!]
UNION
SELECT * FROM [!][[/!]TOP-003-ALL-Problem-4-7T-TOTALS[!]][/!]
UNION
SELECT * FROM [!][[/!]TOP-003-ALL-Problem-7-9PT-TOTALS[!]][/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks Lespaul,

Now is there a way where I can have separate columns for each of the queries and the Top 3 under each one. The way it is now only shows all the counts but does not identify with which tonnage they are with.

1-3Ton 4-7Ton 6-9T
 
Upvote 0 Downvote
You need to add to your original queries an indicator. I did it in the big long query:
Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, [b]"1-3" As Type[/b], As Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, [b]4[/b]
UNION
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem,
WorkUnitsFaultsMainTBL.FaultCategory, [b]"4-7"[/b], Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, [b]4[/b]
UNION
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, [b]"6-9"[/b], Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate)
Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, [b]4[/b]
ORDER BY 4, Count(*) DESC;

you would just have to do that in each individual query.

HTH
 
Upvote 0 Downvote
Lespaul,

I do not understand what an indicator is or how I would add one to each query. Can you explain more or show me how?

Thanks for all of your help.
 
Upvote 0 Downvote
Lespaul,

In the code below I changed each query to what I thought would match what you said to try. The fourth piece of code below is what I used from your post. I am still getting the:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
Click to expand...

Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "1-3" AS Type, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
ORDER BY Count(*) DESC;
Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "4-7" AS Type, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813","D024","C879")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
ORDER BY Count(*) DESC;
Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "6-9" AS Type, Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
ORDER BY Count(*) DESC;
Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "1-3" As Type, As Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
UNION
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "4-7" As Type, As Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("G004","E818","N005","F813")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
UNION
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "6-9" As Type, As Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate)
Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("H006","C878")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
ORDER BY 4, Count(*) DESC;
 
Upvote 0 Downvote
If I had to guess, I would say the keyword is probably TYPE (my bad). You can either change the name or surround it in brackets:

Code: 
SELECT TOP 3 WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, "1-3" AS [Type], Count(*) AS Totals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, WorkUnitsFaultsMainTBL.Problem, WorkUnitsFaultsMainTBL.FaultCategory, 4
ORDER BY Count(*) DESC;
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Wolfdzn
  • Locked
  • Question Question
Join two seperate SQL into one
Replies
6
Views
799
dhookom
dhookom
TrekBiker
  • Locked
  • Question Question
Records in one table not in another
Replies
3
Views
591
Andrzejek
Andrzejek
TheresAlwaysAWay
  • Locked
  • Question Question
Union query provides different reslt than when summing the combined queries individually
Replies
12
Views
2K
TheresAlwaysAWay
TheresAlwaysAWay
TrekBiker
  • Locked
  • Question Question
Select addresses using two criteria, one 'Like&quot;
Replies
3
Views
614
TrekBiker
TrekBiker
breezett93
  • Locked
  • Question Question
Merging multiple records into just one
2
Replies
22
Views
1K
breezett93
breezett93

Part and Inventory Search

Sponsor

Back
Top