Query Too Complex 1

[Dontremb]

Ok, it seems that this message is my bane. Antithesis, if you will.

I have a lot of calculated numbers from many different queries, which I then have combined all in one query (this query is nothing but a holding query: it holds no calculations, it simply has the ONE field from each of my other queries)

It works fine with my 32 queries I currently have in the main query. But when I try to put any more in there, it gives the error, "Query too complex."

This sounds VERY bad. Well, not horrible, I may have to just split the reports up into two or more subreports, and put them all together that way.

This, however, is what I wanted to avoid, because my report looks MUCH more professional with all the data coming from one query instead of using 50-100 subreports. Also, the subreports are such a pain to deal with.

Anyway, is there any other way around the "Query is too complex." problem?

Thanks for any help.

 

[PHV]

Without seeing any SQL code it's hard to say anything ...
I wonder the complexity of more than 32 queries for a report.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dontremb]

Right now I have 32 queries that look like this:

SELECT Count(*) AS IndianHired
FROM (SELECT DISTINCT tblCompilation.IDCompilation FROM qryIndianHired)  AS qryIndianHired;

Then, one query that simply has That field from each of the 32 queries.

That's all.

If I put more than 32 queries in the main query, it gives me the error.

 

[PHV]

Could you post the SQL code of the "main query" for, say, 3 queries ?

 

[PHV]

BTW, also the SQL code of the 3 queries.

 

[Dontremb]

Main query with 3 of the 'subqueries' in it:

SELECT qryAsianApplicantTotal.AsianApplicant, qryAsianHiredTotal.AsianHired, qryBlackApplicantTotal.BlackApplicant;

Query 1:

SELECT Count(*) AS AsianApplicant
FROM (SELECT DISTINCT tblCompilation.IDCompilation FROM qryAsianApplicant)  AS qryAsianApplicant;

Query 2:

SELECT Count(*) AS AsianHired
FROM (SELECT DISTINCT tblCompilation.IDCompilation FROM qryAsianHired)  AS qryAsianHired;

Query 3:

SELECT Count(*) AS BlackApplicant
FROM (SELECT DISTINCT tblCompilation.IDCompilation FROM qryBlackApplicant)  AS qryBlackApplicant;

And here's the SQL from the query that the first 'subquery' counts:

SELECT tblPersonnel.IDPersonnel, tblCompilation.IDCompilation, tblVacancies.IDVacancy, tblVacancies.VacancyNumber, tblRacialCategory.RacialCategory, Count(tblPersonnel.RacialCategory) AS CountOfRacialCategory, tblCompilation.Hired, tblPersonnel.Hispanic
FROM tblVacancies INNER JOIN (tblRacialCategory INNER JOIN (tblPersonnel INNER JOIN tblCompilation ON tblPersonnel.IDPersonnel = tblCompilation.IDPersonnel) ON tblRacialCategory.IDRacialCat = tblPersonnel.RacialCategory.Value) ON tblVacancies.IDVacancy = tblCompilation.IDVacancy
GROUP BY tblPersonnel.IDPersonnel, tblCompilation.IDCompilation, tblVacancies.IDVacancy, tblVacancies.VacancyNumber, tblRacialCategory.RacialCategory, tblCompilation.Hired, tblPersonnel.Hispanic
HAVING (((tblVacancies.VacancyNumber) Like "09*") AND ((tblRacialCategory.RacialCategory)="Asian") AND ((Count(tblPersonnel.RacialCategory))=1) AND ((tblPersonnel.Hispanic)<>Yes));

I guess it's not really 32 queries. It's more like 64, so far, but I need to run about double that. Or triple. (I have to include the breakdown of Male/Female for EACH category.)

 

[Dontremb]

Someone mentioned something about running functions on the report earlier... Any ideas how that would work? I can make all the queries independently, then run a function on my Master report for all the counts I need, or something, I just don't know how, or the syntax for that.

Also, I made another report with some of the queries on it, and used it as a subreport, and it seemed to work ok. Not too complex.

But it's still VERY annoying to use subreports. I just wish there was a way to get all the queries onto one query or table.

 

[lespaul]

Will be really helpful for you to learn more about counting and grouping. Looks like there's a RacialCategory field, you do know that you can group by that field and count the different Categories in a single query, right?

Something like:

SELECT tblRacialCategory.RacialCategory As Race, COUNT(*) As NumberOf
FROM tblPersonnel
INNER JOIN tblRacialCategory.IDRacialCat = tblPersonnel.RacialCategory
GROUP BY tblRacialCategory.RacialCategory

will give you:
[tt]
Race NumberOf
Asian 12345
Black 235
Hispanic 56878
[/tt]

Leslie

Have you met Hardy Heron?

 

[Dontremb]

lespaul, I did not know you could do that, actually... I'm going to try to mess around with this right now.

