Trouble with parameter for crosstab in report

[joshblair]

Hello,

I have a report that uses a crosstab query for it's data. The crosstab
has a single parameter and its value will be coming from a field in a
subform on a form. Here is what the code looks like:

Set qdf = dbsReport.QueryDefs("MyCrossTab")

qdf.Parameters("[Forms]![frmDisplayQuotes]![subfDisplayQuotes].[Form]![Part
No]") = [Forms]![frmDisplayQuotes]![subfDisplayQuotes].[Form]![Part No]

When I try to test the report, it never comes back (runs forever until
I kill it in task manager). If I try to "preview" this report without
initiating it from the main form, it gives me an error:

Run-time error '2450'

RFQ - Access97 can't find the form 'frmDisplayQuotes' referred to in a
module expression or Visial Basic code.

How do I specify the parameter for the crosstab query specified in the
querydef?

Thanks,

Josh Blair

 

[Duane Hookom]

You need to set the parameter data types in your query. If the form is open
and you have a value in the PartNo text box, you shouldn't have to do
anything with the qdf and qdf.Parameters.

I could be missing something but I have never had to create a querydef
object when dealing with crosstab queries.

 

[joshblair]

Duane,

Would you say that I need to specify the crosstab like this:

PARAMETERS [forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part
No] Text;
TRANSFORM
Avg(IIf([tblQUOTE_VALUES].vPrice<>0,[tblQUOTE_VALUES].vPrice)) AS [The
Value]
SELECT tblQUOTECORE.qtRFQDate, tblQUOTECORE.qtQuoteNo, VEID.VEND_NAME
FROM (tblQUOTECORE LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo) LEFT JOIN VEID ON tblQUOTECORE.qtSupplierID
= VEID.VENDOR_ID
WHERE
(((tblQUOTECORE.qtPartNo)=[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part
No]))
GROUP BY tblQUOTECORE.qtRFQDate, tblQUOTECORE.qtQuoteNo, VEID.VEND_NAME
ORDER BY tblQUOTECORE.qtRFQDate DESC
PIVOT tblQUOTE_VALUES.vQty;

I commented out te code in my report's open event handler that set the
querydef parameter.

Here is what that method looks like now:

Dim intX As Integer
Dim qdf As QueryDef
'Dim frm As Form
Set dbsReport = CurrentDb

' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("MyCrossTab")

'Set frm = Forms!frmDisplayQuotes


'qdf.Parameters("[Forms]![frmDisplayQuotes]![subfDisplayQuotes].[Form]![Part
No]") = frm![subfDisplayQuotes].[Form]![Part No]

' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

MsgBox "total num columns: " & CStr(intColumnCount)
End Sub

When I try to open the report from my main form (that has a subform in
it that contains the [Part No] field that I using to specify my
criteria to the crosstab used in my report, it never opens, my Access
mdb just sits there running. I can't activate anything in the Access
app. All I can do is close it from the taskbar or ffrom Task Manager.

Also, if I don't specify the Record Source in the report properties
as"MyCrossTab" then I don't get the proper results. Should this be
necessary?

Maybe I'm going at this all wrong...

Thanks for your help,

Josh Blair

 

[Duane Hookom]

I expect you are using a crosstab report solution that needs to get the
fields from the querydef in order to set values/control sources. I don't
care much for that solution. If this describes your effort, consider the
much more flexible and efficient crosstab report with dynamic column
headings at http://www.invisibleinc.com/divFiles.cfm?divDivID=4. Try the
Crosstab.mdb.

 

[joshblair]

Duane,

I am looking at your crosstab.mdb solution again, but just to clarify,
I was able to get a sample report working based on the Solutions9.mdb
"Create a crosstab with dynamic column headings". The issue I am
having, and I may have not been clear about it is that I can't figure
out how to specify the parameter to my crosstab query properly.

1) If I don't set the report's Record Source property to my crosstab
query's name MyCrossTab, I don't get the results.

2) In my report's Report_Open event handler, I tried a few different
ways of creating my report's RecordSource programmatically. One way I
created a SQL string that held the contents of my crosstab query and
appended the criteria (part number) using string concatenation and an
input box prompting for the part number. The result of this was 2
dialog prompts, one from the input box and one from the reference to
the [forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part No]
field in the crosstab query.

