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.