Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping & showing totals on each in a query (XP/SP2)

    I'm trying to create a query for fixed assets by department. I want to group on Department. I want each department to show in the query. Each asset has an asset type...building, land, vehicles, equipment. I want to show cost totals for each asset type by department. If a department doesn't have an asset type for say building then I want it to show up with a zero. I believe this involves making use of 2 queries one that does the grouping and the other that does the calculating of totals but I can't seem to get it working correctly. I'm hoping someone here can show me the way... <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    Thanks to all for any input.
    Don

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

    Re: Grouping & showing totals on each in a query (XP/SP2)

    Create a standard totals query that groups by Department and Asset Type and totals on Cost. This query will omit Asset Types (and Departments) that have no data.

    Next, create a query that returns all possible combinations of Department and Asset Type. If you have tables that list Departments and Asset Types, just add both tables to the query without joining them. Add the fields you need to the query grid, make sure to include the identifying fields.

    Thirdly, create a query based on the first two. Join them on the fields identifying Departments and Asset Types, i.e. there should be two joining lines between the queries. Change both to outer joins to display all records from the second query. Instead of just SumOfCost, add CCur(Nz([SumOfCost],0)) to the query grid. This will make the query return 0 for Asset Types without data.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping & showing totals on each in a query (XP/SP2)

    Exactly the information that I needed but how do I now create a column for each asset type ( a total of 4).
    So the first column is a list of all departments, column 2 is a list of building totals, column 3 is list of land totals and so on...

    Thank you very much for your assistance.
    Don

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

    Re: Grouping & showing totals on each in a query (XP/SP2)

    That is called a Crosstab query. Note: I am using a foreign-language version of Access, terms in the following may be slightly off.
    <UL><LI>Activate the Queries "tab" of the database window.
    <LI>Click "New".
    <LI>Select "Crosstab Query Wizard".
    <LI>Click "OK".
    <LI>The Crosstab Query Wizard starts. Select the table or query with the "raw" data (not the query with the totals).
    <LI>Click "Next".
    <LI>Select "Department" as row heading.
    <LI>Click "Next".
    <LI>Select "Asset Type" as column heading.
    <LI>Click "Next".
    <LI>Select "Cost" as field for calculations, and select "Sum" as function.
    <LI>Click "Next".
    <LI>Specify a name for the new query.
    <LI>Click "OK".[/list]You will notice that there are many empty "cells" in the result. If you want to see zeros:
    <UL><LI>Switch to design view.
    <LI>Locate the column with "Value" in the Crosstab row.
    <LI>Change the field to CCur(Nz(Sum([Cost]),0)) and the Totals option to "Expression".
    <LI>Switch back to datasheet view.[/list]Phew!

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping & showing totals on each in a query (XP/SP2)

    Perfect! Thanks Hans!

Posting Permissions

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