Crosstab Query - External Criteria

P

Peter Hibbs

Why does this work -

TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblOrders.ShipCountry)="USA"))
GROUP BY Trim([FirstName] & " " & [LastName])
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

and this does not.

TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblOrders.ShipCountry)=[Forms]![frmCrosstab]![cboCountry]))
GROUP BY Trim([FirstName] & " " & [LastName]), tblOrders.ShipCountry
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

The difference is that the first query has the criteria 'built-in' and
the second gets the criteria from a combo box on a form. Of course the
form is open and the names are correct. The error message shown is :-

The Microsoft Jet database Engine does not recognize
'[Forms]![frmCrosstab]![cboCountry]' as a valid name or expression.

How would I open a crosstab query and select the required criteria in
a combo box control on a form?

Peter Hibbs.
 
R

Rick Brandt

Peter said:
Why does this work -

TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblOrders.ShipCountry)="USA"))
GROUP BY Trim([FirstName] & " " & [LastName])
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

and this does not.

TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblOrders.ShipCountry)=[Forms]![frmCrosstab]![cboCountry]))
GROUP BY Trim([FirstName] & " " & [LastName]), tblOrders.ShipCountry
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

The difference is that the first query has the criteria 'built-in' and
the second gets the criteria from a combo box on a form. Of course the
form is open and the names are correct. The error message shown is :-

The Microsoft Jet database Engine does not recognize
'[Forms]![frmCrosstab]![cboCountry]' as a valid name or expression.

How would I open a crosstab query and select the required criteria in
a combo box control on a form?

Peter Hibbs.
Click to expand...

Most Access queries will automatically resolve parameter references with a
"best guess" as to the DataType. Crosstab queries insist that such
references be explicitly defined.

If you look at your query in design view there will be a menu item to open a
"Parameters" dialog. If you enter your form references into that along with
the DataType for each then the Crosstab will work.
 
A

Allen Browne

To use parameters in a crosstab, you need to declare the parameters or
specify the column headings.

In query design view, open the Parameters dialog (Parameters on Query menu),
and enter the 2 parameters.

Alternatively, open the Properties box in query design view, and list the
Column Headings.

Details in:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html#Param
 
P

Peter Hibbs

Allen (and Rick),

It's not quite working yet, can't see what I'm doing wrong.

Here is the query (qryOrders) :-

PARAMETERS [Forms].[frmCrosstab].[cboCountry] Text ( 255 );
TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblCustomers.Country)=[Forms]![frmCrosstab]![cboCountry]))
GROUP BY Trim([FirstName] & " " & [LastName])
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

and the code I am using :-


Dim rst As Recordset
Dim vArray As Variant

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

I get the error :- Too few parameters, expected 1.

What I am trying to do is this. On the form is a combo box
(cboCountry) in which the user can select a country name. In the
AfterUpdate event of the combo I call the VBA code above which should
return data based on the specified country.
The code fails at the 'Set rst = CurrentDb.OpenRecordset("qryOrders")'
line with the error.

Any ideas?

Peter Hibbs.
 
T

Tom Wickerath

Hi Peter,

Check out page 11 of a Word document that I call "Access Links.doc". You can
download a zipped copy from my web site:

http://www.accessmvp.com/TWickerath/

Here is a working sample that involves a crosstab query:

Self Adjusting Crosstab Example
http://www.accessmvp.com/TWickerath/downloads/SelfAdjustingCrosstabExample.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
A

Allen Browne

Peter Hibbs said:
Dim rst As Recordset
Dim vArray As Variant

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

I get the error :- Too few parameters, expected 1.
Click to expand...


You're trying to OpenRecordset() on a parameter query?

Concatenate the values into the query string. You'll find that's easier than
trying to supply the the parameter to the QueryDef in code.
 
P

Peter Hibbs

Hi Tom,

Thanks for the info but -- still does not work. I am thinking now that
this is a limitation of Crosstab queries.

For example, in your form fdlgAskForDatesAndCategory you have :-

