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.


"joshblair" wrote:

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