Show results from several queries

  • Thread starter Thread starter Katt
  • Start date Start date
K

Katt

I want to create a report that shows the results of 6
different queries. Is this possible?
-Katt -lost in Spokane
 
I want to create a report that shows
the results of 6 different queries. Is
this possible?
Click to expand...

It is possible to bind a report to only one query.

Depending on the particulars of your database and these queries, it may be
possible to create a query that joins the 6 queries, or it may be possible
to use Reports imbedded in Subreport controls on your main Report to show
some/all of the data.

If you want to clarify with more detail, perhaps someone (not necessarily I)
can give you a more specific answer.

Larry Linson
Microsoft Access MVP
 
Yes. Create a query that pulls the information from the 6 other queries
then use that query as the record source for your report. Or if all the
tables in the queries have relationships to each other, you can use the
report wizard.

Nelson
 
I have 6+ queries that gather the "count" of yes/no
records - yes values only. I want a report to take and
give me a listing of the outcome of each query on one
report printout.
This is since I could not create one query to report
the "count" of yes answers for each field. All it would
give me is the count of records period. Never just the
yes/true answer. I created one query per category I
needed to retrieve.
Now I want to combine it all onto one report to printout
at the end of the month.
-Katt
 
Do I read between the lines that you actually have one
table with yes no answers for different fields, you
created multiple queries to count the results and now want
to combine the various queries to create a single report?
If this is the case. I would suggest creating one query
to the yes' or no's and create the report based on this
one query.
Post back if this is the case and give some description of
the fields soI can give some pointers.
Hope this helps.
Fons
 
In a nutshell - yes.
I had tried getting help on the query end of things
(discussion group) but to no avail.
I have a form/table with 16 fields of yes/no values that I
want only the yes counted and returned on a report/query.
I have now created many seperate queries to count
each "yes" answer I need. When I tried creating one query
to return only yes values it counted every single record
and not yes values as I had hoped. Even with a Criteria
of YES.
Now I want a report/query to gather all the totals from
every query that I have running correctly (by month) and
return that information.
-Katt
 
Katt said:
I have 6+ queries that gather the "count" of yes/no
records - yes values only. I want a report to take and
give me a listing of the outcome of each query on one
report printout.
This is since I could not create one query to report
the "count" of yes answers for each field. All it would
give me is the count of records period. Never just the
yes/true answer. I created one query per category I
needed to retrieve.
Now I want to combine it all onto one report to printout
at the end of the month.
-Katt
Click to expand...


You have gone a little too far down the wrong path and asked
the wrong question. The question really is - How can I
count the True values in multiple columns in one query?
This can be done fairly easily by using a conditional
expression in the Sum function. E.g.

SELECT Abs(Sum(f1)) As f1Yes,
Abs(Sum(f2)) As f2Yes,
. . .
Abs(Sum(fn)) As fnYes
FROM table

Abs(Sum(f1)) is the most concise and fastest way to do this,
but it is somewhat obscure and relies on the Access value of
True being -1. If speed is not an issue and you prefer more
clarity, you could use any number of other expressions.
E.g.
Count(IIf(f1 = True, 1, Null))
Sum(IId(f1 = True, 1, 0))
 
Thanks Marshall -
Now my problem is making sense of what you have written
(LOL). I can create queries/reports/forms but only in
design or with a wizard. I have never tried SQL. How can
I take your statements and make them work for the layman?
-Katt
 
Thanks Marshall -
Now my problem is making sense of what you have written
(LOL). I can create queries/reports/forms but only in
design or with a wizard. I have never tried SQL. How can
I take your statements and make them work for the layman?
Click to expand...


Create a new query without bothering picking a table, and
switch to SQL view. Next, Copy/Paste my sample SQL
statement into the SQL view of the new query, replacing
whatever Access already had there. Then, change the "f1",
"f2", etc to your own field names and change "table" to your
table name.

Now switch back to design view. It should look more
familiar to you so you can make any additional changes in
the QBE grid.
--
Marsh
MVP [MS Access]



 
I copied the statment but when I change the f1 to my field
name as stated in the table, I get an error message.
Would that have anything to do with my field names having
spaces in them?
-Katt
-----Original Message-----
Thanks Marshall -
Now my problem is making sense of what you have written
(LOL). I can create queries/reports/forms but only in
design or with a wizard. I have never tried SQL. How can
I take your statements and make them work for the layman?
Click to expand...


Create a new query without bothering picking a table, and
switch to SQL view. Next, Copy/Paste my sample SQL
statement into the SQL view of the new query, replacing
whatever Access already had there. Then, change the "f1",
"f2", etc to your own field names and change "table" to your
table name.

Now switch back to design view. It should look more
familiar to you so you can make any additional changes in
the QBE grid.
--
Marsh
MVP [MS Access]



value
of
Click to expand...

.
Click to expand...
 
Katt said:
I copied the statment but when I change the f1 to my field
name as stated in the table, I get an error message.
Would that have anything to do with my field names having
spaces in them?
Click to expand...

Yes, that would be illegal. Be sure to place square
brackets around names the have any non-alphanumeric
characters in them. E.g. [Name with spaces]
--
Marsh
MVP [MS Access]


 
Back
Top