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
|
|||
|
|||
Crosstab query with criteria, dynamic columns and crosstab report question
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 |
#2
|
|||
|
|||
Crosstab query with criteria, dynamic columns and crosstab report
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 |
#3
|
|||
|
|||
Crosstab query with criteria, dynamic columns and crosstab report question
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] )) .... "joshblair" wrote in message ups.com... 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 |
#4
|
|||
|
|||
Crosstab query with criteria, dynamic columns and crosstab report question
John,
Thanks for the reply. You solved that parameter issue. It was really puzzling me. Josh Blair |
#5
|
|||
|
|||
Crosstab query with criteria, dynamic columns and crosstab report
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 |
#6
|
|||
|
|||
Crosstab query with criteria, dynamic columns and crosstab rep
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. -- Jerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "joshblair" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab report repeats last record for all values | Jason Kearns | Setting Up & Running Reports | 10 | December 22nd, 2005 08:02 AM |
dynamic crosstab report - updated to ADO, works, but needs tuning, | SAm | General Discussion | 0 | September 22nd, 2005 03:57 PM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
?create report from crosstab query without set number of columns | dv42 | Setting Up & Running Reports | 1 | May 11th, 2005 07:44 PM |
Dynamic crosstab query report | Richardson | Setting Up & Running Reports | 1 | August 31st, 2004 10:13 PM |