Crosstab Filter Form

[bymarce]

I have made several filter forms based Allen Browne's example. Thanks
Allen!! I would like to make a filter form that filters the rows and headers
for a crosstab query and displays the sorted records in the bottom of the
form. I don't want my users to need to learn how to make a crosstab query.
They won't use it if they have to learn too much. I have a data table which
contains MLO (cataloge number, text field), Property (ie boiling pont),
TestMethod, NResults (number field), TResults (text field), and units. I
want MLO to be the row heading. I used a query to make the column headings
based on abreviations for property and test method since you can only have
one column heading field in a crosstab. There are also proptery, test
method, and units tables that are used as look up tables. The property and
test method tables are joined through a junction table (many to many).
Conditions of the tests are contained in a conditions table that is related
to the data table through a junction table (many to many). My form has
multiselect list filter boxes for MLO, Property, Method, Tresults and
Nresults. Is this possible to do this on one form or do I need to make the
form open a query based on search criteria? Thanks.
Marcie

 

[Allen Browne]

Marcie, what you are seeking to do is possible, but not simple. It will
require quite a bit of coding. Typically, I use a report rather than a form
for this (since resutls are read-only anyway and it prints better.)

In essence, you decide on the maximum number of columns your form will cope
with, and place an unbound text box on the form for each one. Name them
txt1, txt2, txt3, etc. (so you can refer to them in a loop.) And save the
form without any RecordSource.

In its Open event, you build up a string that is the SQL statement that
gives you a record for each column heading (i.e. each combination of
[Property] + [TestMethod] that maches the criteria for this run.)
OpenRecordset. Loop through the records, assigning the ControlSource of each
text box to the right 'field' that will be generated this run. If you have
too many, give a warning that your form doesn't cope; if you have more than
enough, hide the remaining ones and set the Left and Width of the existing
ones to take advantage of available space. Simultaneously, build the IN list
for the PIVOT clause you will need for your crosstab.

Now you have the form set up, close the recordset, and build the crosstab
query string. You can probably use the same WHERE clause you used above.
Include the column heading names string in the PIVOT clause (just in case
any more or fewer columns turn up/disappear [which could happen in a
multi-user scenario.]) Assign this crosstab query string to the RecordSource
of the form.

It's not the simplest thing in the world, but is feasible if you have a good
knowledge of SQL, VBA, and recordsets.

 

[bymarce]

Thanks for the concept!! This will take me a while to build and I will
probably be posting back with more questions after I get started. Thanks
again.
Marcie

Marcie, what you are seeking to do is possible, but not simple. It will
require quite a bit of coding. Typically, I use a report rather than a form
for this (since resutls are read-only anyway and it prints better.)

In essence, you decide on the maximum number of columns your form will cope
with, and place an unbound text box on the form for each one. Name them
txt1, txt2, txt3, etc. (so you can refer to them in a loop.) And save the
form without any RecordSource.

In its Open event, you build up a string that is the SQL statement that
gives you a record for each column heading (i.e. each combination of
[Property] + [TestMethod] that maches the criteria for this run.)
OpenRecordset. Loop through the records, assigning the ControlSource of each
text box to the right 'field' that will be generated this run. If you have
too many, give a warning that your form doesn't cope; if you have more than
enough, hide the remaining ones and set the Left and Width of the existing
ones to take advantage of available space. Simultaneously, build the IN list
for the PIVOT clause you will need for your crosstab.

Now you have the form set up, close the recordset, and build the crosstab
query string. You can probably use the same WHERE clause you used above.
Include the column heading names string in the PIVOT clause (just in case
any more or fewer columns turn up/disappear [which could happen in a
multi-user scenario.]) Assign this crosstab query string to the RecordSource
of the form.

