If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Almost a Crosstab but not Quite
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 |
#2
|
|||
|
|||
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 "John Ortt" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Excellent,
Thankyou Michel, exactly what I wanted. "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mutiple Field Crosstab | Kevin Labore | Running & Setting Up Queries | 3 | February 23rd, 2005 10:18 PM |
Crosstab & Query by Form | Terry | Running & Setting Up Queries | 1 | February 10th, 2005 02:21 PM |
Crosstab query help please | K. L. Collins | Running & Setting Up Queries | 0 | January 21st, 2005 08:11 PM |
pivot table sort on crosstab | Johnny C. | Running & Setting Up Queries | 1 | August 11th, 2004 01:45 PM |