Help with query, possible crosstab

[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 J. Steele]

Your task would be MUCH simpler if you normalized your database. Rather than
5 separate fields in a single row, your quantities should be 5 separate
rows.

Jeff Conrad has a lot of good links dealing with normalization at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[joshblair]

Douglas,

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

Thanks for your time,

Josh

 

[Douglas J. Steele]

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.

 

[Duane Hookom]

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.

 

[Duane Hookom]

And to realize we posted about the same time from two different countries...

You are a very smart person ;-)

 

[joshblair]

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

 

[Duane Hookom]

I don't like the "Solutions.mdb" solution for dynamic crosstab queries. I
use the Crosstab.mdb in the download from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

 

[joshblair]

Thanks Duane. I'm working on it.