Here is a sample of the union query. I am trying to take the columns
and
row
and transpose. The information for this union query comes from a sub
(or
nested) SUM IIF query, where only one row of data is present and not
from
a
table (could this be the issue?). This has not worked because the
error
message regarding missing operand occurs. when I place the brackets
around
each of the field names and run the query, all that data appears in one
column named for the first field at the top of the sql statement (Total
SectionA Issues).
example:
Total SectionA Issues
5
4
3
etc...
I want the names of each field to reside in one column, with their
corresponding data appearing right next to the name of the field.
so instead of the data appearing as in the example below:
Month Total SectionA Issues Total SectionB Issues Total
Section C Iss
Dec 2005 5 4
3
I would like to have the data appear like this:
Issue Fields Dec 2005
Total SectionA Issues 5
Total SectionB Issues 4
Total SectionC Issues 3
and so on...
:
I assume your union query will properly normalize your table. Now, you
should be able to create a Crosstab query based on your union query.
Can
you
provide the SQL of your union query, a few sample records from the
union
query, and how you want these to finally appear?
--
Duane Hookom
MS Access MVP
--
message
ok, i tried it and it did not work. what the union query does is
give
me
the
information I want but in only one column. in essence, the
information
is
transferred to one column instead of rows like i need it to be.
I found this sample code that I am trying to use in a module that is
supposed to transpose column to row, but I am getting errors also as
it
is
not referencing the 1st line of code. here is the code referenced
from
article 182882 from the microsoft website.
http://support.microsoft.com/kb/182822/en-us
Function Transposer(strSource As String, strTarget As String)
Dim db As Database
Dim tdfNewDef As TableDef
Dim fldNewField As Field
Dim rstSource As Recordset, rstTarget As Recordset
Dim i As Integer, j As Integer
On Error GoTo Transposer_Err
Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast
' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To rstSource.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1),
dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef
' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
For i = 0 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(i).Name
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j
db.Close
Exit Function
Transposer_Err:
Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select
Exit Function
End Function
:
I will try that, thanks for the help. Will let you guys know what
happens
:
I second that observation...
--
Duane Hookom
MS Access MVP
--
message
On Wed, 4 Jan 2006 14:16:03 -0800, "saschamps9903"
"Syntax error (missing operator) in query expression "total
A-Hud1
Issues"
This probably means you have a field, or a table, named A-Hudl
Issues
(or some other name containing blanks or special characters).
Enclose all of your table and fieldnames in the query in
[Square
brackets] - otherwise Access will assume that A-Hudl is one
thing,
and
Issues is some different thing, and thereby get confused.
John W. Vinson[MVP]
