Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports with no entries (Access 2002)

    Hi all,
    I have written a report that with 4 sub reports each with a numerical total in the sub report footer. On occasion, any one of these reports may have an underlying query with no records. This means my Sum() function throws up an error. I tried to get around it using:
    =IIf(IsNull(Sum([RCPCOST])),0,Sum([RCPCOST]))
    where [RCPCOST] is the field I am totalling at the end of each sub report, but I still get an error with an empty query. Any ideas anyone?

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports with no entries (Access 2002)

    Try
    <pre>=IIf([Report].[HasData],Sum([RCPCOST]),0)</pre>


  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with no entries (Access 2002)

    Thanks Hans, works fine now

  4. #4
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with no entries (Access 2002)

    New problem with the same report. The subreports all have their "can shrink" properties set to yes. Now, if I run the subreport by itself with the criteria set so there is no data, it prints out the total of $0.00 at the end of the report (single line). However, if I run the master report and one of the sub reports has no data it shrinks the whole subreport to nothing and the expression I use to total the subreport totals throws out an error. The expression I use is:
    =([SubRptCompPurch].[Report]![TxtGrandTotal])+([SubRptOutstandPurch].[Report]![TxtGrandTotal])+([SubRptIncompRCP].[Report]![TxtGrandTotal])+([SubRptMiscPurchases].[Report]![TxtGrandTotal])

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports with no entries (Access 2002)

    The answer involves HasData again.

    Replace

    [SubRptCompPurch].[Report]![TxtGrandTotal]

    by

    IIf([SubRptCompPurch].[Report].[HasData];[SubRptCompPurch].[Report]![TxtGrandTotal];0)

    and analogously for the other three subreports.

  6. #6
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with no entries (Access 2002)

    Thanks again Hans.

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with no entries (Access 2002)

    Oddly enough, I too have a report with 3 subreports and totals running through all of them - mine is an invoice with rental, labour, other charges all as subreports. Of necessity, each subreport has a sequence of 2-4 queries to run in order to display and total the data in an effective way. There are many iif's, sums, and some count functions run along the way.

    Once I discovered HasData, I was able to ensure that the totals worked, but this report is -slow-. The tables are virtually empty and it takes 8-10secs to load and display my Event dialog form. I noticed a large decrease in speed when I split the front end and the tables into 2 dbs.

    Any suggestions on how I can improve the speed? I've indexed all joined fields and any fields I use as criteria.

    Oh yes - Two users on different machines will be using the db (tables on server, queries, forms, etc on each of their machines.

    Thanks for any help,
    Suzanne <img src=/S/doze.gif border=0 alt=doze width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports with no entries (Access 2002)

    Which database format are you using, Access 2000 or 2002? Access XP will create either flavor. Have you turned off subdatasheets on all tables in the backend by setting the property to [None]?
    Charlotte

  9. #9
    New Lounger
    Join Date
    Jun 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with no entries (Access 2002)

    I'm using Access 2000, Charlotte.

    After reading your post, I went in and checked - I removed all the subdatasheets (there were 3 in total). No noticieable improvement in speed. I decided to keep the back end together with the front end for the time being.

    What I have is a monstrous report for invoicing events in a theatre - a dialog pops up to select the event, two queries feed the main report which includes, one subreport to calculate the rental charges (3 queries), one sbrpt to calculate labour (4 queries), one subrpt to calculate Production Charges (3 queries), on-report calculations for credit card charges and capital Renovation Fund charges, totalling and then, to top it off, the footer contains one more report, one that needs to be sent to Financial Services with the acct information.

    I wanted to spring this one separately, but most of the information I need is contained in the invoice and it seemed most efficient to gather it up then.

    Any ideas for streamlining? I'm a little appalled at the number of queries required, but the totalling and grouping required is horrific. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>



    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports with no entries (Access 2002)

    Did you remove subdatasheets from front AND back end? Did you turn off Name Autocorrect? I can't imagine why it would take 3 or 4 queries to calculate some of your subreports. It sounds like you've built something almost guaranteed to run slow.
    Charlotte

  11. #11
    New Lounger
    Join Date
    Jun 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with no entries (Access 2002)

    Thanks for the help, Charlotte!
    >sigh< Yes, it does seem guaranteed to run slow.

    I have removed all the subdatasheets from all tables (there weren't too many). Do these hamper performance a great deal? While subdatasheets are handy for tracking down data for clients, I don't use them much in developing. So - they're gone.

    I've had a second look at the queries I'm building my data with. In some cases, I grabbed the data with the first query, performed some calculations and then used the calculated fields to join with another table and performed more calculations. Lastly, I've run a totalling query to make it clear to the Client what exactly they are being billed for.

    So, my question: Is it always better to use one query with all the fields you need, and perform all the calculations you need to make there, or is there any advantage to performing an initial grouping and then adding other tables and calculations?

    I'm designing this for my husband's business, so there's the added stress of poor communication between developer and client. [img]/forums/images/smilies/smile.gif[/img]

    I appreciate the advice

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports with no entries (Access 2002)

    Actually, I've found that with very large recordsets, applying a filter at a lower level reduces the number of records that have to be processed in your calculations and can actually speed up the final result. So I filter my records at a lower level but do most of the calculating at the top. If you're actually joining on calculated fields (why, by the way? it doesn't make a lot of sense), that alone will slow the whole thing down because there are no indexes on calculated fields.
    Charlotte

  13. #13
    New Lounger
    Join Date
    Jun 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with no entries (Access 2002)

    First I should tell you, I've managed to get rid of three of those embarassing 'extra' queries. I'm working on others. I'm not actually joining on calculated fields... This is what I'm trying to do.

    Like you suggest, I filter out at the first level - only usage records pertaining to the user-selected Event are chosen. (Many are called, but few are chosen)

    Query 1: I need to know how many usage events per day are performances. (All other events - meetings, lectures, rehearsals, etc are billed per hours used, but with a performance you get 8 free hours in that day, two or three performances - 12 free hours.) You can have more than one event type relating to a specific event in a day - rehearsals in am and pm, show in evening, for example.

    So I use iif([performance],1,0) and then I total based on date, yielding the number of performances for each day of usage.

    Query 2: I hook in the dates and # performances from the first query, pull in rate types for this client and calculate the difference between [time out] and [time in] less break times (all expressed as quarter hours, the billable unit), I round to the nearest quarter hour, convert back to actual hours, calculate the total hours used per day. This is a totalling query, so totalhours by day is now created AND related to the number of performances in the same day. That's crucial. I caluculate OT (based on all three possibilities) and end up with and EventChg.

    I thought programming the whole thing in VBA would be preferable - I could use Case statments and lots of IFs to work it out, but I've never done a whole report using VBA.... perhaps one this complex wouldn't be the best choice.

    Any advice? What would you do with a report like this? Keep in mind that on my Invoice there are also Labour charges (equally complex to calculate) and Production charges, plus a report for Financial Services in the report footer. Is there a magic number of queries, say 2, that should be all anyone needs? One to total, one to calculate?

    I appreciate the ear - I don't know any other Access programmers. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports with no entries (Access 2002)

    IIF is notoriously slow, so if you have a large number of records, try Abs([Performance]). That will give you a 1 or a 0, assuming Performance is a yes/no field. I take it that Query1 is a groupby query and you're using something like Sum(Abs([Performance])), right? Better yet, just Sum the Performance field and worry about the Abs() function in the next query. I'm not sure I understand why you're totalling in both queries, or did I misunderstand?

    One thing I might suggest is temporary tables to reduce some of the complexity. That should speed up your report generation as well. You can create permanent tables that you clear out and repopulate when you need to run the reports. Populate the tables from some of your first or second level queries and base higher level queries on the tables. That should speed things up quite a bit.

    One of the problems with reports is that they create temporary queries on top of the saved queries, and things can get a little slow at that point.

    I'm not sure what you thought about doing in VBA. If it were a stored procedure in SQL Server, you actually could do a bunch of this stuff sequentially and then open a view on the end result. In Access, all you could do in code would be to build the SQL for the report's recordset. If you're thinking about an unbound report populated entirely from code, then no, this wouldn't be a good report to learn on.
    Charlotte

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reports with no entries (Access 2002)

    If your data is not sensitive then post it to the lounge or email a zipped version if it's too big.
    Cheers,
    Pat

Posting Permissions

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