It's not the simplest thing in the world, but is feasible if you have a good
knowledge of SQL, VBA, and recordsets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have made several filter forms based Allen Browne's example. Thanks
Allen!! I would like to make a filter form that filters the rows and
headers
for a crosstab query and displays the sorted records in the bottom of the
form. I don't want my users to need to learn how to make a crosstab
query.
They won't use it if they have to learn too much. I have a data table
which
contains MLO (cataloge number, text field), Property (ie boiling pont),
TestMethod, NResults (number field), TResults (text field), and units. I
want MLO to be the row heading. I used a query to make the column
headings
based on abreviations for property and test method since you can only have
one column heading field in a crosstab. There are also proptery, test
method, and units tables that are used as look up tables. The property
and
test method tables are joined through a junction table (many to many).
Conditions of the tests are contained in a conditions table that is
related
to the data table through a junction table (many to many). My form has
multiselect list filter boxes for MLO, Property, Method, Tresults and
Nresults. Is this possible to do this on one form or do I need to make
the
form open a query based on search criteria? Thanks.
Marcie

 

[bymarce]

Hi Allen,
I think I've gotten to the last step. How do I include the column
heading string in the pivot clause? Here's what I have so far. I decided to
try this with a report first as you mentioned. When I click the button on my
form, I get the error "Property not Found" and DoCmd.OpenQuery ("qryCT") is
highlighted. When I try to open the query directly it says "Does not
recognize [Property.Properties] as valid expression or feild name." Thanks.
Marcie

