Crosstab query with criteria, dynamic columns and crosstab report question

[joshblair]

Hello,

I have the following crosstab query that works with hard-coded
criteria:

TRANSFORM Avg(IIF([tblQUOTE_VALUES].vPrice <> 0,
[tblQUOTE_VALUES].vPrice)) AS [The Value]
SELECT tblQUOTECORE.qtQuoteNo, tblQUOTECORE.qtSupplierID,
VEID.VEND_NAME, tblQUOTECORE.qtRFQDate
FROM (tblQUOTECORE LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo) LEFT JOIN VEID ON tblQUOTECORE.qtSupplierID
= VEID.VENDOR_ID
WHERE (((tblQUOTECORE.qtPartNo)="PARTXYZ"))
GROUP BY tblQUOTECORE.qtRFQDate, [tblQUOTECORE].qtQuoteNo,
[tblQUOTECORE].qtSupplierID, [VEID].VEND_NAME
ORDER BY tblQUOTECORE.qtRFQDate DESC
PIVOT [tblQUOTE_VALUES].vQty;

I am unable to figure out how to add a dynamic parameter to the
criteria clause so the qtPartNo field is not hard coded to "PARTXYZ".

Also, I am trying to build a report that will accommodate this crosstab
query but one problem I have is that the number of columns and the
names of those columns are not known until the query returns the
results.

Here is an example of the query results when run with the hard coded
criteria qtPartNo = "PARTXYZ" (sorry for the line wrap)

qtQuoteNo qtSupplierID VEND_NAME qtRFQDate 5000
10000 25000 50000 60000 100000 250000
14777 CAM100 Vendor1 10/28/2005
$2.08 $2.00
14778 BAS100 Vendor2 10/28/2005

14387 DUP100 Vendor3 7/16/2005
$3.50
12092 SPP101 Vendor4 3/23/2004 $4.36 $4.21

11507 CAM100 Vendor1 1/13/2004 $2.15
$1.90 $1.66 $1.50
11508 DUP100 Vendor3 1/13/2004 $2.66 $2.32
$2.28 $2.03
11420 DUP100 Vendor3 11/26/2003

11421 CAM100 Vendor1 11/26/2003 $2.95 $2.55
$2.30 $2.10 $1.98
6352 BAR100 Vendor5 9/1/2000

6353 CAM100 Vendor1 9/1/2000 $3.60 $2.55
$1.79 $1.53
6354 DUA100 Vendor6 9/1/2000

6355 MIC100 Vendor7 9/1/2000

6357 mpn101 Vendor8 9/1/2000


If I were to run this query for a different part number, the column
count and column heading names would differ. For example, if I run
this query for qtPartNo = "PARTABC", I might get the following column
headers:

qtRFQDate qtQuoteNo qtSupplierID VEND_NAME 1000 2500 5000 10000

Also, if I populate a datagrid with results of this query using OleDb
in .NET, I can add an OleDb parameter and pass this query the parameter
and get dynamic results based on the qtPartNo criteria.
Unfortunately, my solution requires an Access report, not a ASP.NET Web
form.

Any advice is greatly appreciated,

Thanks in advance,

Josh Blair

 

[Jerry Whittle]

First create a query that returns the records that you want. This query could
have a parameter in it in the criteria under qtPartNo. Next create the
crosstab based on this query instead of the tables directly. In a nutshell a
query based on a query.

For a report you need to know the column headings or it will bomb out. There
are two ways of doing this. The first is to prepopulate the Column Headings
property of the crosstab query. That way the text boxes on the reports will
always know where to find the data. It has other benefits such as putting the
columns in a order that you like (such as months in chronological order
instead of April, August, etc.). It will also hold open a field such as for a
month that had no data or exclude a month if you don't want to see June for
example.

There is code out there to dynamically change a report to match the results
of a crosstab. Google for Access Crosstab Reports.


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

I have the following crosstab query that works with hard-coded
criteria:

TRANSFORM Avg(IIF([tblQUOTE_VALUES].vPrice <> 0,
[tblQUOTE_VALUES].vPrice)) AS [The Value]
SELECT tblQUOTECORE.qtQuoteNo, tblQUOTECORE.qtSupplierID,
VEID.VEND_NAME, tblQUOTECORE.qtRFQDate
FROM (tblQUOTECORE LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo) LEFT JOIN VEID ON tblQUOTECORE.qtSupplierID
= VEID.VENDOR_ID
WHERE (((tblQUOTECORE.qtPartNo)="PARTXYZ"))
GROUP BY tblQUOTECORE.qtRFQDate, [tblQUOTECORE].qtQuoteNo,
[tblQUOTECORE].qtSupplierID, [VEID].VEND_NAME
ORDER BY tblQUOTECORE.qtRFQDate DESC
PIVOT [tblQUOTE_VALUES].vQty;

I am unable to figure out how to add a dynamic parameter to the
criteria clause so the qtPartNo field is not hard coded to "PARTXYZ".

Also, I am trying to build a report that will accommodate this crosstab
query but one problem I have is that the number of columns and the
names of those columns are not known until the query returns the
results.

Here is an example of the query results when run with the hard coded
criteria qtPartNo = "PARTXYZ" (sorry for the line wrap)

qtQuoteNo qtSupplierID VEND_NAME qtRFQDate 5000
10000 25000 50000 60000 100000 250000
14777 CAM100 Vendor1 10/28/2005
$2.08 $2.00
14778 BAS100 Vendor2 10/28/2005

