A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Crosstab query with criteria, dynamic columns and crosstab report question



 
 
Thread Tools Display Modes
  #1  
Old January 13th, 2006, 08:18 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old January 13th, 2006, 08:54 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old January 13th, 2006, 09:02 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old January 13th, 2006, 10:01 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old January 13th, 2006, 10:23 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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  
Old January 13th, 2006, 10:34 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 02:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.