+ Reply to Thread
Results 1 to 2 of 2

Pivot Table from MS Query to Calculate Orders

  1. #1
    Registered User
    Join Date
    06-06-2013
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Pivot Table from MS Query to Calculate Orders

    Hi, I am trying to calculate the total light fixture order based on the following scenario:
    Residential building has several floor plans (UnitTypes) with each floor plan having several light fixture types with various quantities of each type of fixture. I have added a total cost per fixture. Using MS Query I have linked the tables and produced a pivot tables hoping to generate a light fixture subtotal amount. The problem I am finding is that the count of fixtures is not factored into the calculation but rather the instance of the UnitTypes which extends the total for the fixture type. Should I have set up the table for FixtureCounts differently (ie/ line by line by the number of times the fixture appears or null where non existent in the UnitType?) Please see attached. Thank you for your help in advance.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Pivot Table from MS Query to Calculate Orders

    Hello Mars, Welcome to the forum. I suspect there is something not right with your access query and the reason why I say that is : in Unit Type A as per sheet FixtureCounts only 1 count of AC is required but pivot table sum of count is showing 9.
    Can you please check and let us know.
    Best Regards/VKS

  3. #3
    Registered User
    Join Date
    06-06-2013
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Pivot Table from MS Query to Calculate Orders

    Thank you for trying to help. I created an inner join between the three tables and there are in fact 9 unit A types in the building. The tables are linked in a one to many relationship.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1