Use crosstab column heading in a calculation

[Bill R via AccessMonster.com]

Duane,

I'll try that.
Did you check out that thread? I have no familiarity with forms as datasheets,
which is why I was trying to avoid using them. Jesper said he had a workable
solution and Paul Overway apparently concurred. But I can't tell what "it" is
in his statement, "The first time it runs I create a crosstab query ..." and
I have no idea how he set up his subform.

Bill

I would use code to set the parameter values in the sql like:
strSQL = strSQL & "TRANSFORM ..." & vbCrLf
strSQL = strSQL & "SELECT ..." & vbCrLf
strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2 " & vbCrLf
strSQL = strSQL & "WHERE swapid=" & [Forms]![frmSwaps]![SwapID] & vbCrLf
strSQL = strSQL & " And somefield=" & [Forms]![frmSwaps]![Conf95] & vbCrLf
strSQL = strSQL & " GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & " PIVOT CalcMonth;"

Duane,

[quoted text clipped - 52 lines]

 

[Duane Hookom]

I believe their solution is to assign the crosstab query as the object
source of the control on the main form.

--
Duane Hookom
MS Access MVP
--

Duane,

I'll try that.
Did you check out that thread? I have no familiarity with forms as
datasheets,
which is why I was trying to avoid using them. Jesper said he had a
workable
solution and Paul Overway apparently concurred. But I can't tell what "it"
is
in his statement, "The first time it runs I create a crosstab query ..."
and
I have no idea how he set up his subform.

Bill

I would use code to set the parameter values in the sql like:
strSQL = strSQL & "TRANSFORM ..." & vbCrLf
strSQL = strSQL & "SELECT ..." & vbCrLf
strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2 " & vbCrLf
strSQL = strSQL & "WHERE swapid=" & [Forms]![frmSwaps]![SwapID] & vbCrLf
strSQL = strSQL & " And somefield=" & [Forms]![frmSwaps]![Conf95] & vbCrLf
strSQL = strSQL & " GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & " PIVOT CalcMonth;"

Duane,

[quoted text clipped - 52 lines]

Bill

 

[Bill R via AccessMonster.com]

When I do that, I get error msgs like "... can't use crosstab query..."
It sure is frustrating having created this beautiful crosstab query with it's
complex calculation that returns everything I want to show my client, and
then having no way to display the results in a subform. This is something
that should be a breeze, but it's a virtual impossibility! Why else would
anyone want to create such a query if not to display it's results? There may
be other reasons, but displaying its results seems like a pretty common one
to me.

I believe their solution is to assign the crosstab query as the object
source of the control on the main form.

Duane,

[quoted text clipped - 25 lines]

 

[Duane Hookom]

You can easily show the results of a crosstab in a form or subform if the
crosstab has defined column headings. You can create reports with either
static or dynamic if you find the solutions. I have given you a link to a
sample application that I created that accepts dynamic columns in a subform.

--
Duane Hookom
MS Access MVP
--

When I do that, I get error msgs like "... can't use crosstab query..."
It sure is frustrating having created this beautiful crosstab query with
it's
complex calculation that returns everything I want to show my client, and
then having no way to display the results in a subform. This is something
that should be a breeze, but it's a virtual impossibility! Why else would
anyone want to create such a query if not to display it's results? There
may
be other reasons, but displaying its results seems like a pretty common
one
to me.

I believe their solution is to assign the crosstab query as the object
source of the control on the main form.

Duane,

[quoted text clipped - 25 lines]

Bill

 

[Bill R via AccessMonster.com]

Duane,

Right, I'm developing the code to do that, but that's the point. If only
there were a control that would simply display the results of any query on a
subform, regardless of it's # of fields or it's design. Or, better yet, if a
subform could be used as a kind of blank display screen to display the
results of any query. Maybe in my next life!

Thanks for all your help,

Bill

You can easily show the results of a crosstab in a form or subform if the
crosstab has defined column headings. You can create reports with either
static or dynamic if you find the solutions. I have given you a link to a
sample application that I created that accepts dynamic columns in a subform.

When I do that, I get error msgs like "... can't use crosstab query..."
It sure is frustrating having created this beautiful crosstab query with

[quoted text clipped - 16 lines]

 

[Duane Hookom]

I went back to the thread link you posted a while back. Try this:

Create a new blank form and add a subform control:
Name: fsubOne
Source Object:---nothing here-----

Add a combo box to the main form:
Name: cboQueries
Row Source:
--------------------------------------------------
SELECT msysObjects.Name
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
--------------------------------------------------
After Update code:
--------------------------------------------------
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub
--------------------------------------------------

--
Duane Hookom
MS Access MVP
--

Duane,

Right, I'm developing the code to do that, but that's the point. If only
there were a control that would simply display the results of any query on
a
subform, regardless of it's # of fields or it's design. Or, better yet, if
a
subform could be used as a kind of blank display screen to display the
results of any query. Maybe in my next life!

Thanks for all your help,

Bill

You can easily show the results of a crosstab in a form or subform if the
crosstab has defined column headings. You can create reports with either
static or dynamic if you find the solutions. I have given you a link to a
sample application that I created that accepts dynamic columns in a
subform.

When I do that, I get error msgs like "... can't use crosstab query..."
It sure is frustrating having created this beautiful crosstab query with

