Transposing columns and rows

[Lee Stafford via AccessMonster.com]

I have a a report that has all the Tech ID's along the side and then the
data headings are along the top. I need to switch these. I need the Tech
ID's along the top and all of the data headings down the side of the report.

Can I do this?

TIA,

Lee

 

[Duane Hookom]

Usually this involves creating a union query from your record source query
and then a crosstab from the union query. If you need more help, please
provide table and field names as well as sample records and desired display
in the report.

 

[Lee Stafford via AccessMonster.com]

Thanks Duane,

The table is rather large with very similar fields.

QRY_Evaluation
SELECT QRY_Evaluation.Month, QRY_Evaluation.DistrictName,
QRY_Evaluation.System, QRY_Evaluation.[Supervisor's Name],
QRY_Evaluation.TechID, QRY_Evaluation.[Technician's Name], Count(IIf([a1]=-
1,[a1])) AS CT_OF_A1, Count(IIf([a2]=-1,[a2])) AS CT_OF_A2, Count(IIf([a3]=-
1,[a3])) AS CT_OF_A3, Count(IIf([a4]=-1,[a4])) AS CT_OF_A4, Count(IIf([a5]=-
1,[a5])) AS CT_OF_A5, Count(IIf([a6]=-1,[a6])) AS CT_OF_A6, Count(IIf([a7]=-
1,[a7])) AS CT_OF_A7, Count(IIf([a8]=-1,[a8])) AS CT_OF_A8, Count(IIf([a9]=-
1,[a9])) AS CT_OF_A9, Count(IIf([a10]=-1,[a10])) AS CT_OF_A10, Count(IIf(
[a11]=-1,[a11])) AS CT_OF_A11, Count(IIf([a12]=-1,[a12])) AS CT_OF_A12,
Count(IIf([a13]=-1,[a13])) AS CT_OF_A13, Count(IIf([a14]=-1,[a14])) AS
CT_OF_A14, Count(IIf([a15]=-1,[a15])) AS CT_OF_A15, Count(IIf([a16]=-1,[a16]
)) AS CT_OF_A16, Count(IIf([a17]=-1,[a17])) AS CT_OF_A17, Count(IIf([a18]=-
1,[a18])) AS CT_OF_A18, Count(IIf([a19]=-1,[a19])) AS CT_OF_A19, Count(IIf(
[b1]=-1,[b1])) AS CT_OF_b1, Count(IIf([b2]=-1,[b2])) AS CT_OF_b2, Count(IIf
([b3]=-1,[b3])) AS CT_OF_b3, Count(IIf([b4]=-1,[b4])) AS CT_OF_b4, Count
(IIf([b5]=-1,[b5])) AS CT_OF_b5, Count(IIf([b6]=-1,[b6])) AS CT_OF_b6,
Count(IIf([b7]=-1,[b7])) AS CT_OF_b7, Count(IIf([b8]=-1,[b8])) AS CT_OF_b8,
Count(IIf([b9]=-1,[b9])) AS CT_OF_b9, Count(IIf([b10]=-1,[b10])) AS
CT_OF_b10, Count(IIf([b11]=-1,[b11])) AS CT_OF_b11, Count(IIf([b12]=-1,[b12]
)) AS CT_OF_b12, Count(IIf([b13]=-1,[b13])) AS CT_OF_b13, Count(IIf([b14]=-
1,[b14])) AS CT_OF_b14, Count(IIf([b15]=-1,[b15])) AS CT_OF_b15, Count(IIf(
[b16]=-1,[b16])) AS CT_OF_b16, Count(IIf([b17]=-1,[b17])) AS CT_OF_b17,
Count(IIf([c1]=-1,[c1])) AS CT_OF_c1, Count(IIf([c2]=-1,[c2])) AS CT_OF_c2,
Count(IIf([c3]=-1,[c3])) AS CT_OF_c3, Count(IIf([c4]=-1,[c4])) AS CT_OF_c4,
Count(IIf([c5]=-1,[c5])) AS CT_OF_c5, Count(IIf([c6]=-1,[c6])) AS CT_OF_c6,
Count(IIf([c7]=-1,[c7])) AS CT_OF_c7, Count(IIf([c8]=-1,[c8])) AS CT_OF_c8,
Count(IIf([c9]=-1,[c9])) AS CT_OF_c9, Count(IIf([c10]=-1,[c10])) AS
CT_OF_c10, Count(IIf([c11]=-1,[c11])) AS CT_OF_c11
FROM QRY_Evaluation
WHERE (((QRY_Evaluation.Month) Like [Forms]![FRM_ItemsMissedForm]![Month] &
"*") AND ((QRY_Evaluation.System) Like [Forms]![FRM_ItemsMissedForm]!
[System] & "*") AND ((QRY_Evaluation.TechID) Like [Forms]!
[FRM_ItemsMissedForm]![TechID] & "*"))
GROUP BY QRY_Evaluation.Month, QRY_Evaluation.DistrictName,
QRY_Evaluation.System, QRY_Evaluation.[Supervisor's Name],
QRY_Evaluation.TechID, QRY_Evaluation.[Technician's Name]
ORDER BY QRY_Evaluation.System, QRY_Evaluation.TechID;


Display

Month
DistrictName
System
TechID TechID TechID TechID TechID TechID......
Ct_of_a1 Ct_of_a1 Ct_of_a1
Ct_of_a2 Ct_of_a2 Ct_of_a2

 

[Duane Hookom]

If you actually have all these fields, I would probably stop all development
on this system until it had been determined if the application could be
normalized.

I also create all IIf() statements with three arguments, not two as you have
done.
Count(IIf([a1]=-1,[a1],Null)) AS CT_OF_A1,
or
Sum(Abs([a1]=-1)) AS CT_OF_A1,


--
Duane Hookom
MS Access MVP
--

Thanks Duane,

The table is rather large with very similar fields.

QRY_Evaluation
SELECT QRY_Evaluation.Month, QRY_Evaluation.DistrictName,
QRY_Evaluation.System, QRY_Evaluation.[Supervisor's Name],
QRY_Evaluation.TechID, QRY_Evaluation.[Technician's Name],
Count(IIf([a1]=-
1,[a1])) AS CT_OF_A1, Count(IIf([a2]=-1,[a2])) AS CT_OF_A2,
Count(IIf([a3]=-
1,[a3])) AS CT_OF_A3, Count(IIf([a4]=-1,[a4])) AS CT_OF_A4,
Count(IIf([a5]=-
1,[a5])) AS CT_OF_A5, Count(IIf([a6]=-1,[a6])) AS CT_OF_A6,
Count(IIf([a7]=-
1,[a7])) AS CT_OF_A7, Count(IIf([a8]=-1,[a8])) AS CT_OF_A8,
Count(IIf([a9]=-
1,[a9])) AS CT_OF_A9, Count(IIf([a10]=-1,[a10])) AS CT_OF_A10, Count(IIf(
[a11]=-1,[a11])) AS CT_OF_A11, Count(IIf([a12]=-1,[a12])) AS CT_OF_A12,
Count(IIf([a13]=-1,[a13])) AS CT_OF_A13, Count(IIf([a14]=-1,[a14])) AS
CT_OF_A14, Count(IIf([a15]=-1,[a15])) AS CT_OF_A15,
Count(IIf([a16]=-1,[a16]
)) AS CT_OF_A16, Count(IIf([a17]=-1,[a17])) AS CT_OF_A17,
Count(IIf([a18]=-
1,[a18])) AS CT_OF_A18, Count(IIf([a19]=-1,[a19])) AS CT_OF_A19,
Count(IIf(
[b1]=-1,[b1])) AS CT_OF_b1, Count(IIf([b2]=-1,[b2])) AS CT_OF_b2,
Count(IIf
([b3]=-1,[b3])) AS CT_OF_b3, Count(IIf([b4]=-1,[b4])) AS CT_OF_b4, Count
(IIf([b5]=-1,[b5])) AS CT_OF_b5, Count(IIf([b6]=-1,[b6])) AS CT_OF_b6,
Count(IIf([b7]=-1,[b7])) AS CT_OF_b7, Count(IIf([b8]=-1,[b8])) AS
CT_OF_b8,
Count(IIf([b9]=-1,[b9])) AS CT_OF_b9, Count(IIf([b10]=-1,[b10])) AS
CT_OF_b10, Count(IIf([b11]=-1,[b11])) AS CT_OF_b11,
Count(IIf([b12]=-1,[b12]
)) AS CT_OF_b12, Count(IIf([b13]=-1,[b13])) AS CT_OF_b13,
Count(IIf([b14]=-
1,[b14])) AS CT_OF_b14, Count(IIf([b15]=-1,[b15])) AS CT_OF_b15,
Count(IIf(
[b16]=-1,[b16])) AS CT_OF_b16, Count(IIf([b17]=-1,[b17])) AS CT_OF_b17,
Count(IIf([c1]=-1,[c1])) AS CT_OF_c1, Count(IIf([c2]=-1,[c2])) AS
CT_OF_c2,
Count(IIf([c3]=-1,[c3])) AS CT_OF_c3, Count(IIf([c4]=-1,[c4])) AS
CT_OF_c4,
Count(IIf([c5]=-1,[c5])) AS CT_OF_c5, Count(IIf([c6]=-1,[c6])) AS
CT_OF_c6,
Count(IIf([c7]=-1,[c7])) AS CT_OF_c7, Count(IIf([c8]=-1,[c8])) AS
CT_OF_c8,
Count(IIf([c9]=-1,[c9])) AS CT_OF_c9, Count(IIf([c10]=-1,[c10])) AS
CT_OF_c10, Count(IIf([c11]=-1,[c11])) AS CT_OF_c11
FROM QRY_Evaluation
WHERE (((QRY_Evaluation.Month) Like [Forms]![FRM_ItemsMissedForm]![Month]
&
"*") AND ((QRY_Evaluation.System) Like [Forms]![FRM_ItemsMissedForm]!
[System] & "*") AND ((QRY_Evaluation.TechID) Like [Forms]!
[FRM_ItemsMissedForm]![TechID] & "*"))
GROUP BY QRY_Evaluation.Month, QRY_Evaluation.DistrictName,
QRY_Evaluation.System, QRY_Evaluation.[Supervisor's Name],
QRY_Evaluation.TechID, QRY_Evaluation.[Technician's Name]
ORDER BY QRY_Evaluation.System, QRY_Evaluation.TechID;


Display

Month
DistrictName
System
TechID TechID TechID TechID TechID TechID......
Ct_of_a1 Ct_of_a1 Ct_of_a1
Ct_of_a2 Ct_of_a2 Ct_of_a2

 

[Lee Stafford via AccessMonster.com]

Yes, it is normalized. 2nd normal form, I think.
Each of the lettered fields are yes/no questions on an inspection sheet.
There are 4 sections to the sheet, which is why I have a1, b1,c1,d1.
I thought that in sql, if the third part of the IIf werent explicit, then
it was assumed to be null.

I also have another question concerning your applet for QBF. I have posted
it and had emailed you before I realized that you answered problems on
here. I keep getting a 13:Type mismatch in cmdReportClick(). Can you help
with this too?

 

[Duane Hookom]

Your table isn't fully normalized. Did you look at my "At Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

If you are getting an error in the QBF applet, I expect you don't have a
reference set to the Microsoft DAO library. You would need to open any
module and select Tools->References and find & check "Microsoft DAO 3.x..."
then click OK and exit the references. Then compile the application to check
for errors.

 

[Lee Stafford via AccessMonster.com]

Thanks Duane,

I didn't realize that it wasn't normalized. I just wasn't thinking, I
guess. When I saw your applet "At your Survey", I realized right away how
far off it was. I am going to go back and reorganized the table and model
it similiar to yours. Thanks again. I can't tell you how much I
appreciate this.

I will check on the reference for my other form.

Lee

 

[Lee Stafford via AccessMonster.com]

Thanks again Duane,

I am restructuring my table.

On the other problem with the QBF demo, I did find that I was missing a
reference. Now I have found that I can't use the "In" operator to specify
a list of criteria. It will always separate the criteria with a single
quote, even if it is a number. Is there a way I can change the code to
make this change? I actually can't even use it for text. I tried it with
my month field and it needs to be separated by double quotes instead of
single. Can you help me with this one too?

Thanks, I really appreciate it.

Lee

 

[Duane Hookom]

Regarding the delimiters, this will depend on the field data type in your
query. If you open your source query (<query name>) in datasheet view, are
the columns you expect to be numeric all right aligned? Are all the dates
right aligned?

There is code in there that you can change but you should answer the above
questions prior to mucking up the code.

 

[Lee Stafford via AccessMonster.com]

I can't get the "In" operator to work in your db either, when I try to
input a number instead of text. It get a 3075: Syntax Error(Missing
Operator) in query expression [EmployeeID]In( 5','8 ).

I can do the text fine, but I can't figure out how to do the numbers. I
checked both your db and mine to make sure that the numbers are defined as
such, and not as text. What am I doing wrong?

Lee

 

[Duane Hookom]

I asked you before but you ignored my question "Is EmployeeID left or
right-aligned when you open the source query in datasheet view?"

There is code that should determine this and use the after update of the
field to place quotes as needed.

 

[Lee Stafford via AccessMonster.com]

It is the EmployeeID in your db. It is right justified.

 

[Lee Stafford via AccessMonster.com]

I have gone in and double checked my tables as well. Although my TechID
field is a number, it is not right justified. I can not figure out how to
get it right justified, either. I'm sorry, I don't understand why that is.

 

[Lee Stafford via AccessMonster.com]

When I say the TechID is a number, I mean its datatype is that of NUMBER.
The field size is long integer. Yet it is still not right justified.

 

[Duane Hookom]

What is the SQL of your query? Are you using IIf() or Nz() or a union query
or something?

 

[Lee Stafford via AccessMonster.com]

Please nevermind the fields a1 through d2. They are from the table that I
still have to re-organize.


SQL as follows:

SELECT TBL_LOCATION.District, TBL_LOCATION.System, TBL_SUPERVISOR!
SupervisorFirstName & " " & TBL_SUPERVISOR!SupervisorLastName AS
[Supervisor's Name], TBL_QA_WO_INFO.WONumber, TBL_QA_WO_INFO.TechID,
TBL_TECH.TechnicianFirstName & " " & [TechnicianLastName] AS [Technician's
Name], TBL_QA_WO_INFO.QATechID, TBL_QA_WO_INFO.JobNumber,
TBL_QA_WO_INFO.JobType, TBL_JOB_TYPE.CorrespondingType,
TBL_QA_WO_INFO.Street, TBL_QA_WO_INFO.City, TBL_QA_WO_INFO.State,
TBL_QA_WO_INFO.WODate, Format(TBL_QA_WO_INFO!WODate,"mmmm") AS [Month],
Format(TBL_QA_WO_INFO!WODate,"mm") AS MonthNm, TBL_QA_WO_INFO.ReturnTrip,
TBL_QA_WO_INFO.MRO, TBL_QA_WO_INFO.MRONotes, TBL_QA_WO_INFO.Notes,
TBL_QA_WO_INFO.Score, TBL_QA_WO_INFO.A1, TBL_QA_WO_INFO.A2,
TBL_QA_WO_INFO.A3, TBL_QA_WO_INFO.A4, TBL_QA_WO_INFO.A5, TBL_QA_WO_INFO.A6,
TBL_QA_WO_INFO.A7, TBL_QA_WO_INFO.A8, TBL_QA_WO_INFO.A9,
TBL_QA_WO_INFO.A10, TBL_QA_WO_INFO.A11, TBL_QA_WO_INFO.A12,
TBL_QA_WO_INFO.A13, TBL_QA_WO_INFO.A14, TBL_QA_WO_INFO.A15,
TBL_QA_WO_INFO.A16, TBL_QA_WO_INFO.A17, TBL_QA_WO_INFO.A18,
TBL_QA_WO_INFO.A19, TBL_QA_WO_INFO.B1, TBL_QA_WO_INFO.B2,
TBL_QA_WO_INFO.B3, TBL_QA_WO_INFO.B4, TBL_QA_WO_INFO.B5, TBL_QA_WO_INFO.B6,
TBL_QA_WO_INFO.B7, TBL_QA_WO_INFO.B8, TBL_QA_WO_INFO.B9,
TBL_QA_WO_INFO.B10, TBL_QA_WO_INFO.B11, TBL_QA_WO_INFO.B12,
TBL_QA_WO_INFO.B13, TBL_QA_WO_INFO.B14, TBL_QA_WO_INFO.B15,
TBL_QA_WO_INFO.B16, TBL_QA_WO_INFO.B17, TBL_QA_WO_INFO.C1,
TBL_QA_WO_INFO.C2, TBL_QA_WO_INFO.C3, TBL_QA_WO_INFO.C4, TBL_QA_WO_INFO.C5,
TBL_QA_WO_INFO.C6, TBL_QA_WO_INFO.C7, TBL_QA_WO_INFO.C8, TBL_QA_WO_INFO.C9,
TBL_QA_WO_INFO.C10, TBL_QA_WO_INFO.C11, TBL_QA_WO_INFO.D1, TBL_QA_WO_INFO.D2
FROM (TBL_SUPERVISOR INNER JOIN (TBL_LOCATION INNER JOIN TBL_TECH ON
TBL_LOCATION.System=TBL_TECH.System) ON
TBL_SUPERVISOR.SupervisorID=TBL_TECH.SupervisorID) INNER JOIN (TBL_JOB_TYPE
INNER JOIN (TBL_QA_TECH INNER JOIN TBL_QA_WO_INFO ON
TBL_QA_TECH.QATechID=TBL_QA_WO_INFO.QATechID) ON
TBL_JOB_TYPE.JobType=TBL_QA_WO_INFO.JobType) ON
TBL_TECH.TechID=TBL_QA_WO_INFO.TechID
ORDER BY TBL_QA_WO_INFO.WONumber;

 

[Lee Stafford via AccessMonster.com]

I have been trying the "In" operator in the db that is supplied with the
QBF and no matter what field I use, it always separates it with a ',' It
looks like it puts the correct delimiter at the beginning and the end, but
it will not separate the values correctly. I tried the BirthDate field in
the db, and it works the same. I am not so sure that it is my data, maybe
it is the way I am entering it into the field?

Lee

 

[Duane Hookom]

You may need to set a break point and use debug to print in this section of
code:
Private Function DelimitIt(pText As String) As String
On Error GoTo DelimitIt_Err
Dim i As Integer
i = 1
Do While InStr(i, pText, ",") > 0
i = InStr(i, pText, ",")
If Mid$(pText, i - 1, 1) <> "'" Then
pText = Left$(pText, i - 1) & "'" & Mid$(pText, i, 1) &
Mid$(pText, i + 1)
End If
i = i + 2
Loop
i = 1
Do While InStr(i, pText, ",") > 0
i = InStr(i, pText, ",")
If Mid$(pText, i + 1, 1) <> "'" Then
pText = Left$(pText, i) & "'" & Mid$(pText, i + 1)
End If
i = i + 1
Loop
DelimitIt = pText

DelimitIt_Exit:
On Error Resume Next
Exit Function

DelimitIt_Err:
MsgBox Err & ": " & Error$, 16, "Error in DelimitIt"
Resume DelimitIt_Exit:

End Function

And this function:
Private Function OpVisible(pControl As Control)
On Error GoTo OpVisible_Err
Dim strErrMsg As String 'For Error Handling

If Len(pControl & "") > 0 Then
Dim db As DAO.Database
Dim strDel As String
Dim strField As String
Set db = CurrentDb
Dim strFld As String
strField = pControl
Select Case
db.QueryDefs(Forms![frmQBFSource]![cboSource]).Fields(strField).Type
Case dbText
strDel = """"
Case dbDate
strDel = "#"
Case Else
strDel = " "
End Select
Me("txtDelimiter") = strDel
End If


OpVisible_Exit:
Exit Function

OpVisible_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description
MsgBox strErrMsg, vbInformation, "OpVisible"
Resume OpVisible_Exit
End Select
End Function

 

[Lee Stafford via AccessMonster.com]

Thanks Duane,

I clipped out those bits of code and pasted them over the old functions
that were already in the module. The first function looked the same, the
Opvisible function was slightly different.

After pasting them in and compiling the db again, I tried the same
examples. It still has the same error.

Just to remove all doubt that it may be my data, I am trying it in the db
that was supplied with the QBF demo. I can't get it to work in there
either. Even with the different code that you just gave me.

Still lost,

Lee

 

[Duane Hookom]

Add a break point to the code to see what is happening.