Private Sub cmdRptDataCrossTab_Click()
If Me.FilterOn = False Then
MsgBox ("You have not selected any records. Please select" & vbCrLf
& "records by entering criteria and clicking " & "'Filter'" & ".")
GoTo ErrExit
Else
Dim strWhere As String
strWhere = Me.Filter
Dim CHsql As String
Dim strCH As String
Dim strQ As String
strQ = """"
strWhere = Replace(strWhere, "[Property]", "Data.Property")
strWhere = Replace(strWhere, "[MLO]", "Data.MLO")
strWhere = Replace(strWhere, "[TestMethod]", "Data.TestMethod")
strWhere = Replace(strWhere, "[RNumeric]", "Data.RNumeric")
strWhere = Replace(strWhere, "[RText]", "Data.RText")
strWhere = Replace(strWhere, "[Description]", "MLO.Description")
Debug.Print strWhere

'Build sql string for column headings of crosstab report.
strCH = "[Properties.Abbreviation] & " & strQ & vbCrLf & strQ & " &
[TestMethods.Abbreviation]"
CHsql = CHsql & "SELECT DISTINCT " & strCH & " AS ColumnHeadings "
CHsql = CHsql & "FROM (Properties INNER JOIN (TestMethods INNER JOIN
Data ON TestMethods.Method = Data.TestMethod) ON "
CHsql = CHsql & "Properties.Property = Data.Property) INNER JOIN
PropertiesMethodsJunction ON (TestMethods.Method = "
CHsql = CHsql & "PropertiesMethodsJunction.JMethod) AND
(Properties.Property = PropertiesMethodsJunction.JProperty) "
CHsql = CHsql & "WHERE (" & strWhere & ") ORDER BY " & strCH & ";"
Debug.Print CHsql

'Build Crosstab Query SQL
Dim CTQsql As String
Dim CTQqd As QueryDef

CTQsql = CTQsql & "TRANSFORM Avg(Data.RNumeric) AS AvgOfRNumeric "
CTQsql = CTQsql & "SELECT Data.MLO "
CTQsql = CTQsql & "FROM Data "
CTQsql = CTQsql & "WHERE (" & strWhere & ") GROUP BY Data.MLO "
CTQsql = CTQsql & "PIVOT " & "[Properties.Abbreviation]" & "&" &
"[TestMethods.Abbreviation]"
Set CTQqd = CurrentDb.QueryDefs("qryCT")
CTQqd.sql = CTQsql
DoCmd.OpenQuery ("qryCT")
Debug.Print CTQsql

'Assign results of crosstab to text boxes in report.
Dim CHqd As QueryDef
Set CHqd = CurrentDb.QueryDefs("qryColumnHead")
CHqd.sql = CHsql
CHqd.Close
DoCmd.OpenQuery ("qryColumnHead")
DoCmd.OpenReport "rptDataCrossTab", acViewPreview
End If
ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.Description
Resume ErrExit
Resume
End Sub

Marcie, what you are seeking to do is possible, but not simple. It will
require quite a bit of coding. Typically, I use a report rather than a form
for this (since resutls are read-only anyway and it prints better.)

In essence, you decide on the maximum number of columns your form will cope
with, and place an unbound text box on the form for each one. Name them
txt1, txt2, txt3, etc. (so you can refer to them in a loop.) And save the
form without any RecordSource.

In its Open event, you build up a string that is the SQL statement that
gives you a record for each column heading (i.e. each combination of
[Property] + [TestMethod] that maches the criteria for this run.)
OpenRecordset. Loop through the records, assigning the ControlSource of each
text box to the right 'field' that will be generated this run. If you have
too many, give a warning that your form doesn't cope; if you have more than
enough, hide the remaining ones and set the Left and Width of the existing
ones to take advantage of available space. Simultaneously, build the IN list
for the PIVOT clause you will need for your crosstab.

Now you have the form set up, close the recordset, and build the crosstab
query string. You can probably use the same WHERE clause you used above.
Include the column heading names string in the PIVOT clause (just in case
any more or fewer columns turn up/disappear [which could happen in a
multi-user scenario.]) Assign this crosstab query string to the RecordSource
of the form.

It's not the simplest thing in the world, but is feasible if you have a good
knowledge of SQL, VBA, and recordsets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have made several filter forms based Allen Browne's example. Thanks
Allen!! I would like to make a filter form that filters the rows and
headers
for a crosstab query and displays the sorted records in the bottom of the
form. I don't want my users to need to learn how to make a crosstab
query.
They won't use it if they have to learn too much. I have a data table
which
contains MLO (cataloge number, text field), Property (ie boiling pont),
TestMethod, NResults (number field), TResults (text field), and units. I
want MLO to be the row heading. I used a query to make the column
headings
based on abreviations for property and test method since you can only have
one column heading field in a crosstab. There are also proptery, test
method, and units tables that are used as look up tables. The property
and
test method tables are joined through a junction table (many to many).
Conditions of the tests are contained in a conditions table that is
related
to the data table through a junction table (many to many). My form has
multiselect list filter boxes for MLO, Property, Method, Tresults and
Nresults. Is this possible to do this on one form or do I need to make
the
form open a query based on search criteria? Thanks.
Marcie

 

[Allen Browne]

In essence, you OpenRecordset() on a DISTINCT list of the values that meet
the criteria you're going to use for the report, and loop through the
records, building up the list for the PIVOT clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I think I've gotten to the last step. How do I include the column
heading string in the pivot clause? Here's what I have so far. I decided
to
try this with a report first as you mentioned. When I click the button on
my
form, I get the error "Property not Found" and DoCmd.OpenQuery ("qryCT")
is
highlighted. When I try to open the query directly it says "Does not
recognize [Property.Properties] as valid expression or feild name."
Thanks.
Marcie

Private Sub cmdRptDataCrossTab_Click()
If Me.FilterOn = False Then
MsgBox ("You have not selected any records. Please select" & vbCrLf
& "records by entering criteria and clicking " & "'Filter'" & ".")
GoTo ErrExit
Else
Dim strWhere As String
strWhere = Me.Filter
Dim CHsql As String
Dim strCH As String
Dim strQ As String
strQ = """"
strWhere = Replace(strWhere, "[Property]", "Data.Property")
strWhere = Replace(strWhere, "[MLO]", "Data.MLO")
strWhere = Replace(strWhere, "[TestMethod]", "Data.TestMethod")
strWhere = Replace(strWhere, "[RNumeric]", "Data.RNumeric")
strWhere = Replace(strWhere, "[RText]", "Data.RText")
strWhere = Replace(strWhere, "[Description]", "MLO.Description")
Debug.Print strWhere