14387 DUP100 Vendor3 7/16/2005
$3.50
12092 SPP101 Vendor4 3/23/2004 $4.36 $4.21

11507 CAM100 Vendor1 1/13/2004 $2.15
$1.90 $1.66 $1.50
11508 DUP100 Vendor3 1/13/2004 $2.66 $2.32
$2.28 $2.03
11420 DUP100 Vendor3 11/26/2003

11421 CAM100 Vendor1 11/26/2003 $2.95 $2.55
$2.30 $2.10 $1.98
6352 BAR100 Vendor5 9/1/2000

6353 CAM100 Vendor1 9/1/2000 $3.60 $2.55
$1.79 $1.53
6354 DUA100 Vendor6 9/1/2000

6355 MIC100 Vendor7 9/1/2000

6357 mpn101 Vendor8 9/1/2000


If I were to run this query for a different part number, the column
count and column heading names would differ. For example, if I run
this query for qtPartNo = "PARTABC", I might get the following column
headers:

qtRFQDate qtQuoteNo qtSupplierID VEND_NAME 1000 2500 5000 10000

Also, if I populate a datagrid with results of this query using OleDb
in .NET, I can add an OleDb parameter and pass this query the parameter
and get dynamic results based on the qtPartNo criteria.
Unfortunately, my solution requires an Access report, not a ASP.NET Web
form.

Any advice is greatly appreciated,

Thanks in advance,

Josh Blair

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

If you are doing this directly in the SQL window
PARAMETERS [Please specify Part number] Text;
TRANSFORM Avg ...
WHERE (((tblQUOTECORE.qtPartNo)= [Please specify Part number] ))
....

Hello,

I have the following crosstab query that works with hard-coded
criteria:

TRANSFORM Avg(IIF([tblQUOTE_VALUES].vPrice <> 0,
[tblQUOTE_VALUES].vPrice)) AS [The Value]
SELECT tblQUOTECORE.qtQuoteNo, tblQUOTECORE.qtSupplierID,
VEID.VEND_NAME, tblQUOTECORE.qtRFQDate
FROM (tblQUOTECORE LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo) LEFT JOIN VEID ON tblQUOTECORE.qtSupplierID
= VEID.VENDOR_ID
WHERE (((tblQUOTECORE.qtPartNo)="PARTXYZ"))
GROUP BY tblQUOTECORE.qtRFQDate, [tblQUOTECORE].qtQuoteNo,
[tblQUOTECORE].qtSupplierID, [VEID].VEND_NAME
ORDER BY tblQUOTECORE.qtRFQDate DESC
PIVOT [tblQUOTE_VALUES].vQty;

I am unable to figure out how to add a dynamic parameter to the
criteria clause so the qtPartNo field is not hard coded to "PARTXYZ".

Also, I am trying to build a report that will accommodate this crosstab
query but one problem I have is that the number of columns and the
names of those columns are not known until the query returns the
results.

Here is an example of the query results when run with the hard coded
criteria qtPartNo = "PARTXYZ" (sorry for the line wrap)

qtQuoteNo qtSupplierID VEND_NAME qtRFQDate 5000
10000 25000 50000 60000 100000 250000
14777 CAM100 Vendor1 10/28/2005
$2.08 $2.00
14778 BAS100 Vendor2 10/28/2005

14387 DUP100 Vendor3 7/16/2005
$3.50
12092 SPP101 Vendor4 3/23/2004 $4.36 $4.21

11507 CAM100 Vendor1 1/13/2004 $2.15
$1.90 $1.66 $1.50
11508 DUP100 Vendor3 1/13/2004 $2.66 $2.32
$2.28 $2.03
11420 DUP100 Vendor3 11/26/2003

11421 CAM100 Vendor1 11/26/2003 $2.95 $2.55
$2.30 $2.10 $1.98
6352 BAR100 Vendor5 9/1/2000

6353 CAM100 Vendor1 9/1/2000 $3.60 $2.55
$1.79 $1.53
6354 DUA100 Vendor6 9/1/2000

6355 MIC100 Vendor7 9/1/2000

6357 mpn101 Vendor8 9/1/2000


If I were to run this query for a different part number, the column
count and column heading names would differ. For example, if I run
this query for qtPartNo = "PARTABC", I might get the following column
headers:

qtRFQDate qtQuoteNo qtSupplierID VEND_NAME 1000 2500 5000 10000

Also, if I populate a datagrid with results of this query using OleDb
in .NET, I can add an OleDb parameter and pass this query the parameter
and get dynamic results based on the qtPartNo criteria.
Unfortunately, my solution requires an Access report, not a ASP.NET Web
form.

Any advice is greatly appreciated,

Thanks in advance,

Josh Blair

 

[joshblair]

John,

Thanks for the reply. You solved that parameter issue. It was really
puzzling me.

Josh Blair

 

[joshblair]

Jerry,

I appreciate the reply. I'm still fuzzy on your solution. You said to
prepopulate the Column Headings property of the crosstab query but I
don't know this information ahead of time. Maybe I am missing
something, but can you eloborate a bit?

I have been looking for examples but the ones that I have found
require that you know the column ahead of time.

Thanks,

Josh Blair

 

[Jerry Whittle]

If you don't know what the column heading are going to be, you probably need
to find some code to dynamically create the report based on the crosstab.