DoCmd.OpenQuery "qxtbEmployeeSalesParameter2"

which opens the crosstab query and displays the results based on the
three criteria (two dates and category). What I want to do is open
that same query, with the criteria active, and copy the the data to a
recordset, like so :-

Set rst = CurrentDb.OpenRecordset("qxtbEmployeeSalesParameter2")

When it runs I get the error : Too few parameters, expected 3.

If I go to the query and remove the criteria (and the entries in the
Parameters form), it works fine with no error message, except that it
returns ALL records, of course, which I don't want.

Maybe I will have to copy the SQL into the VBA window and modify the
criteria in code (which is a bit 'messy') unless you have any other
ideas.

Peter Hibbs.

Hi Peter,

Check out page 11 of a Word document that I call "Access Links.doc". You can
download a zipped copy from my web site:

http://www.accessmvp.com/TWickerath/

Here is a working sample that involves a crosstab query:

Self Adjusting Crosstab Example
http://www.accessmvp.com/TWickerath/downloads/SelfAdjustingCrosstabExample.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Peter Hibbs said:
Allen (and Rick),

It's not quite working yet, can't see what I'm doing wrong.

Here is the query (qryOrders) :-

PARAMETERS [Forms].[frmCrosstab].[cboCountry] Text ( 255 );
TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblCustomers.Country)=[Forms]![frmCrosstab]![cboCountry]))
GROUP BY Trim([FirstName] & " " & [LastName])
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

and the code I am using :-


Dim rst As Recordset
Dim vArray As Variant

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

I get the error :- Too few parameters, expected 1.

What I am trying to do is this. On the form is a combo box
(cboCountry) in which the user can select a country name. In the
AfterUpdate event of the combo I call the VBA code above which should
return data based on the specified country.
The code fails at the 'Set rst = CurrentDb.OpenRecordset("qryOrders")'
line with the error.

Any ideas?

Peter Hibbs.
Click to expand...
Click to expand...
 
R

RoyVidar

Peter said:
Hi Tom,

Thanks for the info but -- still does not work. I am thinking now
that this is a limitation of Crosstab queries.

For example, in your form fdlgAskForDatesAndCategory you have :-

DoCmd.OpenQuery "qxtbEmployeeSalesParameter2"

which opens the crosstab query and displays the results based on the
three criteria (two dates and category). What I want to do is open
that same query, with the criteria active, and copy the the data to a
recordset, like so :-

Set rst =
CurrentDb.OpenRecordset("qxtbEmployeeSalesParameter2")

When it runs I get the error : Too few parameters, expected 3.

If I go to the query and remove the criteria (and the entries in the
Parameters form), it works fine with no error message, except that it
returns ALL records, of course, which I don't want.

Maybe I will have to copy the SQL into the VBA window and modify the
criteria in code (which is a bit 'messy') unless you have any other
ideas.

Peter Hibbs.

Hi Peter,

Check out page 11 of a Word document that I call "Access Links.doc".
You can download a zipped copy from my web site:

http://www.accessmvp.com/TWickerath/

Here is a working sample that involves a crosstab query:

Self Adjusting Crosstab Example
http://www.accessmvp.com/TWickerath/downloads/SelfAdjustingCrosstabExample.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Peter Hibbs said:
Allen (and Rick),

It's not quite working yet, can't see what I'm doing wrong.

Here is the query (qryOrders) :-

PARAMETERS [Forms].[frmCrosstab].[cboCountry] Text ( 255 );
TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblCustomers.Country)=[Forms]![frmCrosstab]![cboCountry]))
GROUP BY Trim([FirstName] & " " & [LastName])
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

and the code I am using :-


Dim rst As Recordset
Dim vArray As Variant

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

I get the error :- Too few parameters, expected 1.

What I am trying to do is this. On the form is a combo box
(cboCountry) in which the user can select a country name. In the
AfterUpdate event of the combo I call the VBA code above which
should return data based on the specified country.
The code fails at the 'Set rst =
CurrentDb.OpenRecordset("qryOrders")' line with the error.

