Help with query, possible crosstab

  • Thread starter Thread starter joshblair
  • Start date Start date
J

joshblair

Hi,

I am trying to get a report built with varying number of columns across
the top based on the contents of 5 QTY fields (Qty_1, Qty_2, Qty_3,
Qty_4, Qty_5) but the quantities don't always coincide with one
another. Sometimes the quantities are all the same: (500, 1000, 5000,
10000, 25000), and sometimes they differ: (1000, 5000, 10000, <null>,
<null>)

Here is an axample of what this report might look like for a given part
number (not represented by the sample data below):

500 1000 2500 5000 10000 25000
==== ==== ==== ==== ===== =====
Supplier A $1.00 $ 0.90 $0.85 $0.80 N/A $0.80

Supplier B $1.45 $1.40 $1.35 $1.30 $1.25 N/A

Supplier C N/A $2.15 $2.15 $2.15 $2.15 $2.15

=================================================================

I have some data in a table that contains the following fields:

ID
SupplierID
SupplierName
PartNumber
PartDesciption
Qty_1
Price_1
Qty_2
Price_2
Qty_3
Price_3
Qty_4
Price_4
Qty_5
Price_5

ID SupplierID SupplierName PartNumber PartDesciption
Qty_1 Price_1 Qty_2 Price_2 Qty_3 Price_3 Qty_4 Price_4 Qty_5
Price_5
1 2000 Supplier A 1000 8wt Fly Reel
500 $1.50 1000 $1.40 2500 $1.25 5000 $1.10 10000
$1.00
2 2200 Supplier B 1000 8wt Fly Reel
1500 $1.50 2000 $1.25 2500 $1.10 5000 $1.00 15000
$0.90
3 2300 Supplier C 1000 8wt Fly Reel
500 $1.75 1500 $1.50 2000 $1.25 4500 $1.15 10000
$1.00
4 2000 Supplier A 2000 8wt 4pc Fly Rod
100 $50.50 500 $40.40 1000 $35.25 2000 $30.10 3000
$25.00
5 2200 Supplier B 2000 8wt 4pc Fly Rod
1000 $40.50 2000 $35.25 3000 $30.10 4000 $28.00 5000
$25.90
6 2300 Supplier C 2000 8wt 4pc Fly Rod
500 $45.75 1500 $42.50 2000 $40.25 4500 $38.15 10000
$30.00

Question 1: Is this possible?
Question 2: Would I accomplish this using one or more crosstab queries?
Question 3: What other guidance can you offer?

Thanks in advance for your time and information.
 
Douglas,

I understand, but I inherited this database. Any advise on the current
structure is greatly appreciated.

Thanks for your time,

Josh
 
Assuming you don't need to update the results, you can try normalizing the
data through a Union query:

SELECT ID, SupplierID, SupplierName,
PartNumber, PartDesciption,
1 AS Position,
Qty_1 AS Qty, Price_1 AS Price
FROM MyTable
UNION
SELECT ID, SupplierID, SupplierName,
PartNumber, PartDesciption,
2 AS Position,
Qty_2 AS Qty, Price_2 AS Price
FROM MyTable
UNION
SELECT ID, SupplierID, SupplierName,
PartNumber, PartDesciption,
3 AS Position,
Qty_3 AS Qty, Price_3 AS Price
FROM MyTable
UNION
SELECT ID, SupplierID, SupplierName,
PartNumber, PartDesciption,
4 AS Position,
Qty_4 AS Qty, Price_4 AS Price
FROM MyTable
UNION
SELECT ID, SupplierID, SupplierName,
PartNumber, PartDesciption,
5 AS Position,
Qty_5 AS Qty, Price_5 AS Price
FROM MyTable

You'll then be able to run the appropriate queries against that query,
instead of the original table.
 
You can create a normalizing union query first
SELECT ID, SupplierID, SupplierName, PartNumber, PartDesciption,
Qty_1 As Qty, 1 as Seq, Price_1 as Price
FROM [that]
UNION ALL
SELECT ID, SupplierID, SupplierName, PartNumber, PartDesciption,
Qty_2, 2 , Price_2
FROM [that]
UNION ALL
SELECT ID, SupplierID, SupplierName, PartNumber, PartDesciption,
Qty_3, 3 , Price_3
FROM [that]
UNION ALL
SELECT ID, SupplierID, SupplierName, PartNumber, PartDesciption,
Qty_4, 4 , Price_4
FROM [that]
UNION ALL
SELECT ID, SupplierID, SupplierName, PartNumber, PartDesciption,
Qty_5, 5 , Price_5
FROM [that];

From there, you should be able to create a crosstab where the column heading
is the Qty field and row heading fields are SupplierID, SupplierName,
PartNumber, and PartDesciption.

I expect Average of Price might be your value.
 
And to realize we posted about the same time from two different countries...

You are a very smart person ;-)
 
Douglas and Duane,

You guys both nailed it. That was exactly what I needed to do to
accomplish the desired result. Thanks to both of you.

Here is what that looks like if I run it for PartNumber=1000 (sorry for
the line wrap):

SupplierID SupplierName Total Of Price 500 1000 1500
2000 2500 4500 5000 10000 15000
2000 Supplier A $1.25 $1.50 $1.40
$1.25 $1.10 $1.00
2200 Supplier B $1.15 $1.50
$1.25 $1.10 $1.00 $0.90
2300 Supplier C $1.33 $1.75 $1.50
$1.25 $1.15 $1.00

One more question: How would you create a report that can handle this
varying number of column headers? Would I have to use unbound fields
and/or custom code within the report?

Thanks again,

Josh
 
Back
Top