The second method was to set the querydef like this Set qdf =
dbsReport.QueryDefs("MyCrossTab") and the querydef's parameter like
this:

qdf.Parameters("[Forms]![frmDisplayQuotes]![subfDisplayQuotes].[Form]![Part
No]") = [Forms]![frmDisplayQuotes]![subfDisplayQuotes].[Form]![Part
No]. This still brought up 2 prompts, but it works if I test the
report. It, however, doesn't work if I hook it up to my Access
applications main form. When I click the command button that loads the
report, my Access app just hangs. I can't figure out why.

==========================

Anyway, I'm trying to apply your Crosstab.mdb solution to my problem.

I'm trying to figure out how to build the "alias framework". I don't
quite see how my schema fits your solution. Can you help me understand
how I can accomplish this?

Your qappEmpCust query looks like this:

INSERT INTO tblEmpCustAlias ( EmployeeID, CustomerID )
SELECT tblSales.EmployeeID, tblSales.CustomerID
FROM tblSales
WHERE (((tblSales.SaleDate) Between
[Forms]![frmReportSelect]![txtStartDate] And
[Forms]![frmReportSelect]![txtEndDate]))
GROUP BY tblSales.EmployeeID, tblSales.CustomerID;

I don't know what fields should go into my alias table. Your report
has customers as column headers. I need quantities as column headers.
You report is grouped by Employee, I need my report sorted by date.

OBTW, I inherited this Access application/database and I don't have
much say in it's design/structure from a table standpoint.

For reference, here are the table structures:

============
tblQUOTECORE
============
Name Type Size
=============================================
qtQuoteNo Number (Long) 4
qtRFQDate Date/Time 8
qtQuoteDate Date/Time 8
qtSupplierID Text 15
qtRFQTo Text 50
qtRFQFrom Text 50
qtPartNo Text 50
qtRevisionNo Text 3
qtPartDescription Text 50
qtSpecialDescription Text 150
qtQTY1 Number (Long) 4
qtPrice1 Currency 8
qtQTY2 Number (Long) 4
qtPrice2 Currency 8
qtQTY3 Number (Long) 4
qtPrice3 Currency 8
qtQTY4 Number (Long) 4
qtPrice4 Currency 8
qtQTY5 Number (Long) 4
qtPrice5 Currency 8
qtToolingCost Text 50
qtToolingDescription Text 100
qtLeadTime Text 50
qtCompletedToPrint Yes/No 1
qtException_Mark Yes/No 1
qtExceptions Memo -
qtSchedProduction Date/Time 8
qtEnteredBy Text 50
qtEntryDate Date/Time 8
qtAdditionalNotes Memo -
qtQuoteStatus Yes/No 1
qtProjectNo Text 20
qtModificationDate Date/Time 8




===============
tblQUOTE_VALUES
===============
Name Type Size
=====================================
vQuoteNo Number (Long) 4
vQty Number (Double) 8
vPrice Currency 8

tblQUOTE_VALUES gets updated if following fields are populated in
tblQUOTECORE (in the Access application)
qtQTY1 Number (Long) 4
qtPrice1 Currency 8
qtQTY2 Number (Long) 4
qtPrice2 Currency 8
qtQTY3 Number (Long) 4
qtPrice3 Currency 8
qtQTY4 Number (Long) 4
qtPrice4 Currency 8
qtQTY5 Number (Long) 4
qtPrice5 Currency 8


Here is some other information that may help. I have 2 ways of getting
the data to be used for the crosstab but both seem to return the same
results when used in a crosstab:

One way is a nasty set of union queries that massages the data into a
more normalized structure (in my example for part number 4519135, 280
records):