Any ideas?

Peter Hibbs.
Click to expand...
Click to expand...
Click to expand...

It is a limitation, not of crosstabs, but of parameterized queries.
DAO knows nothing of Access objects or whatever values they may
contain, so one would need to "resolve" these - meaning providing
the values of the parameters. Simplest, codewise, coulb be something
along the lines of the following (which is by memory, btw, so there
could be errors);

dim pr as dao.parameter
dim qd as dao.querydef
dim db as dao.database

set db = currentdb
set qd = db.querydefs("qxtbEmployeeSalesParameter2")
for each pr in qd.parameters
pr.value = eval(pr.name)
next pr
set rs = qd.openrecordset()
 
P

Peter Hibbs

Thanks guys, as suggested I got it to work by plugging the criteria
into the SQL string and then running that :-

Dim rst As Recordset
Dim vSQL As String, vCriteria As String

vCriteria = cboCountry
vSQL = "TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID " _
& "SELECT Trim([FirstName] & ' ' & [LastName]) AS [Employee Name] " _
& "FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders " _
& "ON tblCustomers.CustomerID = tblOrders.CustomerID) " _
& "ON tblEmployees.EmployeeID = tblOrders.EmployeeID " _
& "WHERE (((tblCustomers.Country) = '" & vCriteria & "')) " _
& "GROUP BY Trim([FirstName] & ' ' & [LastName]) " _
& "ORDER BY Trim([FirstName] & ' ' & [LastName]) " _
& "PIVOT tblCustomers.CustomerID;"

Set rst = CurrentDb.OpenRecordset(vSQL)
.....
.....

Not as easy as using a predefined query but it works OK.

Peter Hibbs.
 
T

Tom Wickerath

Hi Peter,

I see in your later response that you got it to work, and you made the
statement "Not as easy as using a predefined query but it works OK.". My
suggestion was identical to that of Roy Vidar. It should have worked with a
saved crosstab query, if your code evaluated each parameter, ie.

dim pr as dao.parameter
dim qd as dao.querydef

for each pr in qd.parameters
pr.value = eval(pr.name)
next pr


This is how my example works.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


Peter Hibbs said:
Hi Tom,

Thanks for the info but -- still does not work. I am thinking now that
this is a limitation of Crosstab queries.

For example, in your form fdlgAskForDatesAndCategory you have :-

DoCmd.OpenQuery "qxtbEmployeeSalesParameter2"

which opens the crosstab query and displays the results based on the
three criteria (two dates and category). What I want to do is open
that same query, with the criteria active, and copy the the data to a
recordset, like so :-

Set rst = CurrentDb.OpenRecordset("qxtbEmployeeSalesParameter2")

When it runs I get the error : Too few parameters, expected 3.

If I go to the query and remove the criteria (and the entries in the
Parameters form), it works fine with no error message, except that it
returns ALL records, of course, which I don't want.

Maybe I will have to copy the SQL into the VBA window and modify the
criteria in code (which is a bit 'messy') unless you have any other
ideas.

Peter Hibbs.

Hi Peter,

Check out page 11 of a Word document that I call "Access Links.doc". You can
download a zipped copy from my web site:

http://www.accessmvp.com/TWickerath/

Here is a working sample that involves a crosstab query:

Self Adjusting Crosstab Example
http://www.accessmvp.com/TWickerath/downloads/SelfAdjustingCrosstabExample.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Peter Hibbs said:
Allen (and Rick),

It's not quite working yet, can't see what I'm doing wrong.

Here is the query (qryOrders) :-

PARAMETERS [Forms].[frmCrosstab].[cboCountry] Text ( 255 );
TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblCustomers.Country)=[Forms]![frmCrosstab]![cboCountry]))
GROUP BY Trim([FirstName] & " " & [LastName])
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

and the code I am using :-


Dim rst As Recordset
Dim vArray As Variant

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

I get the error :- Too few parameters, expected 1.

What I am trying to do is this. On the form is a combo box
(cboCountry) in which the user can select a country name. In the
AfterUpdate event of the combo I call the VBA code above which should
return data based on the specified country.
The code fails at the 'Set rst = CurrentDb.OpenRecordset("qryOrders")'
line with the error.

Any ideas?

Peter Hibbs.
Click to expand...
Click to expand...
Click to expand...
 
P

Peter Hibbs

OK, thanks Tom. I will have another play with it tomorrow and see if I
can figure it out. It would be useful to have alternative methods
available.

Thanks again to you and Roy.

Peter Hibbs.

Hi Peter,

I see in your later response that you got it to work, and you made the
statement "Not as easy as using a predefined query but it works OK.". My
suggestion was identical to that of Roy Vidar. It should have worked with a
saved crosstab query, if your code evaluated each parameter, ie.

dim pr as dao.parameter
dim qd as dao.querydef

for each pr in qd.parameters
pr.value = eval(pr.name)
next pr


This is how my example works.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


Peter Hibbs said:
Hi Tom,

Thanks for the info but -- still does not work. I am thinking now that
this is a limitation of Crosstab queries.

For example, in your form fdlgAskForDatesAndCategory you have :-

DoCmd.OpenQuery "qxtbEmployeeSalesParameter2"

which opens the crosstab query and displays the results based on the
three criteria (two dates and category). What I want to do is open
that same query, with the criteria active, and copy the the data to a
recordset, like so :-

Set rst = CurrentDb.OpenRecordset("qxtbEmployeeSalesParameter2")

When it runs I get the error : Too few parameters, expected 3.

If I go to the query and remove the criteria (and the entries in the
Parameters form), it works fine with no error message, except that it
returns ALL records, of course, which I don't want.

Maybe I will have to copy the SQL into the VBA window and modify the
criteria in code (which is a bit 'messy') unless you have any other
ideas.

Peter Hibbs.

Hi Peter,

Check out page 11 of a Word document that I call "Access Links.doc". You can
download a zipped copy from my web site:

http://www.accessmvp.com/TWickerath/

Here is a working sample that involves a crosstab query:

Self Adjusting Crosstab Example
http://www.accessmvp.com/TWickerath/downloads/SelfAdjustingCrosstabExample.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Allen (and Rick),

It's not quite working yet, can't see what I'm doing wrong.

Here is the query (qryOrders) :-

PARAMETERS [Forms].[frmCrosstab].[cboCountry] Text ( 255 );
TRANSFORM Count(tblOrders.OrderID) AS CountOfOrderID
SELECT Trim([FirstName] & " " & [LastName]) AS [Employee Name]
FROM tblEmployees INNER JOIN (tblCustomers INNER JOIN tblOrders ON
tblCustomers.CustomerID = tblOrders.CustomerID) ON
tblEmployees.EmployeeID = tblOrders.EmployeeID
WHERE (((tblCustomers.Country)=[Forms]![frmCrosstab]![cboCountry]))
GROUP BY Trim([FirstName] & " " & [LastName])
ORDER BY Trim([FirstName] & " " & [LastName])
PIVOT tblCustomers.CustomerID;

and the code I am using :-


Dim rst As Recordset
Dim vArray As Variant

Set rst = CurrentDb.OpenRecordset("qryOrders")
vArray = rst.GetRows(1000)

I get the error :- Too few parameters, expected 1.

What I am trying to do is this. On the form is a combo box
(cboCountry) in which the user can select a country name. In the
AfterUpdate event of the combo I call the VBA code above which should
return data based on the specified country.
The code fails at the 'Set rst = CurrentDb.OpenRecordset("qryOrders")'
line with the error.

Any ideas?

Peter Hibbs.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract Headers from Crosstab query 2
Totals in Querys 3
Top Values Query combined with a Sum Query 3
Report From Form 2
Filter Options 4
Order Entry type form to add to three tables 11
List box Form Criteria 1
Joining two queries 2

Top