'Build sql string for column headings of crosstab report.
strCH = "[Properties.Abbreviation] & " & strQ & vbCrLf & strQ & " &
[TestMethods.Abbreviation]"
CHsql = CHsql & "SELECT DISTINCT " & strCH & " AS ColumnHeadings "
CHsql = CHsql & "FROM (Properties INNER JOIN (TestMethods INNER
JOIN
Data ON TestMethods.Method = Data.TestMethod) ON "
CHsql = CHsql & "Properties.Property = Data.Property) INNER JOIN
PropertiesMethodsJunction ON (TestMethods.Method = "
CHsql = CHsql & "PropertiesMethodsJunction.JMethod) AND
(Properties.Property = PropertiesMethodsJunction.JProperty) "
CHsql = CHsql & "WHERE (" & strWhere & ") ORDER BY " & strCH & ";"
Debug.Print CHsql

'Build Crosstab Query SQL
Dim CTQsql As String
Dim CTQqd As QueryDef

CTQsql = CTQsql & "TRANSFORM Avg(Data.RNumeric) AS
AvgOfRNumeric "
CTQsql = CTQsql & "SELECT Data.MLO "
CTQsql = CTQsql & "FROM Data "
CTQsql = CTQsql & "WHERE (" & strWhere & ") GROUP BY Data.MLO "
CTQsql = CTQsql & "PIVOT " & "[Properties.Abbreviation]" & "&"
&
"[TestMethods.Abbreviation]"
Set CTQqd = CurrentDb.QueryDefs("qryCT")
CTQqd.sql = CTQsql
DoCmd.OpenQuery ("qryCT")
Debug.Print CTQsql

'Assign results of crosstab to text boxes in report.
Dim CHqd As QueryDef
Set CHqd = CurrentDb.QueryDefs("qryColumnHead")
CHqd.sql = CHsql
CHqd.Close
DoCmd.OpenQuery ("qryColumnHead")
DoCmd.OpenReport "rptDataCrossTab", acViewPreview
End If
ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.Description
Resume ErrExit
Resume
End Sub

Marcie, what you are seeking to do is possible, but not simple. It will
require quite a bit of coding. Typically, I use a report rather than a
form
for this (since resutls are read-only anyway and it prints better.)

In essence, you decide on the maximum number of columns your form will
cope
with, and place an unbound text box on the form for each one. Name them
txt1, txt2, txt3, etc. (so you can refer to them in a loop.) And save the
form without any RecordSource.

In its Open event, you build up a string that is the SQL statement that
gives you a record for each column heading (i.e. each combination of
[Property] + [TestMethod] that maches the criteria for this run.)
OpenRecordset. Loop through the records, assigning the ControlSource of
each
text box to the right 'field' that will be generated this run. If you
have
too many, give a warning that your form doesn't cope; if you have more
than
enough, hide the remaining ones and set the Left and Width of the
existing
ones to take advantage of available space. Simultaneously, build the IN
list
for the PIVOT clause you will need for your crosstab.

Now you have the form set up, close the recordset, and build the crosstab
query string. You can probably use the same WHERE clause you used above.
Include the column heading names string in the PIVOT clause (just in case
any more or fewer columns turn up/disappear [which could happen in a
multi-user scenario.]) Assign this crosstab query string to the
RecordSource
of the form.

It's not the simplest thing in the world, but is feasible if you have a
good
knowledge of SQL, VBA, and recordsets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have made several filter forms based Allen Browne's example. Thanks
Allen!! I would like to make a filter form that filters the rows and
headers
for a crosstab query and displays the sorted records in the bottom of
the
form. I don't want my users to need to learn how to make a crosstab
query.
They won't use it if they have to learn too much. I have a data table
which
contains MLO (cataloge number, text field), Property (ie boiling pont),
TestMethod, NResults (number field), TResults (text field), and units.
I
want MLO to be the row heading. I used a query to make the column
headings
based on abreviations for property and test method since you can only
have
one column heading field in a crosstab. There are also proptery, test
method, and units tables that are used as look up tables. The property
and
test method tables are joined through a junction table (many to many).
Conditions of the tests are contained in a conditions table that is
related
to the data table through a junction table (many to many). My form has
multiselect list filter boxes for MLO, Property, Method, Tresults and
Nresults. Is this possible to do this on one form or do I need to make
the
form open a query based on search criteria? Thanks.
Marcie

 

[bymarce]

I made three querys; qryData (returns MLO, ColumnHeads - calculated field,
Results - calculated field), qryDataCH (returns distinct list of
ColumnHeads), and qryDataCT (crosstab based on qryData). All three return
the correct results. In the report I've put code to try and assign the
results of qryDataCH to unbound controls in the header and to assign them as
the control source of unbound controls in the detail. There's nothing showin
up in the report except the MLO. I have the record source of the report set
to qryDataCT. How can I fix this? Thanks!!
Marcie

Here's the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dbDtl As Database
Dim rsDtl As DAO.Recordset
Dim N As Long
Dim ctl As Control

N = 1
Dim strDtls As String
Set dbDtl = CurrentDb()
Set rsDtl = dbDtl.OpenRecordset("qryData")
Do Until rsDtl.EOF
Me.Controls("Dtl" & N).ControlSource = rsDtl.Fields("Results")
N = N + 1
rsDtl.MoveNext
Loop

End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
'Use sql as recordset to assign values to text boxes in report.
Dim dbCH As Database
Dim rsCH As DAO.Recordset
Dim N As Long
Dim TextBox As String
Dim NumRcd As Long
NumRcd = DCount("ColumnHeads", "qryDataCH")
N = 1
Set dbCH = CurrentDb()
Set rsCH = dbCH.OpenRecordset("qryDataCH")
If NumRcd > 10 Then
MsgBox ("You have selected to many property/method" &
Chr$(13) & "combinations. Please select 10 or fewer in your data search.")
DoCmd.Close acQuery, "qryDataCH", acSaveNo
GoTo ErrExit
Else
Do Until rsCH.EOF
Me.Controls("txt" & N) = rsCH.Fields("ColumnHeads")
N = N + 1
rsCH.MoveNext
Loop
DoCmd.Close acQuery, "qryDataCH", acSaveNo
End If

ErrExit:
Exit Sub

End Sub

In essence, you OpenRecordset() on a DISTINCT list of the values that meet
the criteria you're going to use for the report, and loop through the
records, building up the list for the PIVOT clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I think I've gotten to the last step. How do I include the column
heading string in the pivot clause? Here's what I have so far. I decided
to
try this with a report first as you mentioned. When I click the button on
my
form, I get the error "Property not Found" and DoCmd.OpenQuery ("qryCT")
is
highlighted. When I try to open the query directly it says "Does not
recognize [Property.Properties] as valid expression or feild name."
Thanks.
Marcie

Private Sub cmdRptDataCrossTab_Click()
If Me.FilterOn = False Then
MsgBox ("You have not selected any records. Please select" & vbCrLf
& "records by entering criteria and clicking " & "'Filter'" & ".")
GoTo ErrExit
Else
Dim strWhere As String
strWhere = Me.Filter
Dim CHsql As String
Dim strCH As String
Dim strQ As String
strQ = """"
strWhere = Replace(strWhere, "[Property]", "Data.Property")
strWhere = Replace(strWhere, "[MLO]", "Data.MLO")
strWhere = Replace(strWhere, "[TestMethod]", "Data.TestMethod")
strWhere = Replace(strWhere, "[RNumeric]", "Data.RNumeric")
strWhere = Replace(strWhere, "[RText]", "Data.RText")
strWhere = Replace(strWhere, "[Description]", "MLO.Description")
Debug.Print strWhere

'Build sql string for column headings of crosstab report.
strCH = "[Properties.Abbreviation] & " & strQ & vbCrLf & strQ & " &
[TestMethods.Abbreviation]"
CHsql = CHsql & "SELECT DISTINCT " & strCH & " AS ColumnHeadings "
CHsql = CHsql & "FROM (Properties INNER JOIN (TestMethods INNER
JOIN
Data ON TestMethods.Method = Data.TestMethod) ON "
CHsql = CHsql & "Properties.Property = Data.Property) INNER JOIN
PropertiesMethodsJunction ON (TestMethods.Method = "
CHsql = CHsql & "PropertiesMethodsJunction.JMethod) AND
(Properties.Property = PropertiesMethodsJunction.JProperty) "
CHsql = CHsql & "WHERE (" & strWhere & ") ORDER BY " & strCH & ";"
Debug.Print CHsql

'Build Crosstab Query SQL
Dim CTQsql As String
Dim CTQqd As QueryDef

CTQsql = CTQsql & "TRANSFORM Avg(Data.RNumeric) AS
AvgOfRNumeric "
CTQsql = CTQsql & "SELECT Data.MLO "
CTQsql = CTQsql & "FROM Data "
CTQsql = CTQsql & "WHERE (" & strWhere & ") GROUP BY Data.MLO "
CTQsql = CTQsql & "PIVOT " & "[Properties.Abbreviation]" & "&"
&
"[TestMethods.Abbreviation]"
Set CTQqd = CurrentDb.QueryDefs("qryCT")
CTQqd.sql = CTQsql
DoCmd.OpenQuery ("qryCT")
Debug.Print CTQsql

'Assign results of crosstab to text boxes in report.
Dim CHqd As QueryDef
Set CHqd = CurrentDb.QueryDefs("qryColumnHead")
CHqd.sql = CHsql
CHqd.Close
DoCmd.OpenQuery ("qryColumnHead")
DoCmd.OpenReport "rptDataCrossTab", acViewPreview
End If
ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.Description
Resume ErrExit
Resume
End Sub

Marcie, what you are seeking to do is possible, but not simple. It will
require quite a bit of coding. Typically, I use a report rather than a
form
for this (since resutls are read-only anyway and it prints better.)

In essence, you decide on the maximum number of columns your form will
cope
with, and place an unbound text box on the form for each one. Name them
txt1, txt2, txt3, etc. (so you can refer to them in a loop.) And save the
form without any RecordSource.

In its Open event, you build up a string that is the SQL statement that
gives you a record for each column heading (i.e. each combination of
[Property] + [TestMethod] that maches the criteria for this run.)
OpenRecordset. Loop through the records, assigning the ControlSource of
each
text box to the right 'field' that will be generated this run. If you
have
too many, give a warning that your form doesn't cope; if you have more
than
enough, hide the remaining ones and set the Left and Width of the
existing
ones to take advantage of available space. Simultaneously, build the IN
list
for the PIVOT clause you will need for your crosstab.

Now you have the form set up, close the recordset, and build the crosstab
query string. You can probably use the same WHERE clause you used above.
Include the column heading names string in the PIVOT clause (just in case
any more or fewer columns turn up/disappear [which could happen in a
multi-user scenario.]) Assign this crosstab query string to the
RecordSource
of the form.

It's not the simplest thing in the world, but is feasible if you have a
good
knowledge of SQL, VBA, and recordsets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have made several filter forms based Allen Browne's example. Thanks
Allen!! I would like to make a filter form that filters the rows and
headers
for a crosstab query and displays the sorted records in the bottom of
the
form. I don't want my users to need to learn how to make a crosstab
query.
They won't use it if they have to learn too much. I have a data table
which
contains MLO (cataloge number, text field), Property (ie boiling pont),
TestMethod, NResults (number field), TResults (text field), and units.
I
want MLO to be the row heading. I used a query to make the column
headings
based on abreviations for property and test method since you can only
have
one column heading field in a crosstab. There are also proptery, test
method, and units tables that are used as look up tables. The property
and
test method tables are joined through a junction table (many to many).
Conditions of the tests are contained in a conditions table that is
related
to the data table through a junction table (many to many). My form has
multiselect list filter boxes for MLO, Property, Method, Tresults and
Nresults. Is this possible to do this on one form or do I need to make
the
form open a query based on search criteria? Thanks.
Marcie

 

[bymarce]

I got this worked out. I changed the report headers to labels rather than
unbound text boxes and put the following code in the "On Load" event of the
report. Thanks for your help!!
Marcie
Private Sub Report_Load()
'Concept for crosstab filter form report/from Allen Browne.
'Use sql as recordset to assign values to text boxes in
report.
Dim dbCH As Database
Dim rsCH As DAO.Recordset
Dim N As Long
Dim TextBox As String
Dim NumRcd As Long
NumRcd = DCount("ColumnHeads", "qryDataCH")
N = 1
Set dbCH = CurrentDb()
Set rsCH = dbCH.OpenRecordset("qryDataCH")
If NumRcd > 10 Then
MsgBox ("You have selected to many property/method" &
Chr$(13) & "combinations. Please select 10 or fewer in your data search.")
GoTo ErrExit
Else
Do Until rsCH.EOF
Me.Controls("txt" & N).Caption =
rsCH.Fields("ColumnHeads")
Me.Controls("dtl" & N).ControlSource =
rsCH.Fields("ColumnHeads")
N = N + 1
rsCH.MoveNext
Loop

End If
If NumRcd < 10 Then
NumRcd = NumRcd + 1
For N = NumRcd To 10
Me.Controls("txt" & N).ForeColor = vbWhite
Next
End If
DoCmd.Close acQuery, "qryDataCH", acSaveNo
DoCmd.Close acQuery, "qryData", acSaveNo
DoCmd.Close acQuery, "qryDataCT", acSaveNo
ErrExit:
Exit Sub

End Sub

I made three querys; qryData (returns MLO, ColumnHeads - calculated field,
Results - calculated field), qryDataCH (returns distinct list of
ColumnHeads), and qryDataCT (crosstab based on qryData). All three return
the correct results. In the report I've put code to try and assign the
results of qryDataCH to unbound controls in the header and to assign them as
the control source of unbound controls in the detail. There's nothing showin
up in the report except the MLO. I have the record source of the report set
to qryDataCT. How can I fix this? Thanks!!
Marcie

Here's the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dbDtl As Database
Dim rsDtl As DAO.Recordset
Dim N As Long
Dim ctl As Control

N = 1
Dim strDtls As String
Set dbDtl = CurrentDb()
Set rsDtl = dbDtl.OpenRecordset("qryData")
Do Until rsDtl.EOF
Me.Controls("Dtl" & N).ControlSource = rsDtl.Fields("Results")
N = N + 1
rsDtl.MoveNext
Loop

End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
'Use sql as recordset to assign values to text boxes in report.
Dim dbCH As Database
Dim rsCH As DAO.Recordset
Dim N As Long
Dim TextBox As String
Dim NumRcd As Long
NumRcd = DCount("ColumnHeads", "qryDataCH")
N = 1
Set dbCH = CurrentDb()
Set rsCH = dbCH.OpenRecordset("qryDataCH")
If NumRcd > 10 Then
MsgBox ("You have selected to many property/method" &
Chr$(13) & "combinations. Please select 10 or fewer in your data search.")
DoCmd.Close acQuery, "qryDataCH", acSaveNo
GoTo ErrExit
Else
Do Until rsCH.EOF
Me.Controls("txt" & N) = rsCH.Fields("ColumnHeads")
N = N + 1
rsCH.MoveNext
Loop
DoCmd.Close acQuery, "qryDataCH", acSaveNo
End If

ErrExit:
Exit Sub

End Sub

In essence, you OpenRecordset() on a DISTINCT list of the values that meet
the criteria you're going to use for the report, and loop through the
records, building up the list for the PIVOT clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I think I've gotten to the last step. How do I include the column
heading string in the pivot clause? Here's what I have so far. I decided
to
try this with a report first as you mentioned. When I click the button on
my
form, I get the error "Property not Found" and DoCmd.OpenQuery ("qryCT")
is
highlighted. When I try to open the query directly it says "Does not
recognize [Property.Properties] as valid expression or feild name."
Thanks.
Marcie

Private Sub cmdRptDataCrossTab_Click()
If Me.FilterOn = False Then
MsgBox ("You have not selected any records. Please select" & vbCrLf
& "records by entering criteria and clicking " & "'Filter'" & ".")
GoTo ErrExit
Else
Dim strWhere As String
strWhere = Me.Filter
Dim CHsql As String
Dim strCH As String
Dim strQ As String
strQ = """"
strWhere = Replace(strWhere, "[Property]", "Data.Property")
strWhere = Replace(strWhere, "[MLO]", "Data.MLO")
strWhere = Replace(strWhere, "[TestMethod]", "Data.TestMethod")
strWhere = Replace(strWhere, "[RNumeric]", "Data.RNumeric")
strWhere = Replace(strWhere, "[RText]", "Data.RText")
strWhere = Replace(strWhere, "[Description]", "MLO.Description")
Debug.Print strWhere

'Build sql string for column headings of crosstab report.
strCH = "[Properties.Abbreviation] & " & strQ & vbCrLf & strQ & " &
[TestMethods.Abbreviation]"
CHsql = CHsql & "SELECT DISTINCT " & strCH & " AS ColumnHeadings "
CHsql = CHsql & "FROM (Properties INNER JOIN (TestMethods INNER
JOIN
Data ON TestMethods.Method = Data.TestMethod) ON "
CHsql = CHsql & "Properties.Property = Data.Property) INNER JOIN
PropertiesMethodsJunction ON (TestMethods.Method = "
CHsql = CHsql & "PropertiesMethodsJunction.JMethod) AND
(Properties.Property = PropertiesMethodsJunction.JProperty) "
CHsql = CHsql & "WHERE (" & strWhere & ") ORDER BY " & strCH & ";"
Debug.Print CHsql

'Build Crosstab Query SQL
Dim CTQsql As String
Dim CTQqd As QueryDef

CTQsql = CTQsql & "TRANSFORM Avg(Data.RNumeric) AS
AvgOfRNumeric "
CTQsql = CTQsql & "SELECT Data.MLO "
CTQsql = CTQsql & "FROM Data "
CTQsql = CTQsql & "WHERE (" & strWhere & ") GROUP BY Data.MLO "
CTQsql = CTQsql & "PIVOT " & "[Properties.Abbreviation]" & "&"
&
"[TestMethods.Abbreviation]"
Set CTQqd = CurrentDb.QueryDefs("qryCT")
CTQqd.sql = CTQsql
DoCmd.OpenQuery ("qryCT")
Debug.Print CTQsql

'Assign results of crosstab to text boxes in report.
Dim CHqd As QueryDef
Set CHqd = CurrentDb.QueryDefs("qryColumnHead")
CHqd.sql = CHsql
CHqd.Close
DoCmd.OpenQuery ("qryColumnHead")
DoCmd.OpenReport "rptDataCrossTab", acViewPreview
End If
ErrExit:
Exit Sub

ErrHandle:
MsgBox Err.Description
Resume ErrExit
Resume
End Sub

:

Marcie, what you are seeking to do is possible, but not simple. It will
require quite a bit of coding. Typically, I use a report rather than a
form
for this (since resutls are read-only anyway and it prints better.)

In essence, you decide on the maximum number of columns your form will
cope
with, and place an unbound text box on the form for each one. Name them
txt1, txt2, txt3, etc. (so you can refer to them in a loop.) And save the
form without any RecordSource.

In its Open event, you build up a string that is the SQL statement that
gives you a record for each column heading (i.e. each combination of
[Property] + [TestMethod] that maches the criteria for this run.)
OpenRecordset. Loop through the records, assigning the ControlSource of
each
text box to the right 'field' that will be generated this run. If you
have
too many, give a warning that your form doesn't cope; if you have more
than
enough, hide the remaining ones and set the Left and Width of the
existing
ones to take advantage of available space. Simultaneously, build the IN
list
for the PIVOT clause you will need for your crosstab.

Now you have the form set up, close the recordset, and build the crosstab
query string. You can probably use the same WHERE clause you used above.
Include the column heading names string in the PIVOT clause (just in case
any more or fewer columns turn up/disappear [which could happen in a
multi-user scenario.]) Assign this crosstab query string to the
RecordSource
of the form.

It's not the simplest thing in the world, but is feasible if you have a
good
knowledge of SQL, VBA, and recordsets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have made several filter forms based Allen Browne's example. Thanks
Allen!! I would like to make a filter form that filters the rows and
headers
for a crosstab query and displays the sorted records in the bottom of
the
form. I don't want my users to need to learn how to make a crosstab
query.
They won't use it if they have to learn too much. I have a data table
which
contains MLO (cataloge number, text field), Property (ie boiling pont),
TestMethod, NResults (number field), TResults (text field), and units.
I
want MLO to be the row heading. I used a query to make the column
headings
based on abreviations for property and test method since you can only
have
one column heading field in a crosstab. There are also proptery, test
method, and units tables that are used as look up tables. The property
and
test method tables are joined through a junction table (many to many).
Conditions of the tests are contained in a conditions table that is
related
to the data table through a junction table (many to many). My form has
multiselect list filter boxes for MLO, Property, Method, Tresults and
Nresults. Is this possible to do this on one form or do I need to make
the
form open a query based on search criteria? Thanks.
Marcie