Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated Fields In Report (Access 2000)

    Hello:
    I want to include the results of some calculated fields in a report. I am trying to get the results from a query, but am having trouble. I have seven items which are tax deductible, but they are deductible at different rates. For example, one item's amount is 25% tax deductible and another is 40%.
    I built a query with an expression and a calculated amount using ExpenseItemAmount * (0.25). In the criteria field I entered the appropriate item number that corresponded to the ExpenseCategoryID. It works fine when I enter only one item number, but when I enter all seven items in seven different fields nothing is returned in the calculated fields.
    How can I get the report to show the correct tax deductible amount for all these fields. I can't figure it out. Should I be doing this in a query or should I try to figure out how to do it in a report? Some type of IIF statement in the textbox?
    Thank you,
    Doug

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

    Re: Calculated Fields In Report (Access 2000)

    Does one record contain all seven items, or just one item plus a field that identifies the type (ExpenseCategoryID)?

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Fields In Report (Access 2000)

    Hans:
    Each record contains one of the seven items that is identified from ExpenseCategoryID.
    Doug

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

    Re: Calculated Fields In Report (Access 2000)

    I would create a lookup table with two columns (fields): ExpenseCategoryID (primary key, same type as in the table you already have) and DeductionPercentage (number, single precision, format set to Percent, 0 decimals.) Enter seven records in this table, one for each type.
    Add the lookup table to your query, and join the ExpenseCategoryID fields. You can now create a calculated expression [ExpenseItemAmount]*[DeductionPercentage]

    Note: if the ExpenseCategoryID field is blank in some records of your main table, make the join on this field into an outer join: double click the join line, and specify that you want to display ALL records from the main table.

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Fields In Report (Access 2000)

    Hans:
    I did as you suggested and built the Lookup Table and the query using it. It still will only return a value if there is only one criteria entered. If I enter more than one criteria, one each for the seven deductions, it returns empty fields.
    I don't understand how Access will return one calculated field result when there are more than one criteria entered. It not returning results makes sense to me in that regard.
    Second, how can I get this method to work without having to build seven little queries each with ony one different criteria in it.
    I know there has got to be a way to make this work, but I just can't see it.
    Thank you for your help,
    Doug

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

    Re: Calculated Fields In Report (Access 2000)

    I don't understand how you want to use multiple criteria. Could you try to explain what you want to accomplish?

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Fields In Report (Access 2000)

    Hello, Hans:
    Thanks for your help.
    I think my description in an earlier post wasn't clear. I have about 25 expense categories, seven of which are tax deductions other than 100%. I need to create a report that will show the original amount of the expense and then in another text box show the tax deductible amount. I thought I should perform the calculations in a query and then return the results in a text box. I wanted to perform the calculations in one query and reference the appropriate fields in the report. In order to do this I need to tell the query which category code for each field to use when doing the math.
    The only way I could figure out to make the result correct for each field was to set a criteria for each calculated field. For example, the electric expense is 25% tax deductible so in field I rename it Electric and set the math up. Then in that field's criteria I set it to the table and field where the category code is and then have it equal 16, the electric category. In the next calculated field I do the same for Water and so on. When I only have the one field entered the query returns the proper number. If I have all the categories that are 25% deductible and enter say 16 in only one field they all return the proper number. This is because 16 is a code which is 25% deductible.
    When I enter a code in each field's criteria so some can have 25% and some 40% there is nothing returned. Probably because there is no field that has all the codes 16, 18, 22, etc. in it. If I enter no criteria code number the query returns results in a column of several record fields that while may be correct I can't use in them in the report. How could I reference them? They're all under the Electric field name.
    The field I am performing the math on is ExpenseItemAmount. It contains the dollar amounts. The one field ExpenseCategoryID contains the code number like 16,18, 22. I use this field in the criteria so the calculation will return the proper deduction percentage based on the code.
    My question is: how can I get the proper result from one query when it's necessary to use multiple criteria to obtain the proper result?
    I wonder if I should just start all over with a new database designed differently.
    Thank you,
    Doug

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

    Re: Calculated Fields In Report (Access 2000)

    Could you post a stripped down version of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]That would enable Loungers to look at the problem directly.

  9. #9
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Fields In Report (Access 2000)

    Hans:
    Thank you for the help. I can't get the zipped 97 version below 125K. The way it is built, the db needs four tables, the query and the report so you can look at it. I think I'll try a different design then see what kind of queries I can build with it.
    Thanks again,
    Doug

Posting Permissions

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