Al,
I can do this for the Case Managers and Programs they are assigned to, but the Programs
have seperate tables which create Unique File IDs based on the Main File ID. This is
done because the client can be opened under multiple programs and the Open and Close
Dates are Tracked (they can be open and closed muliple times depending on the program).
I am not sure how to create a single query that will show All of the open clients in
each program along with program id and last open date.
This is what the current open client for program looks like. I have a query like this
for each program.
SELECT tblMain_File.CFN, tblMain_File.CN, tblMain_File.IOD, tblFST.EMPID, tblFST.CM,
tblFST.FSTFID, Count(tblFST.FSTFID) AS CountOfFSTFID,
Max(tblFST_Case_File_Open_Closed_Dates.dtmOCDate) AS MaxOfdtmOCDate, tblFST.PID
FROM tblMain_File INNER JOIN (tblFST INNER JOIN tblFST_Case_File_Open_Closed_Dates ON
tblFST.FSTFID = tblFST_Case_File_Open_Closed_Dates.FSTFID) ON tblMain_File.CFID =
tblFST.CFID
WHERE (((tblFST.Open)=True))
GROUP BY tblMain_File.CFN, tblMain_File.CN, tblMain_File.IOD, tblFST.EMPID, tblFST.CM,
tblFST.FSTFID, tblFST.PID
ORDER BY tblMain_File.CN;
****************************
My Case Manger Program query looks like this:
SELECT tblUSys_Employee.EMPID, tblUSys_Employee.FN, tblUSys_Employee.LN, [LN] & ", " &
[FN] AS Name, tblUSys_Program_Name.Program
FROM tblUSys_Employee INNER JOIN (tblUSys_Program_Name INNER JOIN tblUSys_Program ON
tblUSys_Program_Name.PID = tblUSys_Program.PID) ON tblUSys_Employee.EMPID =
tblUSys_Program.EMPID
WHERE (((tblUSys_Employee.CM)=True) AND ((tblUSys_Program_Name.CM)=True) AND
((tblUSys_Program_Name.SubProgram) Is Null))
GROUP BY tblUSys_Employee.EMPID, tblUSys_Employee.FN, tblUSys_Employee.LN, [LN] & ", " &
[FN], tblUSys_Program_Name.Program;
*************************
How would I combine this to show the open clients from each program similar to the query
above?
-Thanks For The Help
Bryan
Al Camp said:
Yes there is... What happens if you add another category? Say, [txtFCAP2], or
[txtFCAP3], etc...
Now you'll have to return to report design and add another subform to handle that
value?
In fact, if your tables of Managers and Categories is designed properly and
associated, you shouldn't need any subreports at all.
Create a query that list every manager, and those categories/monies associated with
each manager. Then, basically, add Group By Manager and under that, a Group by
Category.
The report should print a manager, and all the associated categories. Then... the
Count in the Category groups Footer would yield totals for each category, and Count in
the Manager Footer would yield the total for the Manager, and a Count in the Report
Footer would yield the Grand Total.
Let the power of the report "grouping" do the Count work for you. And... if you add
another category later, it will just flow out along with all the others, under each
associated manager.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Bryan Hughes said:
John,
I got it to work.
I made anoter textbox txtTotal1 in the group footer. Have the Running Sum to Over All
and made the source
=[txtCAC_Count]+[txtCVAC_Count]+[txtFCAP_Count]+[txtFST_Count]+[txtGT_Count]+[txtPCIT_Count]+[txtPCV_Count]
Then the source for the report total is texTotal1.
I don't know if there is a better way but this works.
Bryan
NZ works on Null values
If there are no records returned, then there isn't any record to have a null value in
the field (control). Basically there is no object in the current context.
Bryan,
Well, I think you're just mimicking the NZ function via the HasNoData property.
Different strokes...
I'd still use
=NZ(rsubPCV_Open_Case_Files.Report!txtCount)
So, you now show the correct totals individual totals of the 5 subs on the main
report...
Try summing those 5 calculated fields in the Manager footer, adding the 5
calculations for each total.
ex.
=NZ(rsubPCV_1_Open_Case_Files.Report!txtCount)
+NZ(rsubPCV_2_Open_Case_Files.Report!txtCount) + ... etc for all 5
and then use a running total against that group footer field (OverAll) in the report
footer
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Al,
Thanks, I figured this out by creating a txtCount_Program for each program, setting
the RunningSum Property to OverGroup, and doing the following in Control Source.
=IIf(rsubPCV_Open_Case_Files.Report.HasData,rsubPCV_Open_Case_Files.Report.txtCount,0)
Then I have a txtCM_Count in the group footer that adds the totals from each
program txtCount_Program. This shows the totals for each case manager.
Now I need to get all the totals for the Case Mangers to show the totals for
everyone in the report footer.
How should I do this?
Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each subreport
you had a subform footer totals called Case1Totals and Case2Totals respectively.
An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] + NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Hello,
I have a report that has Case Manager Group, and 5 different sub reports showing
cases assigned to the case manager in different programs. Each sub report has a
count total. I need to get the total from each sub report and then total that to
show the total cases for each case manager. How can I do this?
-Thanks for the help
Bryan