SELECT qtQuoteNo, VENDOR_ID, VEND_NAME,
qtQTY1 As Qty, 1 as Seq, qtPrice1 as Price
FROM (tblQUOTECORE INNER JOIN VEID ON tblQUOTECORE.qtSupplierID =
VEID.VENDOR_ID) LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo =
tblQUOTE_VALUES.vQuoteNo
Where tblQUOTECORE.qtPartNo =
[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part No]
UNION ALL
SELECT qtQuoteNo, VENDOR_ID, VEND_NAME,
qtQTY2, 2 , qtPrice2
FROM (tblQUOTECORE INNER JOIN VEID ON tblQUOTECORE.qtSupplierID =
VEID.VENDOR_ID) LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo =
tblQUOTE_VALUES.vQuoteNo
Where tblQUOTECORE.qtPartNo =
[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part No]
UNION ALL
SELECT qtQuoteNo, VENDOR_ID, VEND_NAME,
qtQTY3, 3 , qtPrice3
FROM (tblQUOTECORE INNER JOIN VEID ON tblQUOTECORE.qtSupplierID =
VEID.VENDOR_ID) LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo =
tblQUOTE_VALUES.vQuoteNo
Where tblQUOTECORE.qtPartNo =
[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part No]
UNION ALL
SELECT qtQuoteNo, VENDOR_ID, VEND_NAME,
qtQTY4, 4 , qtPrice4
FROM (tblQUOTECORE INNER JOIN VEID ON tblQUOTECORE.qtSupplierID =
VEID.VENDOR_ID) LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo =
tblQUOTE_VALUES.vQuoteNo
Where tblQUOTECORE.qtPartNo =
[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part No]
UNION ALL SELECT qtQuoteNo, VENDOR_ID, VEND_NAME,
qtQTY5, 5 , qtPrice5
FROM (tblQUOTECORE INNER JOIN VEID ON tblQUOTECORE.qtSupplierID =
VEID.VENDOR_ID) LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo =
tblQUOTE_VALUES.vQuoteNo
Where tblQUOTECORE.qtPartNo =
[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part No];

The other way is to create a join query but this approach will miss
records for some quotes (in my example, 56 records)

SELECT tblQUOTECORE.qtQuoteNo, tblQUOTECORE.qtRFQDate, VEID.VENDOR_ID,
VEID.VEND_NAME, tblQUOTE_VALUES.vQty, tblQUOTE_VALUES.vPrice
FROM (tblQUOTECORE INNER JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo) INNER JOIN VEID ON
tblQUOTECORE.qtSupplierID = VEID.VENDOR_ID
WHERE
(((tblQUOTECORE.qtPartNo)=[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part
No]))
ORDER BY tblQUOTECORE.qtRFQDate DESC;

Anyway, if you could point me into the right direction to model my
report after your crosstab.mdb solution, I'd really appreciate it.

 

[joshblair]

I forgot to list my current crosstab query for reference:

PARAMETERS [forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part
No] Text;
TRANSFORM
Avg(IIf([tblQUOTE_VALUES].vPrice<>0,[tblQUOTE_VALUES].vPrice)) AS [The
Value]
SELECT tblQUOTECORE.qtRFQDate, tblQUOTECORE.qtQuoteNo, VEID.VEND_NAME
FROM (tblQUOTECORE LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo) LEFT JOIN VEID ON tblQUOTECORE.qtSupplierID
= VEID.VENDOR_ID
WHERE
(((tblQUOTECORE.qtPartNo)=[forms]![frmDisplayQuotes]![subfDisplayQuotes].[form]![Part
No]))
GROUP BY tblQUOTECORE.qtRFQDate, tblQUOTECORE.qtQuoteNo, VEID.VEND_NAME
ORDER BY tblQUOTECORE.qtRFQDate DESC
PIVOT tblQUOTE_VALUES.vQty;

The parameter listed on top may be a problem. Should this be OK?

It references my main Access application form named frmDisplayQuotes
that has a subform named subfDisplayQuotes that has a field named [Part
No]

Should I be able to use this as a parameter to my crosstab query for
use in my report?

Tanks,

Josh Blair

 

[Duane Hookom]

If you are set on attempting to use the method from the solutions mdb I
can't help you. I think that solution is slow and ugly and I can't justify
spending the time to reverse engineer it to answer your question. That's why
I created the more flexible crosstab report.

Hopefully someone else will pick up this thread.

 