This may be one of the MANY problems that arises from me being self-taught in Access. There is a bunch of stuff that I'm just not aware of.

Hrm... I run that, and just get this error:

"Syntax error in FROM clause."

Any ideas?

 

[lespaul]

I wrote the join wrong! try this:

SELECT tblRacialCategory.RacialCategory As Race, COUNT(*) As NumberOf
FROM tblPersonnel
INNER JOIN tblRacialCategory ON tblRacialCategory.IDRacialCat = tblPersonnel.RacialCategory
GROUP BY tblRacialCategory.RacialCategory

Leslie

Have you met Hardy Heron?

 

[MajP]

I think you could radically knock down the amount of queries you have, but then I probably would do something like this to get my output

Public Sub createOutput()
  Dim strSql As String
  Dim val1 As Variant
  Dim val2 As Variant
  Dim val3 As Variant
  
  strSql = "Delete * from tblOutput"
  CurrentDb.Execute strSql
  
  val1 = strVal("Count of Employees")
  val2 = DCount("EmployeeID", "Employees")
  val3 = strVal("Employees")
  Call insertVals(val1, val2, val3)
  
  val1 = strVal("Count of Sales Representatives")
  val2 = DCount("EmployeeID", "Employees", "Title = 'Sales Representative'")
  Call insertVals(val1, val2, val3)
  
  val1 = strVal("Count of Managers")
  val2 = DCount("EmployeeID", "Employees", "Title = 'Sales Manager'")
  Call insertVals(val1, val2, val3)
  
  val1 = strVal("Count of Orders over ")
  val2 = DCount("EmployeeID", "Employees", "Title = 'Sales Manager'")
  Call insertVals(val1, val2, val3)
  
  val1 = strVal("Count of Unit Price = 1")
  val2 = DCount("OrderID", "qryOrderPrice", "CountOfUnitPrice = 1")
  val3 = strVal("qryOrderPrice")
  Call insertVals(val1, val2, val3)
  
  val1 = strVal("Count of Unit Price = 2")
  val2 = DCount("OrderID", "qryOrderPrice", "CountOfUnitPrice = 2")
  val3 = strVal("qryOrderPrice")
  Call insertVals(val1, val2, val3)

End Sub

Public Sub insertVals(val1 As Variant, val2 As Variant, val3 As Variant)
  Dim strSql As String
  strSql = "INSERT INTO tblOutput(Description,itemCount,qryName) values (" & val1 & ", " & val2 & ", " & val3 & ")"
  CurrentDb.Execute strSql
End Sub

Public Function strVal(varVal As Variant) As String
  strVal = "'" & varVal & "'"
End Function

So my output would look like this

description itemCount qryName
Count of Employees 10 Employees
Count of Sales Representatives 3 Employees
Count of Managers 1 Employees
Count of Orders over 1 Employees
Count of Unit Price = 1 137 qryOrderPrice
Count of Unit Price = 2 283 qryOrderPrice


The nice thing about this approach is that I can bite this off in chunks. I just keep adding another output to my table. Also my inputs can come from anywhere: a query, dcount, dlookup, recordset, other function.

 

[Dontremb]

Hrm, MajP. That certainly looks interesting, and I may play around with that. I enjoy working in code better anyway.

I have two questions:

1) Where do you put that code? Link it to a button or something? a) Where does the output go? tblOutput?

2) I HAVE used VBasic for years, but never on something like that. So, is there a way to filter your data? It looks like you may have done that, but I'm not really sure what your tables and such look like.


If I can figure out how to do it this way, this would be a billion times better than the way I'm doing it now: 200 queries, combined into 7 different "combined" queries. Then, I've got a master report with 7 subreports, invisible, so I can reference all the fields FROM those subreports on the main report. (It's so sloppy I feel like I should shoot myself, but at least the report LOOKS pretty. It's very slow, though.)

Anyway, Thanks MajP, I'll go see if I can figure out how to get it to work for me.

 

[Dontremb]

Also, MajP, how does each know what query or table you're getting the information from?

val1 = strVal("Count of Employees")
  val2 = DCount("EmployeeID", "Employees")
  val3 = strVal("Employees")
  Call insertVals(val1, val2, val3)

I see what Val1 equals,
But val2? It's obviously counting "EmployeeID" and I guess filtering with "Employees" ? But how does it know WHERE to get "EmployeeID" to count?

 

[MajP]

This is not a use a code to replace how you get your counts, but it is a way to dump all of your results into a single report.

I had to do something similar (if I understand what you are looking to do). I had a database on performance calculations on a "Systems of Systems". Each metric was based on a query, but then the results of the queries were starting points to run complex algorithms. Once the calculation was done I would dump the results into a table with Metric Title, Value. It looked like

Metric Value
Earliest Detection Window 10 meters
Night Time Detection Range 5 meters
Recovery Time 2.5 hrs



