I have a report that is based on a name field, which works OK as a standlone report.
But i want to link the totals on the report to different 'status' field results.
So I have 'status' field OPEN & CLOSED and want the report to look like this;
(Name) (Open) (Closed)
NAME - 0 2
NAME - 1 4
Get my drift - so it shows different totals (i have the queries in place if i need to use them) for each NAME on the report, how would i do this - i have to have two seperate reports at the moment for the same results and would like these result totals on the same report.
Several different ways come to mind, but it depends on where is this going to be used. If, what we are looking at is a list of NAMEs with the Open count, and the Closed count, then a sub-report will do the trick with no problem. If that is not it, please let us know how and where you need these counts.
actually just worked it out much like subforms and linked to different queries, but how do i make it so it links different months along the row as well as if there is no total in query to display '0' (eg: if jobid count = 0 within the query/sub-report)
When I have several subreports that have no actual data to match with in the main report, I create hidden fields on the main report, set their Control Source properties to "=1", or "=2", etc for Jan, or Feb, etc.
Each subreport needs to print the 0 when there is no data. That is a Report event named OnNoData.
I have gone back and read the whole thread and I don't see enough detail here to be able to give you the detail you are now asking for. Rather than (Name), (Open), and (Close) as you specified in your first post, please give some actual detail of what the fields are in the table you are using for the report. What is the Name field a name of. And about how many names are we dealing with?
right i have a table MAIN which has STATUS field (with 3 selections eg: OPEN, CLOSED, ON HOLD). The Table also has JOB ID, DATE, NAME and DETAILS as fields among other misc fields.
There are about 3 or 4 different names used at this time.
The Report has a MAINREPORT which holds the NAME, DATE fields and I have several subreports which are linked to various queries that show different STATUS fields by Count(JobID).
Does this make it clearer? Did you want me to send you the database?
MAINREPORT ---> (linked to TBL_Main)
SubRep_Open (query only shows OPEN status fields depending on name above)
SubRep_Closed (query only shows CLOSED status fields depending on name above)
SubRep_OnHold (query only shows ON HOLD status fields depending on name above)
What are these details in the sub reports? The NAME field refers to what name? A person's name, the name of something, if so what is the something? I know this whole thing should be able to be done in concept, but I have found that when we actually discuss reality is when the final details come out that I have not been able to see. Sorry I'm not getting it, but actual details have really helped in the past.
I forgot to state that the report now actually has NAME at the top and I have JAN, FEB displayed down the side but the totals show all the year thus far and don't split the months up? does that make sense?
if anyone can help further i would be very grateful.
the problem i have got is that how do i link a field count to a different query, so within the same subreport can I have a field (TotalOpen) linked to a different query (so it counts the records for all OPEN), and the same for (TotalClose) linking to Query for closed jobs, etc.. on the same subreport or do i need to use different subreports for the same results - if so, how do i link the same month to each total. I can link the date/month if its within the same subreport but not when seperate ones are used? please help...
*** or as anyone got a quick example database using totals on reports (in rows) that i can view and revamp to within my database
Right I got it all working using CrossTab Queries which auto totals the info up for depending on status field and engineer.
But I have yet another problem now - how do i make it so the engineer field within this CrossTab field can be linked to my REPORTS Form (which links to engineer combo box - cboEngineerTotal)
I use it like I would on any other query but it states i can't??? any idea's how I would do this without creating a report for each Engineer.