Crosstab Query - External Criteria

  • Thread starter Thread starter Peter Hibbs
  • Start date Start date
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.
 
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.
 
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
 
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.
 
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:
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.
 
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...
 
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()
 
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.
 
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...
 
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...
 
Back
Top