Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Unanswered: How to divide one by another row in ms access

    i need to divide a number of rows into another row.
    in the following table i would like to divide each gender category by total. there is one way to use union query but i don't want to use that. Do you know any other way.


    Gender Population
    Male 3000
    Female 2500
    Total 5500

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your table is incorrect, it should not store the total, the total should be computed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In addition, your data really looks more like a spreadsheet than a relational database.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by r937 View Post
    your table is incorrect, it should not store the total, the total should be computed

    pretend it is query. Now i want to add another query to calculate the percentage.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by accessworm View Post
    pretend it is query. Now i want to add another query to calculate the percentage.
    oh, okay
    Code:
    SELECT Gender
         , 100.0 * Population / 
           ( SELECT Population
               FROM pretend
              WHERE Gender = 'Total' ) AS Pop_Percent
      FROM pretend
     WHERE Gender <> 'Total'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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