Duane Hookom - Crosstab.mdb by Corporate Technologies

[R.T.]

Mr. Hookom.

I've read many of your posts over the years, and based on your
encouragement to another poster to use the Crosstab.mdb I gave it a
shot. My issue is that my requirements are much less cumbersome, and
I'm having trouble paring the code in the module down to work with my
data. I'm hoping that you can point me in the correct direction.

My data all resides in a single table. Basically, it contains the
line number of a bid item, the bidder name, the bid amount, and the
code.

The key is a composite key consisting of LineNumber,BidderName.

Each line number is assigned a 'code' number. I.E., Code 20 might be
concrete paving. Of course the description is pulled from a different
table.

What I'm interested in is a report which uses the bidder names for the
colum headings. Keep in mind that we never know who will bid, or how
many bidders there will be.

The rows will be the sum of their bid for each code calculated from
the line items.

I appreciate any suggestions you may provide.

Rodney Tatum, MCSE

 

[Duane Hookom]

If you have a dynamic list of column headings, consider using the sample
found in Crosstab.mdb at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The bidder name would
replace the Customer in the sample. I assume the line would be like the date
from the sample. The Amount would be displayed in the text box.

 

[R.T.]

Thanks for the quick reply.

That is exactly what I've done. The issue is that I'm getting the
alias table populated correctly with VenderName, but the Alias is all
"a's".

I haven't cleaned it up yet, I'm just trying to get it to work. Note
that lngEmpID is changed to a string in order to use it with the
vendor names.

I've included my code from the module below, if you are interested.

Function UpdateEmpCustAlias(pbytNumColumns As Byte) As Long
'============================================================
' Purpose:
' Copyright: 1999 Business Results
' Company: Business Results
' Phone: 715-835-8130
' E-Mail: (e-mail address removed)
' Programmer: Duane Hookom
' Called From:
' Date: 1/22/00
' Parameters:
'============================================================
On Error GoTo UpdateEmpCustAlias_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
'Dim bytLevel As Byte
Dim lngEmpID As String
Dim bytMaxColumns As Byte

Dim db As Database
Dim rs As Recordset


strSQL = "Delete * from tblBidAnalysisAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.OpenQuery "qryBidAnalysisAlias" 'create table of unique
combinations of employee and customer
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("tblBidAnalysisAlias") 'table used to
redefine/alias the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngEmpID = !VendorName
'bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !VendorName = lngEmpID
.Edit
'!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A -
whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
'bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

UpdateEmpCustAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

UpdateEmpCustAlias_Err:
Select Case Err
Case Else
UpdateEmpCustAlias = Err.Number
Resume UpdateEmpCustAlias_Exit
End Select

End Function

 

[Duane Hookom]

You might want to check the sort order of qryBidAnalysisAlias. I should have
probably included a reliable sort on the VendorName field.

--
Duane Hookom
MS Access MVP
--

Thanks for the quick reply.

That is exactly what I've done. The issue is that I'm getting the
alias table populated correctly with VenderName, but the Alias is all
"a's".

I haven't cleaned it up yet, I'm just trying to get it to work. Note
that lngEmpID is changed to a string in order to use it with the
vendor names.

I've included my code from the module below, if you are interested.

Function UpdateEmpCustAlias(pbytNumColumns As Byte) As Long
'============================================================
' Purpose:
' Copyright: 1999 Business Results
' Company: Business Results
' Phone: 715-835-8130
' E-Mail: (e-mail address removed)
' Programmer: Duane Hookom
' Called From:
' Date: 1/22/00
' Parameters:
'============================================================
On Error GoTo UpdateEmpCustAlias_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
'Dim bytLevel As Byte
Dim lngEmpID As String
Dim bytMaxColumns As Byte

Dim db As Database
Dim rs As Recordset


strSQL = "Delete * from tblBidAnalysisAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.OpenQuery "qryBidAnalysisAlias" 'create table of unique
combinations of employee and customer
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("tblBidAnalysisAlias") 'table used to
redefine/alias the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngEmpID = !VendorName
'bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !VendorName = lngEmpID
.Edit
'!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A -
whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
'bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

UpdateEmpCustAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

UpdateEmpCustAlias_Err:
Select Case Err
Case Else
UpdateEmpCustAlias = Err.Number
Resume UpdateEmpCustAlias_Exit
End Select

End Function

 

[R.T.]

That particular query is working fine. It populates my
tblBidAnalysisAlias with a list of unique bider names.

The problem starts when I try to assign the unique letters to each
bider. They all end up with 'A', giving me a single colum instead one
for each.

 

[Duane Hookom]

Try change this line of code (assuming vendorname is your column heading):
Set rs = db.OpenRecordset("tblBidAnalysisAlias")
to
strSQL = "SELECT * FROM tblBidAnalysisAlias " & _
" ORDER BY VendorName"
Set rs = db.OpenRecordset(strSQL)

I also assume you are sending in a number of columns matching your report.

--
Duane Hookom
MS Access MVP
--

That particular query is working fine. It populates my
tblBidAnalysisAlias with a list of unique bider names.

The problem starts when I try to assign the unique letters to each
bider. They all end up with 'A', giving me a single colum instead one
for each.

 

[R.T.]

As I looked through the code, I noticed that intAlias what being set
back to 65 "A" each time it looped. I moved that above the first Do
While loop, and now I have a nice populated table with vendor name,
and a unique letter.

What I don't see, is how you are getting your crosstab query to
provide headings of a-h even if those don't exist.

 

[R.T.]

Ah. Column Headings.