473,229 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,229 software developers and data experts.

custormizable report using a crosstab query

I want to create a dynamic report using a crosstab query...... pls someone look into my attached example database and help me out to do the report generation....

example is like dis...:

there are 4 combo boxes.. when u select an item from each combo box and click the ViewReport Button then those selected item values should go as parameters to the crosstab query in the vba code subroutine and from that the report should be generated.

pls someone out there help me quickly
Apr 5 '07 #1
6 4790
Rabbit
12,516 Expert Mod 8TB
You'll have to build the SQL string in code and then use that to create a query def that your report is based on. You'll also have to open the report in design view and delete or add fields that the user chose. You can do this using code.

You let them choose fields for the crosstab but you didn't mention anything about letting the user choose whether it will be a row heading, column heading, or value. And if it's a value, what kind of value.
Apr 5 '07 #2
Denburt
1,356 Expert 1GB
You didn't state the version of Access you are using but the following link explains things in detail.


Dynamic Report Using a Crosstab query
Apr 5 '07 #3
I want to create custormizable report using a crosstab query. For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query and then generate the report... here's the code for the crosstab query :-
-----------------------------------------------------
(cboColumns/cboProjectTitle/cboRows/cboIndicator)
These are the combobox parameters pass to the crosstab query after button's being clikced.
------------------------------------------------------------

(sSql = "TRANSFORM Max(IndicatorData.nValue) AS MaxOfnValue " + _
"SELECT " + Trim(Replace(cboRows, "_", ".")) + " " + _
"FROM [Indicator], IndicatorData, Region, Project " + _
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" + cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND Region.nRegionId=IndicatorData.nRegionId " + _
"GROUP BY IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " " + _
"PIVOT Format(" + Trim(Replace(cboColumns, "_", ".")) + ",'yyyy') "
)

pls someone help me out to do this report or tell me is there a way to do this kind of requirement(this is a real customer requirement).
Apr 6 '07 #4
Rabbit
12,516 Expert Mod 8TB
Please do not double post. And do try to respond to the original thread when someone has offered some help.

MODERATOR
Apr 6 '07 #5
I want to create custormizable report using a crosstab query.For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query and then what i did was i created a temparary table and insert the values into that temp table using the crosstab query. now what i want is to generate the report using that temp table but every time user selects different row headings and column headings then the report will be changed. so i found a way to create a report using VBA code but at the moment im stuck with dat code.... here's the code for the crosstab query and the temp table and the code for generate the report. :-
-----------------------------------------------------
(cboColumns/cboProjectTitle/cboRows/cboIndicator)
These are the combobox parameters pass to the crosstab query after button's being clikced.
------------------------------------------------------------
Cross Tab Query

(sSql = "TRANSFORM Max(IndicatorData.nValue) AS MaxOfnValue " + _
"SELECT " + Trim(Replace(cboRows, "_", ".")) + " " + _
"FROM [Indicator], IndicatorData, Region, Project " + _
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" + cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND Region.nRegionId=IndicatorData.nRegionId " + _
"GROUP BY IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " " + _
"PIVOT Format(" + Trim(Replace(cboColumns, "_", ".")) + ",'yyyy') "
)
-------------------------------------------------------------------------------------------------------------------
Temp Table

Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Dim recCrossTab As Recordset
Dim sString, sFields, sValues As String
sString = "CREATE TABLE X ("
Set recCrossTab = dbCurr.OpenRecordset(CrossTabSql)
For i = 0 To recCrossTab.Fields.Count - 1
If i = recCrossTab.Fields.Count - 1 Then
sString = sString + recCrossTab.Fields(i).Name + " Text(50) "
sFields = sFields + recCrossTab.Fields(i).Name
Else
sString = sString + recCrossTab.Fields(i).Name + " Text(50), "
sFields = sFields + recCrossTab.Fields(i).Name + ", "

End If
Next

sString = sString + ") "
Debug.Print sString
DoCmd.RunSQL "Drop Table X"
DoCmd.RunSQL sString


Dim InsertStr As String
InsertStr = "INSERT INTO X (" + sFields + ") values ("