Your first step is to come up with the smallest set of queries that return your counts. My guess is that a few aggregate queries that Leslie showed would radically reduce your number of queries.


Basically I built a table called tblOutput, that is really nothing more than a place to throw all of my results. It has three fields: a description of what I am counting, the count, and where I got the information from.

1) build tblOutput. Each time I run my code, I clear out tblOutput and then start writing to it.

2) Put the code in a standard module, and call it from wherever you want. Eventually you are going to build a report off of this table. So your order would be

call createOutput()
docmd.openreport "YourReport", acpreview

where your report is bound to tblOutput or a query using tblOutput.

3) Here is maybe a better example using what leslie shows

In the Northwind database there is a table of orders. If I want the count for each Product type in that category

SELECT Products.CategoryID, Count(Products.CategoryID) AS CountOfCategoryID
FROM Products
GROUP BY Products.CategoryID;

output looks like

Category	CountOfCategoryID
Beverages	12
Condiments	12
Confections	13
Dairy Products	10
Grains/Cereals	7
Meat/Poultry	6
Produce	        5
Seafood	        12

so lets say I want to add to my output table the count of orders for beverages. Then I Know the name of the query I want to get my information from it is called "qryProductCounts"

So

val1 = strVal("Count of Beverage Orders")
val2 = Dlookup("CountOfCategoryID", "qryProductCounts","Product = 'Beverages'")
val3 = strVal("qryProductCounts")
Call insertVals(val1, val2, val3

Now If I also want count of condiments
val1 = strVal("Count of Condiment Orders")
val2 = Dlookup("CountOfCategoryID", "qryProductCounts","Product = 'Condiments'")
val3 = strVal("qryProductCounts")
Call insertVals(val1, val2, val3


Now I have this set up in the building block format. I can pull my counts and values many different ways. I can use dcounts, dlookups on aggregate queries or even read recordsets.

Of course if I really wanted all of the counts for each group I would write my code to loop.

Now I do not know what your final queries will look like. It may be that you do not even need this approach because you can use Sql more efficiently.

I build this method to grab data from many different unlike sources.

 

[MajP]

One more thing if I use aggregate queries I can use a union query to bring them all together.

SELECT "Product Count" AS Type, Products.CategoryID, Count(Products.CategoryID) AS CountOfCategoryID
FROM Products
GROUP BY Products.CategoryID;

next query
SELECT "Ship Region Count" AS Type, Orders.ShipRegion, Count(Orders.ShipRegion) AS CountOfShipRegion
FROM Orders
GROUP BY "Ship Region Count", Orders.ShipRegion;

Union
Select * from qryCountOfProducts UNION Select All * from qryShipRegion;


[code]

Results

Type	CategoryName	CountOfCategoryID
Product Count	Beverages	12
Product Count	Condiments	12
Product Count	Confections	13
Product Count	Dairy Products	10
Product Count	Grains/Cereals	7
Product Count	Meat/Poultry	6
Product Count	Produce	5
Product Count	Seafood	12
Ship Region Count		0
Ship Region Count	AK	10
Ship Region Count	BC	17
Ship Region Count	CA	4
Ship Region Count	Co. Cork	19
Ship Region Count	DF	2
Ship Region Count	Essex	13
Ship Region Count	ID	31
Ship Region Count	Isle of Wight	10
Ship Region Count	Lara	14
Ship Region Count	MT	3
Ship Region Count	NM	18
Ship Region Count	Nueva Esparta	12
Ship Region Count	OR	28
Ship Region Count	Québec	13

 

[Dontremb]

Wow, MajP. Thank you so much for going into depth and explaining it all to me.

I believe I actually understand everything now. I still don't think I can use grouping to limit the number of queries effectively, but at least I understand how it's used.

Thanks again. I'm archiving this thread just in case I don't use it right now.

 

[MajP]

Assuming you have all your queries built and they look like

SELECT Count(*) AS IndianHired
FROM (SELECT DISTINCT tblCompilation.IDCompilation FROM qryIndianHired) AS qryIndianHired;

where they return one single field the count and the name of the

I would build myself a table:

tblInputs
description (A description of what is counted)
fldName (Name of field where the count is)
qryName (name of query)

Description fldName qryName
Native Americans Hired IndianHired qryCountIndianHired
African Americans Hired AAHired qryCountAAHired

Now this table then tells you all the queries you have and what they are counting.

Now you could do something like

dim rs as dao.recordset
set rs = currentdb.openrecordset("tblInput")

do while not rs.eof
val1 = strVal(rs.fields("Description")
val3 = strVal(rs.fields("qryName")
val2 = dlookup(rs.fields(flName),rs.fields(qryName))
Call insertVals(val1, val2, val3)
loop

That is is. Output from one hundred queries put into a table based on 100 rows in a table. Quick and easy.