[quoted text clipped - 16 lines]

Bill

 

[Bill R via AccessMonster.com]

Holy C**p!
That's great!

Thanks, Duane

Bill

I went back to the thread link you posted a while back. Try this:

Create a new blank form and add a subform control:
Name: fsubOne
Source Object:---nothing here-----

Add a combo box to the main form:
Name: cboQueries
Row Source:
--------------------------------------------------
SELECT msysObjects.Name
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
--------------------------------------------------
After Update code:
--------------------------------------------------
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub
--------------------------------------------------

Duane,

[quoted text clipped - 21 lines]

 

[Duane Hookom]

My thoughts also. Thanks for pushing me to try this out....

--
Duane Hookom
MS Access MVP
--

Holy C**p!
That's great!

Thanks, Duane

Bill

I went back to the thread link you posted a while back. Try this:

Create a new blank form and add a subform control:
Name: fsubOne
Source Object:---nothing here-----

Add a combo box to the main form:
Name: cboQueries
Row Source:
--------------------------------------------------
SELECT msysObjects.Name
FROM msysObjects
WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5))
ORDER BY msysObjects.Name;
--------------------------------------------------
After Update code:
--------------------------------------------------
Private Sub cboQueries_AfterUpdate()
If Not IsNull(Me.cboQueries) Then
Me.fsubOne.SourceObject = "Query." & Me.cboQueries
End If
End Sub
--------------------------------------------------

Duane,

[quoted text clipped - 21 lines]

Bill

 

[Bill R via AccessMonster.com]

I understand from the post that it works with tables, too.

BTW, it still doesn't work for crosstab queries, which is what the post had
purported. I'm using a more mundane solution.

Thanks,

Bill

My thoughts also. Thanks for pushing me to try this out....

Holy C**p!
That's great!

[quoted text clipped - 32 lines]

 

[Vincent Johns]

So, why don't you just specify which columns you want?

In Query Design View, right click in the upper window and set the Column
Headings property to something like

1,2,23

or whatever you want displayed there.

Or, you could place a filter on the Crosstab Query by specifying a
criterion (such as

< 69

) on the Column Heading field in Query Design View.

Alternatively, you could define another Select Query that uses your
Crosstab Query as its data source and select only the fields (via a
criterion in your Select Query) that you want to use. Don't expect the
results of the Select Query to be modifiable, though.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

When I open the form I get the msg:

"You can't use a pass-through query or a non-fixed-column crosstab query as a
record source for a subform or a subreport.
Before you bind a subform or a subreport to a crosstab query, set the query's
ColumnHeadings property"

You don't ask for much do you ;-)

You can only show records in a subform where you know the fields so you can
set the control sources. I have not seen solutions for dynamic subforms. I
have created solutions for dynamic fields in reports.

I do create a somewhat dynamic subform in the Query By Form applet available
for download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane,

[quoted text clipped - 17 lines]

Bill

 

[Bill R via AccessMonster.com]

The crosstab query delivers a variable # of fields. I won't know in advance
how many fields or what their names will be. As long as I have to write code
anyway, I may as well do the whole operation in a VBA procedure, which is
what I've decided to do.
It is indeed unfortunate that Duane's simple form would be a perfect solution,
if only it could work for a crosstab query of indeterminate fields.

That's showbiz!

So, why don't you just specify which columns you want?

In Query Design View, right click in the upper window and set the Column
Headings property to something like

1,2,23

or whatever you want displayed there.

Or, you could place a filter on the Crosstab Query by specifying a
criterion (such as

< 69

) on the Column Heading field in Query Design View.

Alternatively, you could define another Select Query that uses your
Crosstab Query as its data source and select only the fields (via a
criterion in your Select Query) that you want to use. Don't expect the
results of the Select Query to be modifiable, though.

When I open the form I get the msg:

[quoted text clipped - 18 lines]

 

[Vincent Johns]

OK, I wasn't thinking that you actually WANTED to have a variable number
of fields, as apparently you do. But are you sure your users want to
see data displayed like that? They would have difficulty, I expect,
navigating 120-130 fields on a subform. (Or at least I think I would.)
I don't know, of course, since I'm not familiar with your application,
but my guess is that if you can find a way to choose convenient subsets
of, say, a dozen or so related fields at a time for users to deal with,
it might be easier for them to use your system.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

The crosstab query delivers a variable # of fields. I won't know in advance
how many fields or what their names will be. As long as I have to write code
anyway, I may as well do the whole operation in a VBA procedure, which is
what I've decided to do.
It is indeed unfortunate that Duane's simple form would be a perfect solution,
if only it could work for a crosstab query of indeterminate fields.

That's showbiz!

So, why don't you just specify which columns you want?

In Query Design View, right click in the upper window and set the Column
Headings property to something like

1,2,23

or whatever you want displayed there.

Or, you could place a filter on the Crosstab Query by specifying a
criterion (such as

< 69

) on the Column Heading field in Query Design View.

Alternatively, you could define another Select Query that uses your
Crosstab Query as its data source and select only the fields (via a
criterion in your Select Query) that you want to use. Don't expect the
results of the Select Query to be modifiable, though.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

When I open the form I get the msg:

[quoted text clipped - 18 lines]

Bill