Results 1 to 8 of 8
  1. #1
    Lady_Jane is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2010
    Location
    Canada
    Posts
    49

    Question Crosstab Query-including rows not part of selection

    Hi Everyone,
    Forgive me for the long-winded explanation.

    Let's say I have some data from fruit baskets. Each record in my main table will contain a Basket ID, a type of fruit, and the number of that fruit in the basket. Example:
    Basket ID --- Fruit --- Count --- Table
    1------------Pear-------3--------A
    2------------Apple------4--------A
    2-----------Orange-----1--------A

    I want to do a crosstab query so that I will have BasketIDs along the top, fruit types in the first column (row headers) and the count of each fruit as the value. Example:
    Fruit --- Basket1 --- Basket2
    Pear ------3----------0
    Apple-----0----------4
    Orange----0-----------1

    The problem is there are more baskets than available columns in Access. So I need to further divide the data into tables. So essentially baskets 1 through 254 are in Table A, and baskets 255 through 508 are in Table B, hence the Table column above.
    I have created a crosstab query that selects all rows where Table = "A" and creates the desired table. This means that the fruit list is different for each of my new tables. I would like to keep the fruit list the same for all tables.
    So, for example, if none of the baskets in Table A contain pears, I would still like pears to show up under the fruit column.



    Thank you all for your help.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you can put fruit as column heading and basket as row heading.

  3. #3
    Lady_Jane is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2010
    Location
    Canada
    Posts
    49
    Quote Originally Posted by weekend00 View Post
    you can put fruit as column heading and basket as row heading.
    Thanks for your reply.
    I can't do that either because I have more than 255 types of fruit. If I try and divide the tables up according to fruit types then I will end up having data from the same basket split into different tables which I can't do.

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I didn't realize you had so many kinds of fruits as all I know there are less than 100 kinds of fruits.

    In this case, we need to think another way.

    first thing, we make some fake records to let every basket has all kind of fruits.

    your table name: fruitBasket
    Query: MakeUp
    SELECT a.BasketID, b.Fruit, a.Table
    FROM FruitBasket AS a, FruitBasket AS b
    GROUP BY a.BasketID, b.Fruit, a.Table;

    Query: MakeUp2
    SELECT MakeUp.BasketID, MakeUp.Table, MakeUp.Fruit, FruitBasket.Ccount
    FROM FruitBasket RIGHT JOIN MakeUp ON (FruitBasket.BasketID = MakeUp.BasketID) AND (FruitBasket.Fruit = MakeUp.Fruit);

    Query: PIVOT_A

    TRANSFORM Sum(MakeUp2.Ccount) AS SumOfCcount
    SELECT MakeUp2.Fruit
    FROM MakeUp2
    WHERE (((MakeUp2.Table)="A"))
    GROUP BY MakeUp2.Fruit
    PIVOT MakeUp2.BasketID;


    Query: PIVOT_B

    TRANSFORM Sum(MakeUp2.Ccount) AS SumOfCcount
    SELECT MakeUp2.Fruit
    FROM MakeUp2
    WHERE (((MakeUp2.Table)="B"))
    GROUP BY MakeUp2.Fruit
    PIVOT MakeUp2.BasketID;

  5. #5
    Lady_Jane is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2010
    Location
    Canada
    Posts
    49
    Haha, yes
    We're not actually talking about fruit you see, we're talking about benthos taxonomy, but I find it easier to think in terms of apples and oranges than Calopterygidae and Arhynchobdellida.

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Please See my updates.

  7. #7
    Lady_Jane is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2010
    Location
    Canada
    Posts
    49

    Question

    Thanks a lot for you help,
    I don't fully understand what your queries are supposed to do though. In the first, you have FruitBasket AS a and FruitBasket AS b, is this right? Are the "fake records" supposed to have counts of zero?

    Thanks

  8. #8
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Are the "fake records" supposed to have counts of zero?
    in my query, the counts are nulls(blanks). if you want to show 0, some extra work is required.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query A part description /name
    By wvswede in forum Queries
    Replies: 5
    Last Post: 04-07-2010, 06:07 AM
  2. Including two queries in one report
    By kulanga in forum Reports
    Replies: 1
    Last Post: 03-23-2010, 10:21 PM
  3. Query including Null relationship?
    By David Criniti in forum Database Design
    Replies: 0
    Last Post: 08-14-2009, 09:10 PM
  4. Replies: 0
    Last Post: 11-16-2008, 09:46 PM
  5. Replies: 2
    Last Post: 12-09-2005, 01:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums