two queries one report
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
thanks in advance.
charly
ASKER
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.
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.
what version of access are you using?
post the query you are using.
post the query you are using.
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.
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.
ASKER
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.PhoneNumbe
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)="A
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)="A
GROUP BY Members.BadgeStatus
PIVOT Members.MemberStatus;
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.PhoneNumbe
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)="A
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)="A
GROUP BY Members.BadgeStatus
PIVOT Members.MemberStatus;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
charly
insert this report as a subreport in your roster report.