Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    12

    Sum multiple columns from tab1 * with value from tab2 query

    Good day All,

    I have been strugling lately to come up with a query that sum multiple columns and return extra 3 rows depending on what was selected.

    For exapmle see my sample data below:

    Town | Sector | Outside| Inside |Available|Price
    Roy----Formal----0----------0---------1------0
    Kobus--Formal----0 ---------0---------1------0
    Wika---Formal----0----------0---------1------0
    Mevo---Formal---1----------1---------1-------0
    Hoch---Formal----1----------1---------1------1
    Points------------2----------2---------5------1
    Score------------10--------10--------10-----10
    %score-----------20--------20--------50-----10


    Each column has a constant weighting (which serves as a factor and it can change depending on the areas) in this case the weighting for this areas are the ones in the first row for the sector Formal:


    Sector |Outside| Inside |Available|Price
    Formal---1----------1 ------1-----1
    Informal--1----------0 ------2-----1

    I tried using the aggregate sum function in but it wont work since i need the factor in the other table. Which is where my challenge started

    To compute the rows below the report
    points = sum per column * weighting factor per column
    Score = sum of no of shops visited (in this case its 5) * weighting factor per column
    % score = points/Score * 100

    The report should display as described above. With the new computed rows below.
    I kindly ask anyone to assist me with this challenge as i tried searching on the internet for solutions but havent come across any.

    Thanks a lot for your support in advance!!

  2. #2
    Join Date
    Mar 2011
    Posts
    12
    Hi All,

    No need to assist with this anymore, i found the solution.

Tags for this Thread

Posting Permissions

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