Questions about CrossTab Query

[keawee]

Hello,

I have several questions concerning the crosstab query. I created a crosstab query which take data since a table (I have a form with several listbox and independent fields and I send my selections to a table by a query).

I replaced in my crosstab query the fields null by zeros and I inserted a table to have in my query all my unit. I have a query which is made up as this but the number of column after Total can vary:

Unit-Total-<>-Renault-Peugeot-Ford

I have this column < > because of the insertion as of the my table. I could remove it by masking this column.

My question;

- it Is possible to make a query to make total for all my columns like this....my columns after total can vary.
Total -- Renault -- Peugeot -- Ford etc..

- it Is possible to create a report dynamically because my columns after total can vary but how to make it?.

Thank you for your assistance because I am blocked.

Keawee

PS: Here the SQL of my query if that can help you.

TRANSFORM NullDonneZéro(Count([Table: Requete resultat selection].Serie)) AS CompteDeSerie
SELECT IIf(IsNull([TblCodeReparationUnique]![CodeReparationsCourt]),"AUTRES",[TblCodeReparationUnique]![CodeReparationsCourt]) AS [Module], Count([Table: Requete resultat selection].Serie) AS Total
FROM TblCodeReparationUnique LEFT JOIN [Table: Requete resultat selection] ON TblCodeReparationUnique.CodeReparationsCourt = [Table: Requete resultat selection].CodeReparationsCourt
GROUP BY IIf(IsNull([TblCodeReparationUnique]![CodeReparationsCourt]),"AUTRES",[TblCodeReparationUnique]![CodeReparationsCourt])
PIVOT [Table: Requete resultat selection].Type;

- I attach 1 picture from ma cross tab query.

 

[Pat Hartman]

Download the solutions database. It has an example of a dynamic crosstab. Be prepared to dive into code.

http://msdn.microsoft.com/library/d...n-us/bapp2000/html/mdbdownload.asp?frame=true

 

[keawee]

Thanks for your help Pat,

I just have one question about the code in the Microsoft Web Site.

In window VBA, there is a document which names Report_EmployeeSales. I have a problem with a part of the code which I modified but in what relates to me, I do not have a form like in the example. I want simply to recover the data of my crosstab query.

Here the Microsoft code:

Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Don't open report if EmployeeSalesDialogBox form isn't loaded.
If Not (IsLoaded("EmployeeSalesDialogBox")) Then
Cancel = True
MsgBox "To preview or print this report, you must open " _
& "EmployeeSalesDialogBox in Form view.", vbExclamation, _
"Must Open Dialog Box"
Exit Sub
End If

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!EmployeeSalesDialogBox
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("EmployeeSales")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!EmployeeSalesDialogBox!BeginningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!EmployeeSalesDialogBox!EndingDate") _
= frm!EndingDate

' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub


Here my code, hum hum :

Private Sub Report_Open(Cancel As Integer)

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb

Set qdf = dbsReport.QueryDefs("MyCrossQuery")

' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub

I inserted the whole of the functions while modifying well on the code according to my data. My problem is that I have data in my crosstab query but not in my.

Thanks for your help,

Keawee