Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Different Totals in reports

    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.

    Thanks.
    Last edited by NeilMansell; 02-07-07 at 09:17.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    silly question but how would i use the subreports?

    I have created seperate reports linked to seperate queries but when i add them to the main report the info is just duplicated.
    can you explain please?
    Last edited by NeilMansell; 02-12-07 at 06:13.

  4. #4
    Join Date
    Mar 2004
    Posts
    287
    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)

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.

    Hope this helps,

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    can you please give me an example of what you mean?

    I think i understand but its not working for me.

    I have it setup like this;

    MAIN (Date Month) then subreports with Count(JobID) linked for each query


    I think the problem is that i need to inclde the date month in the subreport not on the main report?!
    As well as having it so it equals 0 when no data.

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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?
    Thanks,

  8. #8
    Join Date
    Mar 2004
    Posts
    287

    update

    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)

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.
    Thanks,

  10. #10
    Join Date
    Mar 2004
    Posts
    287
    NAME is a persons name, TEXT 50 characters long.

    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?

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    what should the subreport holding in the way of data fields, just the count(jobid) or date as well?

  12. #12
    Join Date
    Mar 2004
    Posts
    287
    cab you help any further on this, do you need more info? please?!

  13. #13
    Join Date
    Mar 2004
    Posts
    287
    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
    Last edited by NeilMansell; 02-15-07 at 09:04.

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    LATEST UPDATE:

    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.

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Neil,
    Thanks for the very timely update. I had just worked out a solution and was in the process of typing it in.

    Now, please provide the detail of how you are doing this "linking" that Access says you can't. Detail = actual coding, etc. Not a description of it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •