locked
Access crosstab query? RRS feed

  • Question

  • I try to create a table (rows called NUMBER CODE should be integer numbers from 1 to 100 and columns are total of COST) with crosstab query and use 2 tables for that. First is a standart table of NUMBER CODE including 100 rows (1, 2, 3, ......, 100). The other is a source table having NUMBER CODE and COST Value together. I have created a join between the source table and NUMBER COST table. The source table may not include any value of NUMBER CODE, for example 45, but I would like to see the 45. row in the last table although there is no any information in the source Table for 45.

    In my set up, if there is no any cost information for 45 there will no 45. row in the final list. I want to see the 45. row as empty.

    How can I create this in access?

    Thanks in advance.

    • Moved by VMazur Wednesday, September 16, 2009 10:30 AM (From:ADO.NET Data Providers)
    Friday, September 11, 2009 2:40 PM

Answers

  • You need a table (or query on two tables) with 3 columns for a cross-tab query.
    mysorian
    • Marked as answer by Erdem ISBILEN Monday, December 28, 2009 8:25 PM
    Sunday, September 13, 2009 3:34 AM
  • Hi Erdem,

     

    Could you provide a clear definition of your tables?

    For example,

    tblUser:

    userId int

    username varchar

     

    Then give a sample result you want. It would be favorable if you provide the join command you already have.

    I think your problem is mainly related to SQL command skill. Something like, left join or right join. And at last do a select form the your result by the common crosstab query pattern. For example:

    http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

     

     

    Please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

    • Marked as answer by Erdem ISBILEN Monday, December 28, 2009 8:26 PM
    Tuesday, September 15, 2009 4:56 AM

All replies

  • You need a table (or query on two tables) with 3 columns for a cross-tab query.
    mysorian
    • Marked as answer by Erdem ISBILEN Monday, December 28, 2009 8:25 PM
    Sunday, September 13, 2009 3:34 AM
  • Hi Erdem,

     

    Could you provide a clear definition of your tables?

    For example,

    tblUser:

    userId int

    username varchar

     

    Then give a sample result you want. It would be favorable if you provide the join command you already have.

    I think your problem is mainly related to SQL command skill. Something like, left join or right join. And at last do a select form the your result by the common crosstab query pattern. For example:

    http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

     

     

    Please update the thread and we will have a further discussion.

     

     

    Best Regards

    Yichun Feng

    • Marked as answer by Erdem ISBILEN Monday, December 28, 2009 8:26 PM
    Tuesday, September 15, 2009 4:56 AM
  • thank you, the problem is resolved.
    Monday, December 28, 2009 8:27 PM