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

x
?
Solved

Creating an Access Report from multiple queries

Posted on 2005-04-04
8
Medium Priority
?
8,951 Views
Last Modified: 2011-08-18
Hi,
I asked this question previously and the solutions did not help.  I really need an answer on this.  I have a database with many queries that provide counts on responses.  I need to put the results of all the queries into one report.  For example:

query1 - count of smokers  20

query2 - count of depression 13

query3 - count of high stress level  24

etc.

How can I have the results appear in one report?  I tried subreports, but each subreport takes up too much room, when it needs to be just one line of data.  

Thanks!
0
Comment
Question by:suehunt
8 Comments
 
LVL 4

Expert Comment

by:Parax77
ID: 13697654
try using dlookup() to get a value from a table or query

eg
=dlookup("MyValue","MyQuery","Conditions")


0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 13697682
Hi suehunt,


Is the count indicated by a checkbox/boolean type?

Try ...

SELECT COUNT(*), COUNT(smokers), COUNT(depression), COUNT(stress) FROM Results

Or ..

SELECT COUNT(*), COUNT(ALL smokers), COUNT(ALL depression), COUNT(ALL stress) FROM Results.

smokers, depression and stress are column names in Results.
smokers, depression and stress need to be null of you do NOT want to count them.

I use this mechanism in SQL Server, but MS Query does not understand it at all and thinks the query is corrupt.


Regards,

Richard Quadling.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13697694
Alternatively, if each query has the same number of fields, you could UNION them:

SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
UNION ALL
SELECT * FROM Query3

If each query doesn't include a description, you can include it within the union:
SELECT "Count of smokers" As Description, * FROM Query1
UNION ALL
SELECT "Count of depression",* FROM Query2
UNION ALL
SELECT "Count of high stress level",* FROM Query3
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.

 
LVL 4

Accepted Solution

by:
Parax77 earned 2000 total points
ID: 13697756
Further explanatin and examples:

to get the field [CountofSmokers] from [Query1] where the query returns one row use
=dlookup("[CountofSmokers]","[Query1]")

to get the field [CountofSmokers] from [Query1] from a record with ContactID = 10
=dlookup("[CountofSmokers]","[Query1]", "[ContactID]=10")

you should also be aware of dcount()
to count the records in a table called [contacts] (Primarykey = ContactID) where the yes/no field [smoker] is set to yes, Use:
=dcount("[ContactID]","[Contacts]","[Smoker] = True")

(Caution do not overuse domain aggregate functions as they can be responsible for slowing down databases as each instance is calculated independantly)
0
 
LVL 4

Expert Comment

by:McDougall
ID: 13698594
Could we do this?

SELECT Qbored.CountOfDescription, QHungry.CountOfDescription, QSmoker.CountOfDescription
FROM Qbored, QHungry, QSmoker;
0
 

Author Comment

by:suehunt
ID: 13721120
I've tried your suggestions, and still no luck.  To further explain the structure of the queries, each query contains 3 fields.  To be specific, I'll use qryHealthIssues as the example.  Two of the fields are Health Issues and one is a Group By, the other is the Count.  The last field is a date field with the criteria of Between #6/30/2004# And #5/28/2005#.  I have around 50 queries, all providing counts that need to be put into one report.  Thanks!
0
 

Author Comment

by:suehunt
ID: 13721259
I spoke too soon, sorry!  I must've had a syntax issue the first time I tried =dlookup("[CountofSmokers]","[Query1]"), but it didn't give me that error.  Just tried it again, and IT WORKS!!!! Thank you Parax77!!!!
0
 
LVL 4

Expert Comment

by:Parax77
ID: 13725916
Glad you got it working >:o)
If your queries are fairly simple you may want to consider just using the domain aggregate functions to examine your tables without needing to create loads of queries, the functions all use conditions that you would write in the same way as a query where clause (except group! this is done using the dmax, dmin, dcount, or dsum functions), you can even use the functions as fields in queries, but be aware that they are processed one at a time hence a query with a thousand rows would examine the source table/query a thousand times!

Parax

p.s. be aware that dlookup only returns the first result that matches the criteria and ignors any others.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

564 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