Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: Display multiple rows into one row result

    I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.

    Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.

    I am currently using Access 2010.

    Any help please?

    Thanks

    Kevin
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT WH.SalesPerson, WH.Description, WH.Percentage as WH, RET.Percentage as RET, CONS.Percentage as CONS, WHLEss.Percentage as WHLESS
    FROM ((mytable AS WH LEFT JOIN MyTable AS RET ON WH.SalesPerson = RET.SalesPerson) LEFT JOIN mytable AS CONS ON WH.SalesPerson = CONS.SalesPerson) LEFT JOIN mytable AS WHLESS ON WH.SalesPerson = WHLESS.SalesPerson
    WHERE (((CONS.PriceLevel)="CONS") AND ((WHLESS.PriceLevel)="WH/LESS") AND ((WH.PriceLevel)="WH") AND ((RET.PriceLevel)="RET"));
    you will need to adjust the table name (MyTable) and repalce it with whatever your table is called
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Posts
    6
    Hi and thanks for your reply however I am having some issues to implement your code.

    I have created a specific database for this purpose and created a query by copying your exact code, however still this is not returning any result.

    Attached is an access database having just one table and your query based on your suggestion.

    thanks for your help - kevin
    ----------------------------------------------------------------------
    Quote Originally Posted by healdem View Post
    Code:
    SELECT WH.SalesPerson, WH.Description, WH.Percentage as WH, RET.Percentage as RET, CONS.Percentage as CONS, WHLEss.Percentage as WHLESS
    FROM ((mytable AS WH LEFT JOIN MyTable AS RET ON WH.SalesPerson = RET.SalesPerson) LEFT JOIN mytable AS CONS ON WH.SalesPerson = CONS.SalesPerson) LEFT JOIN mytable AS WHLESS ON WH.SalesPerson = WHLESS.SalesPerson
    WHERE (((CONS.PriceLevel)="CONS") AND ((WHLESS.PriceLevel)="WH/LESS") AND ((WH.PriceLevel)="WH") AND ((RET.PriceLevel)="RET"));
    you will need to adjust the table name (MyTable) and repalce it with whatever your table is called
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you adjust the table name (MyTable) to be the same as your real table
    likewise did you adjust the column names to be the same as your real column names

    SELECT WH.SalesPerson, WH.Description, WH.Percentage as WH, RET.Percentage as RET, CONS.Percentage as CONS, WHLEss.Percentage as WHLESS
    FROM ((mytable AS WH LEFT JOIN MyTable AS RET ON WH.SalesPerson = RET.SalesPerson) LEFT JOIN mytable AS CONS ON WH.SalesPerson = CONS.SalesPerson) LEFT JOIN mytable AS WHLESS ON WH.SalesPerson = WHLESS.SalesPerson
    WHERE (((CONS.PriceLevel)="CONS") AND ((WHLESS.PriceLevel)="WH/LESS") AND ((WH.PriceLevel)="WH") AND ((RET.PriceLevel)="RET"));

    make certain the items in red match the actual names in your db
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Posts
    6
    Hi thanks for your reply. It worked well against my original table and got the desired results. thanks for your help.

Posting Permissions

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