''InsertStr = Replace(InsertStr,"'",""")

recCrossTab.MoveFirst

While (recCrossTab.EOF = False And recCrossTab.BOF = False)
For b = 0 To recCrossTab.Fields.Count - 1
If b = recCrossTab.Fields.Count - 1 Then
sValues = sValues + "'" + CStr(recCrossTab.Fields(recCrossTab.Fields(b).Name )) + "' "
Else
sValues = sValues + "'" + CStr(recCrossTab.Fields(recCrossTab.Fields(b).Name )) + "', "
End If

Next
InsertStr = "INSERT INTO X (" + sFields + ") values (" + sValues + ") "
Debug.Print InsertStr

DoCmd.SetWarnings False
DoCmd.RunSQL InsertStr

InsertStr = "INSERT INTO X (" + sFields + ") values ("
sValues = ""

recCrossTab.MoveNext

Wend
-------------------------------------------------------------------------------------------------------------------

Creating the Report using the VBA Code. This Code is in the Code View of the Report called Custom Report. (Code is taken from:- http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch15.mspx)

Private Property Let CustomReport_Source(RHS As String)
' Create report based on specified data source.
Dim txtNew As Access.TextBox
Dim lblNew As Access.Label
Dim rstSource As ADODB.Recordset
Dim fldData As ADODB.Field
Dim lngTop As Long
Dim lngLeft As Long
lngLeft = 0
lngTop = 0
' Set report's RecordSource property.
Me.Report.RecordSource = RHS
' Open recordset on specified record source.
Set rstSource = New ADODB.Recordset
rstSource.Open "SELECT * FROM [" & RHS & "];", _
CurrentProject.Connection, adOpenForwardOnly
' Create corresponding label and text box controls for each field.
For Each fldData In rstSource.Fields
' Create new text box control and size to fit data.
Set txtNew = CreateReportControl(Me.Report.Name, acTextBox, _
acDetail, , fldData.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit
' Create new label control and size to fit data.
Set lblNew = CreateReportControl(Me.Report.Name, acLabel, acDetail, _
txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
' Increment top value for next control.
lngTop = lngTop + txtNew.Height + 25
Next
CustomReport_Source_End:
On Error Resume Next
rstSource.Close
Set rstSource = Nothing
Exit Property
End Property
-------------------------------------------------------------------------------------------------------------------
In here we can send the created temp table but I cant access the above property. so i created a sub-routine. and call that in the following Sub Report_Open() method. But It gives an error like this "U must be in design view to create or delete Controls".


Private Sub Report_Open(Cancel As Integer)
CustomReport_Source("X")
End Sub

pls someone help me out or please tell me is there any other way to do this kind of requirement.
Apr 10 '07 #6
Rabbit
12,516 Expert Mod 8TB
Just like the error says, you have to open the report in design view to use the code that creates the report. acViewDesign instead of acViewPreview.
Apr 10 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
3
by: Edward | last post by:
ACCESS 2k I need to design a report based on a rota system for staff at various shops. The data is effectively stored in a single table, along the lines of: Initials (e.g. BH, FG, RM etc.)...
4
by: No Spam | last post by:
Dear Access 2000 users, I have a crosstab query that puts together certain information perfectly. It has a criteria that is based on a form that limits how many columns are returned based on...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
3
by: deejayquai | last post by:
Hi I've created a crosstab query and displayed it as a sub-report in my main report. This is fine until the data changes and the column names become incorrect. I know I have to create a...
3
by: Bryan | last post by:
I've been looking over this newsgroup, but I can't find an answer to my problem. I see that a few others have posted this issue over the years, but none of them got a response. I'll give it...
1
by: Brad | last post by:
Thanks for taking the time to read my question. I have a table of data that has Date, Data and Category. I need to show, in a report, each Categories Data by Date. The Date has to be it's own...
2
AccessIdiot
by: AccessIdiot | last post by:
Hello all, I have a report that is based on a crosstab query. One of the fields in the crosstab query is based on a query. It is a coded value. When I look at the crosstab query it displays the...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.