Tip The Table

[Guest]

I need help with a query to tip a table on a form. I have a table organized
like the first table below (tblFruitDate) with the Date field as the unique
Key. It also has the fields named Fruit, Qty and Lbs. It shows the fruit used
each day for the last few years. I want it to also be displayed on a form
that uses either a subform or bound/unbound fields to be laid out like the
bottom example. It would only show the top 7 dates (most recent).

I don’t know if this can be done as a query and create a table that would be
like the bottom (that would be preferable as I’d like to later use it for a
chart). It would also make a lot less expression./function work. Essentially,
it is like the transpose feature of Excell (although I don’t want to use
Excell, grrrr!). This is to mimic a paper form I wish to replace with a
simple Access program. I can do queries based on how may apple there, how
many Lbs per week, etc. Can anyone give me direction?

tblFruitDate
Date* Fruit Qty Lbs
30 jan 06 apple 1 0.25
31 jan 06 orange 2 0.50
1 feb 06 banana 3 0.33
2 feb 06 grapes 25 0.25
3 feb 06 apple 1 0.25

I want it to look like this in a form or new table

Date 30 jan 06 31 jan 06 1 feb 06 2 feb 06 3 feb 06
Qty 1 2 3 25 1
Fruit apple orange banana grapes apple
Lbs 0.25 0.50 0.33 0.25 0.25

Thanks

 

[Michel Walsh]

Hi,


Untested, but it seems you need multiple crosstab with a UNION


TRANSFORM CStr(SUM(qty))
SELECT "Qty" As Date
FROM mytable
GROUP BY null
PIVOT [date]

UNION

TRANSFORM LAST(fruit)
SELECT "Fruit"
FROM mytable
GROUP BY null
PIVOT [date]

UNION

TRANSFORM CStr(SUM(lbs))
SELECT "lbs"
FROM mytable
GROUP BY null
PIVOT [date]



Each of the crosstab produces a single row, and the UNION merge those
(vertically).


Hoping it may help,
Vanderghast, Access MVP