Almost a Crosstab but not Quite

  • Thread starter Thread starter John Ortt
  • Start date Start date
J

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
 
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
 
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
 
Back
Top