Almost a Crosstab but not Quite

[John Ortt]

Hi Everyone,

I am doing a stock Audit and we have created a table with the fields [Part
No], [Bin No] and [Qty] to record the quantity of each part number found and
their location. This is producing Data as follows:

Part Bin Qty
X 1 10
X 5 3
X 23 15
Y 4 67
Y 7 10
Z 5 76

I tried producing a crosstab report with the part number on the left hand
side, the Bin numbers listed allong the top and the Quantity in the value
field but there were too many column headers.

If possible I would like to produce the data as follows (using as many
columns as necessary - but I know it is less than 10)

Part BinA QtyA BinB QtyB BinC QtyC

X 1 10 5 3 23 15
Y 4 67 7 10
Z 5 76

Any help will be greatly appreciated.

Thanks in Advance,

John

 

[Michel Walsh]

Hi,


First, rank the stuff


SELECT a.Part, a.Bin, a.Qty, COUNT(*) AS Rank
FROM myTable As a INNER JOIN myTable As b
ON a.Part = b.Part AND a.bin <= b.bin




Say it is query Q1.


Have a table OneTwo with one field, d, two records, one with d=1 and one
with d=2


TRANSFORM LAST(iif(d=1, qty, bin)) As what
SELECT part
FROM q1, onetwo
GROUP BY part
PIVOT iif(d=1, "Qty", "Bin") & Rank


produces the desired columns



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,

To get the name you mentioned, change the PIVOT segment to


PIVOT iif(d=1, "Qty_", "Bin_") & Chr$(64+Rank)



as example.




Vanderghast, Access MVP

 

[John Ortt]

Excellent,

Thankyou Michel, exactly what I wanted.