1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2000 Report Summing

Discussion in 'Business Applications' started by ccoop61, Sep 10, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. ccoop61

    ccoop61 Thread Starter

    Joined:
    Sep 10, 2003
    Messages:
    4
    I have created a report that runs off a crosstab query which allows users to select their criteria. I am using DSum to create subtotals from the report, but Access is summing totals from the universe of the crosstab query and not just the criteria that was selected. Is there a way that I can get Access to calculate the DSum from what the user selected to run the query?
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Make sure the record source for the control in which you use the DSum is set to the query and not the table.
     
  3. ccoop61

    ccoop61 Thread Starter

    Joined:
    Sep 10, 2003
    Messages:
    4
    The record source referenced in the DSum is set to the query. When the user selects the report, they then enter in their criteria that should be shown on the report. All of the information in the report reflects what was selected by the user except for the DSums I have added.
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Sorry. That's all I've got without seeing it.
     
  5. ccoop61

    ccoop61 Thread Starter

    Joined:
    Sep 10, 2003
    Messages:
    4
    Thanks for trying. Do you know if Access could be closing the query as the user ran it before it calculates the DSum? The reason I'm wondering is because the sum I am getting is what the query returns when no criteria is selcted.
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    I'm not sure. How is the criteria being asked? Through a regular query criteria? Or query by form?
     
  7. ccoop61

    ccoop61 Thread Starter

    Joined:
    Sep 10, 2003
    Messages:
    4
    It's query by form. A form is set up where the user selects the report they want to run and then select the criteria...it's all done on one form.
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Def'ly something the matter with the form then. Sorry--I'm crap at troubleshooting Access without seeing the DB. :(
     
  9. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Try building a non-crosstab query which draws data from the crosstab results. Add the crosstab to the QBE window's upper half, and you can select its "fields" as you would with any other query. Then set your criteria there, to match the form fields. And make any DSum statements match that query. Though, in theory, if the criteria are already in the form, there's no reason to use DSum.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/163705

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice