Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    35

    Unanswered: Do I use reports for calculations?

    Hi.

    Now that I have my data imported into tables, how can I calculate and print the results?

    My calculations will be as simple as count how many records contain option "abc" in field D, for example.

    I created a form with text fields where the user can input a few filtering parameters such as starting date, ending date, person name etc. When these are entered I can probably perform an sql query that will count records based on the filtering.

    But considering I have quite a few of these statistic calculations - probably 2 or 3 pages long,
    Is it reasonable to create an sql query for every one of the calculations?
    Is report a good idea for organizing the output? If so, can a report update itself based on my filtering input?

    Thank you!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Using a query, you can do many (all) calculations there and then you can print and summarise them in your report(s).

    You can use expressions like ABCCount: Iif([Field D] = "abc", 1, 0) in a query column and then =Sum(ABCCount) in your report to show these kinds of results.

    The query you base your report on can also extract only those records between your dates as well

    Is it reasonable to create an sql query for every one of the calculations?
    You can do it, but I wouldn't
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2007
    Posts
    35
    Sounds like it can do nicely, what is a query column though? a column in query design view?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it depends entirely on what you want to calculate
    if you can do it it SQL then do it in SQL
    however for some times SQL isn't practical (fer performance, data handling or other reasons). like so many things in the systems world their often is more than one right answer, and choosing 'the' right answer depends on circumstances, often the developers familiarity of certain techniques.

    One thing to bear in mind with Access reports is that just like Access forms you can hang code behind events in the report. so you can often do a great deal of calculations or data manipulation in the forms on format (and other) events.

    it comes down what information you are having to process and how its organised

    bear in mind that an Access report doiesn't have to have anyhtign in a detail section, so you could arrange your report and just pull in summarised data by setting appropriate grouping in the report, and if you like use the aggregate functions =count([column name])

    if you can write your SQL to do the summing then do so.. its going to be a lot more efficient than writing the code in the report.

    if say you need to do a count by product group you can probably do that as a simple query

    eg
    select Count(productid) as NoProducts, FieldD from mytable
    group by FieldD

    you will need to replace productid with a valid column name from your table

    perhaps if you elaborate on what you want to achieve.. its a wee bit vague at present

  5. #5
    Join Date
    Nov 2007
    Posts
    35
    Take a look at these screenshots taken from an Excel illustration:
    The data, Statistics

    In Excel, I would use array functions to count how many records meet certain criteria pretty much like so.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by ronh
    Sounds like it can do nicely, what is a query column though? a column in query design view?
    Yes. That's correct.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2007
    Posts
    35
    Thank you, all valid suggestions.
    In any case I'm now using the DCount function by passing it my filtered query, it makes things pretty efficient I think.
    The query filters the global criteria such as date range and person's name - these won't vary throughout an entire calculation. So it only makes sense to filter them once.
    Then the DCount function goes through the above query and counts records that meet more specific criteria.

    I'm still not decided what is an easy way to organize all the information.
    In Excel I would simply create a new worksheet and populate cells with the functions I need (See above screenshots).
    Do I really need to create a text box for every value I want to print? There must be another way?

    Thanks.

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    When you create a report in Access and other reporting tools, you create template for a Header(s), Detail, and Footer(s). Then Access uses the data to populate the report. If you are concerned about having to add too many textboxes then I would think you are trying to put too much information in one report.

    The second screen shot looks like a good candidate for a Cross Tab query. The Object field would be your Row Header and the Place field would be the Column and the Data would be the Result column.

    I found Cross Tabs to be a little confusing when I first started using them, so if you need more help let us know.

  9. #9
    Join Date
    Nov 2007
    Posts
    35
    Thanks, crosstab query was pretty much what I was looking for.
    I base a new crosstab query on my previous date and name filtering query, but I get error 3070 when running it.
    Access complains that it can't recognize my 'forms!F_Welcome.Box_SDate' as a valid field(or any of the other fields if I omit this one), which is where the user inputs the criteria.
    My filtering query works by its own, so it beats me why crosstab won't work.
    Any ideas?

    The crosstab query is built by the wizard.
    Here's my filtering query for reference:
    Code:
    SELECT *
    FROM T_DNA
    WHERE (F2>=Forms!F_Welcome.Box_SDate) And F2<Forms!F_Welcome.Box_EDate) And
    (F11=Forms!F_Welcome.Box_Technician Or "All"=Forms!F_Welcome.Box_Technician) And
    (F5=Forms!F_Welcome.Box_Station Or "All"=Forms!F_Welcome.Box_Station);
    EDIT:
    Oh. Apparently I forgot to declare the parameters for the crosstab query. Yea it seems to work now.

    EDIT:
    But do I have to specify the column headings if I want to use the crosstab query inside a report? I would much rather leave the column headings to be decided automatically.
    Thanks
    Last edited by ronh; 04-23-08 at 07:00.

Posting Permissions

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