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
  #11  
Old January 18th, 2006, 02:27 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default Use crosstab column heading in a calculation

Bingo!!!

Thanks a million, Duane.

Bill

Duane Hookom wrote:
You must always define the data type of all parameters in crosstab queries.
Select Query-Parameters and enter
[Forms]![frmSwaps]![txtSwapID] datatype

BTW: without reading all your other postings, the solution for your first
posting is to create a query with this sql
TRANSFORM
First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth]) AS
Expr1
SELECT tblSwapParameters.SpotMonth
FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1
GROUP BY tblSwapParameters.SpotMonth
PIVOT tblSwapParameters_1.SpotMonth;

PS:

[quoted text clipped - 42 lines]

Bill


--
Bill Reed

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

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

Duane,

Now that I've got the simple calculation working, is it possible to include a
value from a control on a form in the calculation "Expr1"? What are the
restrictions on calculations in a crosstab query? I've already come across a
problem in using a control on the main form as criteria in a crosstab query
or even in an underlying query for a crosstab query.

If I can't use data from a control on a form, I will fall back to "Plan I"
and run a make table query based on this crosstab query, and then populate
the records and fields using a calculation in VBA.

Thanks,

Bill

Duane Hookom wrote:
You must always define the data type of all parameters in crosstab queries.
Select Query-Parameters and enter
[Forms]![frmSwaps]![txtSwapID] datatype

BTW: without reading all your other postings, the solution for your first
posting is to create a query with this sql
TRANSFORM
First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth]) AS
Expr1
SELECT tblSwapParameters.SpotMonth
FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1
GROUP BY tblSwapParameters.SpotMonth
PIVOT tblSwapParameters_1.SpotMonth;

PS:

[quoted text clipped - 42 lines]

Bill


--
Bill Reed

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

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

You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

--
Duane Hookom
MS Access MVP
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a8b11974c13f@uwe...
Duane,

Now that I've got the simple calculation working, is it possible to
include a
value from a control on a form in the calculation "Expr1"? What are the
restrictions on calculations in a crosstab query? I've already come across
a
problem in using a control on the main form as criteria in a crosstab
query
or even in an underlying query for a crosstab query.

If I can't use data from a control on a form, I will fall back to "Plan I"
and run a make table query based on this crosstab query, and then populate
the records and fields using a calculation in VBA.

Thanks,

Bill

Duane Hookom wrote:
You must always define the data type of all parameters in crosstab
queries.
Select Query-Parameters and enter
[Forms]![frmSwaps]![txtSwapID] datatype

BTW: without reading all your other postings, the solution for your first
posting is to create a query with this sql
TRANSFORM
First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth])
AS
Expr1
SELECT tblSwapParameters.SpotMonth
FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1
GROUP BY tblSwapParameters.SpotMonth
PIVOT tblSwapParameters_1.SpotMonth;

PS:

[quoted text clipped - 42 lines]

Bill


--
Bill Reed

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

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



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

Thanks,

I finally figured out where to set the datatype of the parameter. I had been
looking in query properties, but it's in the Query menu in the toolbar.

Bill

Duane Hookom wrote:
You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

Duane,

[quoted text clipped - 36 lines]

Bill


--
Bill Reed

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

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

Duane,

Now that I've got all the calculations working, I would like to use this
crosstab query as the datasource for a subform. I would prefer a continuous
subform to a datasheet. How do I display in a subform, a datasource whose
field names and the number of fields will change depending on the results of
the crosstab query?

Bill

Duane Hookom wrote:
You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

Duane,

[quoted text clipped - 36 lines]

Bill


--
Bill Reed

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

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

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 Hookom
MS Access MVP
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a8c96c5835c7@uwe...
Duane,

Now that I've got all the calculations working, I would like to use this
crosstab query as the datasource for a subform. I would prefer a
continuous
subform to a datasheet. How do I display in a subform, a datasource whose
field names and the number of fields will change depending on the results
of
the crosstab query?

Bill

Duane Hookom wrote:
You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to
identify the data types of all parameters.

Duane,

[quoted text clipped - 36 lines]

Bill


--
Bill Reed

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

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



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

I wouldn't keep asking if you guys weren't so damn helpful!!! ;-)

Thanks for the advice,

Bill

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


--
Bill Reed

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

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

Duane,

I found a thread that purports to have solved the problem, but I can't seem
to make it work. Here's the thread:

http://www.accessmonster.com/Uwe/For...8dd03e6294fuwe


Here's my code in the On Current event of the main form:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As QueryDef

Set db = CurrentDb

strSQL = "PARAMETERS [Forms]![frmSwaps]![SwapID] Long, [Forms]![frmSwaps]!
[Conf95] IEEEDouble;" & vbCrLf
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 & "GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & "PIVOT CalcMonth;"

Set qdf = db.QueryDefs("qryFwdPrices")
qdf.SQL = strSQL
Me!sfrmFwdPrices.SourceObject = "Query.qryFwdPrices"
Set db = Nothing

End Sub

Can you see where I have strayed from Jesper's example?

Thanks,

Bill

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


--
Bill Reed

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

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

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


--
Bill Reed

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

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

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 Hookom
MS Access MVP
--

"Bill R via AccessMonster.com" u9289@uwe wrote in message
news:5a8de36f0c9bb@uwe...
Duane,

I found a thread that purports to have solved the problem, but I can't
seem
to make it work. Here's the thread:

http://www.accessmonster.com/Uwe/For...8dd03e6294fuwe


Here's my code in the On Current event of the main form:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As QueryDef

Set db = CurrentDb

strSQL = "PARAMETERS [Forms]![frmSwaps]![SwapID] Long, [Forms]![frmSwaps]!
[Conf95] IEEEDouble;" & vbCrLf
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 & "GROUP BY swapid, ..." & vbCrLf
strSQL = strSQL & "PIVOT CalcMonth;"

Set qdf = db.QueryDefs("qryFwdPrices")
qdf.SQL = strSQL
Me!sfrmFwdPrices.SourceObject = "Query.qryFwdPrices"
Set db = Nothing

End Sub

Can you see where I have strayed from Jesper's example?

Thanks,

Bill

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


--
Bill Reed

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

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



 




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 05:46 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.