A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use crosstab column heading in a calculation



 
 
Thread Tools Display Modes
  #21  
Old January 18th, 2006, 09:24 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com
  #22  
Old January 18th, 2006, 10:09 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a8e766574b8b@uwe...
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

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com



  #23  
Old January 18th, 2006, 10:17 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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.

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com
  #24  
Old January 19th, 2006, 03:11 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a8eed0d898ff@uwe...
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.

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com



  #25  
Old January 19th, 2006, 02:16 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com
  #26  
Old January 19th, 2006, 05:35 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a974d2956668@uwe...
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

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com



  #27  
Old January 19th, 2006, 07:22 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

Holy C**p!
That's great!

Thanks, Duane

Bill

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com
  #28  
Old January 20th, 2006, 04:33 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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

--
Duane Hookom
MS Access MVP
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a99f72116c58@uwe...
Holy C**p!
That's great!

Thanks, Duane

Bill

Duane Hookom wrote:
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 Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com



  #29  
Old January 20th, 2006, 06:16 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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

Duane Hookom wrote:
My thoughts also. Thanks for pushing me to try this out....

Holy C**p!
That's great!

[quoted text clipped - 32 lines]

Bill


--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com
  #30  
Old January 20th, 2006, 07:05 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

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
Please feel free to quote anything I say here.


Bill R via AccessMonster.com wrote:
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"


Duane Hookom wrote:

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/O...p#Hookom,Duane


Duane,


[quoted text clipped - 17 lines]

Bill



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David General Discussion 1 January 6th, 2006 04:56 AM
creating a bar graph Johnfli General Discussion 0 October 26th, 2005 08:16 PM
Crosstab Column Heading Sort MJatAflac Running & Setting Up Queries 3 June 29th, 2005 01:54 AM
How do I set up a report using dates as my report header? Robin Setting Up & Running Reports 16 November 13th, 2004 02:00 PM
Using Validation to force entry into cells? Mark General Discussion 16 October 27th, 2004 09:23 PM


All times are GMT +1. The time now is 11:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.