?
Solved

Creating an Access Report from multiple queries

Posted on 2005-04-04
8
Medium Priority
?
8,948 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

801 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