[joshblair]

Duane,

I'm not sold on the solutions mdb. I just tried it when I couldn't
figure out how to get there with Crosstab.mdb

I'm asking for help with your solution. Please let me know if you can
help to guide me with applying your solution to my database. I don't
understand how to implement what your qappEmpCust and
UpdateEmpCustAlias framework does with my data.

Can you explain this to me?

Thanks for your time and information,

Josh Blair

 

[Duane Hookom]

Your solution would use the vqty rather than Customer since you want vqty as
your column heading. I'm not sure what specific field or fields you would
want to define your major row heading which would be similar to the Employee
in the example.

If you open the database window, you will find a table ztblExplanation which
might help more.

 

[joshblair]

Duane,

Thanks for the push in the right direction.

I have your solution partially working.

For column headers, you are right, I want QTY across the top.

The deal is, I think that Part Number would be my major row heading
except that I only this report to show data for a single given part.
This means that what I need is a report shaped similar to a single
Employee's sales data. Does that make sense? I have adjusted the
report accordingly. (please let me know if I'm off-base)

Where you solution has date as a "sub" row header, I'd like to have RFQ
Date (qtRFQDate), Quote Number (qtQuoteNo), and Supplier Name
(VEND_NAME)

My append query looks like this:

INSERT INTO tblPartQtyAlias ( qtPartNo, vQty )
SELECT DISTINCTROW tblQUOTECORE.qtPartNo, tblQUOTE_VALUES.vQty
FROM tblQUOTECORE INNER JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo
WHERE qtPartNo =
[Forms]![frmDisplayQuotes]![subfDisplayQuotes].[Form]![Part No]
GROUP BY tblQUOTECORE.qtPartNo, tblQUOTE_VALUES.vQty;

I have the UpdatePartQtyAlias method working in my modCreateColumnAlias
module.

I have the subreport (srptQtyHeadings) and report
(rptItemCostByPartQty) working pretty well.

What I struggling with is the actual crosstab query that mimics your
qxtbEmpCustSales query. Mine is named qxtbPartQtyCost and looks like
this but the query isn't right. I get all kinds of duplicate data and
report show this erroneous data.

TRANSFORM
Avg(IIf([tblQUOTE_VALUES].vPrice<>0,[tblQUOTE_VALUES].vPrice)) AS [The
Value]
SELECT tblQUOTECORE.qtQuoteNo, tblPartQtyAlias.Level,
tblQUOTECORE.qtRFQDate, VEID.VEND_NAME
FROM ((tblQUOTECORE INNER JOIN tblQUOTE_VALUES ON
tblQUOTECORE.qtQuoteNo = tblQUOTE_VALUES.vQuoteNo) INNER JOIN
tblPartQtyAlias ON tblQUOTECORE.qtPartNo = tblPartQtyAlias.qtPartNo)
INNER JOIN VEID ON tblQUOTECORE.qtSupplierID = VEID.VENDOR_ID
GROUP BY tblQUOTECORE.qtRFQDate, tblPartQtyAlias.Level,
tblQUOTECORE.qtQuoteNo, VEID.VEND_NAME
ORDER BY tblQUOTECORE.qtRFQDate DESC
PIVOT tblPartQtyAlias.ColumnAlias In ("A","B","C","D","E","F","G","H");

Do you see anything offhand that looks wrong with my crosstab? I tried
adjusting the joins but I could find the right combination.

I have another question: What defines the spacing between the
"Customer" columns in the subreport? I could stand to shrink this up
and display more columns.

Thanks again for your time and assistance,

Josh Blair

 

[Duane Hookom]

I think you need two join lines from tblPartQtyAlias. One to Part and the
other to vQty.

The spacing in the columns subreport is set by the Columns properties of the
subreport.

 

[joshblair]

Duane, that was it. I needed an additional join. Your dynamic
crosstab solution works as advertised. I appreciate the time and
information. Thanks for your help with my crosstab report, your
contributions to this news group and the other news groups.

Josh Blair

 

[Duane Hookom]

Glad to hear you got this working. Getting your head (or even my head)
around the solution isn't real obvious.