Solved

two queries one report

Posted on 2014-03-17
8
237 Views
Last Modified: 2014-06-28
Hello, i have a roster report where i list members sorted by their last name. there are three types of members in this report. at the top of the report i would like to have a txt box or label that shows the count of each type of member. how can i add a second query to a section of the report or some other way of adding this data to the report.
thanks in advance.
charly
0
Comment
Question by:cazink
[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
  • 3
  • 3
  • 2
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39935165
you can create another report, using the second  (totals query) as the record source.

insert this report as a subreport in your roster report.
0
 

Author Comment

by:cazink
ID: 39935276
what is in the box i am trying to add to the report would look like this:
active members: 345
trial members: 44
life members: 92
the report is pages long and if the subreport was added at the end would just get lost. i was trying to get this in the heading or report header.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39935296
what version of access are you using?

post the query you are using.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 38

Expert Comment

by:Jim P.
ID: 39935399
Make a copy of the the report and name it something Member_Summary.

Do you already have the counts at the bottom of the report? In the Member_Summary add them in as you want them to appear in the footer. Then open up properties and set the Visible property to No for the Header and the Detail section. Save the report once you get the footer looking like you want it to.

Then go back to the original report and drag the Member_Summary into report header.

That's how you get the totals into the report header.
0
 

Author Comment

by:cazink
ID: 39938462
Jim, i think you misunderstood me,
Rey, i am using 2010 access. the main report query is the following:
SELECT Members.*, Trim([Title] & " " & [FirstName] & IIf(Not IsNull([Middle])," " & Mid([Middle],1,1) & ".") & " " & [LastName] & " " & [Suffix]) AS FullName, Trim([Address1] & IIf(Not IsNull([address2]),"  " & [Address2]) & "  " & [City] & ", " & [State] & " " & Mid([Zip],1,5) & "-" & Mid([Zip],7,4)) AS Address, Trim([City] & ", " & [State] & " " & [Zip]) AS CityStateZip, Members.DriverLicense AS MVA, Members.BadgeStatus, qryPhone_Home.PhoneNumber AS home, qryPhone_Mobile.PhoneNumber AS mobile, qryPhone_Work.PhoneNumber AS [work], Mid([Members].[MemberStatus],1,1) AS Status, Left([WorkPartyStatus],1) & IIf(Not IsNull([ExemptStatus]),(", " & Left([ExemptStatus],1))) AS wp
FROM ((Members LEFT JOIN qryPhone_Home ON Members.MemberID = qryPhone_Home.MemberID) LEFT JOIN qryPhone_Mobile ON Members.MemberID = qryPhone_Mobile.MemberID) LEFT JOIN qryPhone_Work ON Members.MemberID = qryPhone_Work.MemberID
WHERE (((Members.BadgeStatus)="Active"));

then database has two types of members' badge status, active and inactive. the report screens out the inactive ones. of those left there are three types of members, active, trial, and life. each member has a line with address and phone numbers and the last field shows what type of member he is. L,T,A. what i am trying to do is at the top of the roster report place a box which has the totals in it. such as
Trial-92
Life-105
Active-437
i have a report that generates this with the following:
TRANSFORM Count(Members.MemberID) AS CountOfMemberID
SELECT Members.BadgeStatus, Count(Members.MemberID) AS [Total Of MemberID]
FROM Members
WHERE (((Members.BadgeStatus)="Active"))
GROUP BY Members.BadgeStatus
PIVOT Members.MemberStatus;
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 39938479
Run this query

SELECT Members.BadgeStatus, Count(Members.MemberID) AS [Total Of MemberID]
FROM Members
WHERE (((Members.BadgeStatus)="Active"))
GROUP BY Members.BadgeStatus

and post the result
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 250 total points
ID: 39938495
the report is pages long and if the subreport was added at the end would just get lost. i was trying to get this in the heading or report header.

I'm saying that by making the report footer visible with the results as you want them and then making the header and details section invisible it become the subquery/subreport that you can put in the header.
0
 

Author Closing Comment

by:cazink
ID: 40165153
Thank you gentlemen, this has been put on back burner for a short time, then i will get back to the report. both have given me some ideas i can use elsewhere. that is more important than just getting one answer. this keeps the mind working. with out thought i gave you both half for your time. not sure which approach i will use, but thanks non the less.
charly
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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 …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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