Report from Crosstab Query

[Tanya Sweeney via AccessMonster.com]

I have a crosstab query that shows volume per part number scheduled to ship
by ship week (1 through 52). I set my column headings to 1-52 and that
works fine.

I have a report that has one page per part number and shows individual
orders in the detail section.

I would like to put a subreport of the crosstab info for the respective
part number into the page footer.

I would prefer to only show columns for weeks that will have a shipment,
since most shipments will be in thousands or millions and a page is only so
wide.

Also I need pretty detailed instructions because I have limited experience
with crosstab queries and zero experience with subreports and crosstab
queries in reports.

Mr. Hookum, I noticed that you are personally answering a lot of these
questions today. I downloaded your sample crosstab reports last Thursday.
They look really cool and functional. I poked around at them in the
preview and design views and tried to figure out how things work, but I
lack the experience and expertise that would allow me to understand which
one does what and how to make my database do that without some narrative to
go along explaining what does what and how. Is there such a thing, and if
so, how may I find it?

Any help you could offer would be greatly appreciated.

Much thanks - Tanya

 

[Duane Hookom]

There is a table in the Crosstab.mdb that explains how the report works. I
actually need to refresh my brain when creating new crosstab reports.

 

[Tanya Sweeney via AccessMonster.com]

SWEET! Thanks! I'll look this over and see if I can make my report go. I
bet I'll be writing again - and THANK YOU AGAIN!

 

[Tanya Sweeney via AccessMonster.com]

OK now this is a little over my head and I'm getting very lost.

As near as I can figure it, for purposes of the resulting report:

- My part number is as your Employee is.
- My ship week is as your Customer is.
- My End Consumer is as your Date of sale is.

So...

I made an alias table with the ship week, part number, alias and level, and
added it to my crosstab query.

I pasted your Create Alias module into my database and updated the
references to reflect my tables, fields and queries. It's likely that I
missed something here, since I have zero experience with modules.

In your example you set up an append query to drive the aliases; I
overwrite the obsolete records every time I update the report, since this
report is used to forecast production need and I'm only interested in the
future, not the past. Therefore I made a maketable query (instead of an
append query) to drive the aliases so I don't have to store the history.
That's OK, right?

But how do I get the alias and level fields of my table to populate?

Sincerely,

Tanya

 

[Tanya Sweeney via AccessMonster.com]

OK now wait, I read really carefully through the module and it looks to me
like maybe it clears the alias table before you start. So I deleted my
make table query and started over with an append query.

But I can't get the column with the total line set to "where" to stay in my
query when I save and close it. As soon as I re-open it, the "where"
column (my end consumer) is gone.

What up, buttercup?

 

[Duane Hookom]

I don't know what you mean by your "where column".

 

[Tanya Sweeney via AccessMonster.com]

fifth attempt to reply

 

[Tanya Sweeney via AccessMonster.com]

OK it seems like I'm able to reply to this thread now.

Sorry for being unclear in my explanation.

In your example you have a single append query which has just three visible
fields, EmployeeID, CustomerID and SaleDate. The Total properties for
EmployeeID (your first group by/sort by level in your report) and
CustomerID (Column Titles in your report) are set to Group. The Total
property for SaleDate (your row heading) is set to Where.

I tried to set mine up such that the Total properties on my PartNo (first
group by/sort by level) and my ShipWeek(column heading) are set to Group
By, which works fine, and the Total property for EndUser (my row heading)
is set to Where.

This works fine and it returns just a list of part numbers and ship weeks.
If I set the Unique Values property to yes, it comes back with just a list
of one occurrence of each ship week per part number, ship weeks being the
actual columns I will need, which is, I presume, exactly what I'm looking
for.

But when I save, close, and then re-open the query, the EndUser field whose
Total property was set to Where is no longer visible in the query design
grid.

Also, since I don't see Alias or Level fields in the query design grid or
in the datasheet view I'm not sure how it populates the Alias table, which
is not currently happening in my database.

I'm not sure if this is why the level and alias are not populating in my
table, which is really the next step I think I need to achieve. But any
troubleshooting/problem solving help you could lend would be greatly
appreciated.

Warmest regards,

Tanya

 

[Duane Hookom]

If you don't set any criteria under a "Where", the column isn't necessary
and will disappear. Should be no problem.

The ColumnAlias and Level values get populated in the code found in the
basCreateColumnAlias module and UpdateEmpCustAlias function.

 

[Tanya Sweeney via AccessMonster.com]

Oh sweet! OK I didn't know about that function code, I'll try to find it.

another question, and likely not my last - in your code you have a phrase

longEmpID = !employeeID

I have PNID (part number ID) instead of employeeID.

Would mine become PNIID, as I suspect, or PNID, or something altogether
different? Like

longPNIID = !PNID

Much thanks,

Tanya

 

[Tanya Sweeney via AccessMonster.com]

Sorry to ask an obvious question, but how do I find that UpdateEmpCustAlias
function? I've only seen functions as part of the property builder dialog
box for setting properties of form and report controls. If it's there,
what form/report and what control should I look at to find it?

Much thanks, Tanya

 

[Duane Hookom]

If you open the form in design view and check the code behind the command
button, you will find a call to the function:
...
End If
If UpdateEmpCustAlias(Me.lboNumColumns) = 0 Then
stDocName = "rptSalesByEmpCust"
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "There was an error computing values. Can't run report.",
vbCritical + vbOKOnly, "Error"
End If
...
You can place the insertion cursor anywhere in the function name and press
Shift+F2 you will open the module containing the function.

 

[Tanya Sweeney via AccessMonster.com]

Excellent, I will give it a go. - Tanya

 

[Tanya Sweeney via AccessMonster.com]

Dear Duane,

I printed out the code you posted. Then I made a command button to preview
the report. Then I added the posted code to the code behind my command
button.

First it gave me "end if with no block if" errors. So I took out the first
end if.

Now when I click the preview report command button I created nothing
visible happens. So I checked my alias table, and the alias and level
fields are populated with zeros (as they have been for a while).

I suspect perhaps the error lies in my code, so I am posting the code from
the command button and, below it, the code from the module in my database.

May I please ask you to look it over and see if you can spot my errors?

Much thanks, Tanya

**COMMAND BUTTON CODE**

Option Compare Database
Option Explicit

Private Sub cmdPreviewPNSubRpt_Click()
On Error GoTo Err_PreviewPNSubRpt_Click

Dim stDocName As String

stDocName = "PartNumberSubReport"
DoCmd.OpenReport stDocName, acPreview

Exit_PreviewPNSubRpt_Click:
Exit Sub

Err_PreviewPNSubRpt_Click:
MsgBox Err.Description
Resume Exit_PreviewPNSubRpt_Click




If UpdateTblPNOrderDateAlias(Me.lboNumColumns) = 0 Then
stDocName = "PartNumberSubReport"
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "There was an error computing values. Can't run report."
vbCritical vbOKOnly, "Error"
End If

End Sub

Private Sub PreviewPNSubRpt_Click()

End Sub


______________________________________

**MODULE CODE**


Option Compare Database
Option Explicit

Function PNOrderDateAlias(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 TblPNOrderDateAlias_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
Dim bytLevel As Byte
Dim lngPNNID As Long
Dim bytMaxColumns As Byte

Dim db As Database
Dim rs As Recordset


strSQL = "Delete * from TblPNOrderDateAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.OpenQuery "QAppPNOrderDateAlias" 'create table of unique
combinations of part number and ship week
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("TblPNOrderDateAlias") 'table used to
redefine/alias the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngPNIID = !PNID
bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !PNID = lngPNID
.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

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

TblPNOrderDateAlias_Err:
Select Case Err
Case Else
TblPNOrderDateAlias = Err.Number
Resume TblPNOrderDateAlias_Exit
End Select

End Function

 

[Duane Hookom]

Your code in the form calls the function
UpdateTblPNOrderDateAlias(Me.lboNumColumns) but your function name is
PNOrderDateAlias. Also your code looks out of order.

BTW: you shouldn't post code or any text with email addresses. At least the
address is no longer valid since I haven't worked for that company since
2001.

 

[Tanya Sweeney via AccessMonster.com]

4th try

 

[Tanya Sweeney via AccessMonster.com]

Dear Duane,

Sorry about posting the email address and the code. That was an oversight
on my part.

Not to get too far off track, but I understand why we shouldn't post email
addresses, but why is code problematic?

Getting back on the subject, I deleted my comand button and started over.
Then I updated the function name per your suggestion and moved the order
around, putting the call to check the PNOrderDateAlias value first, then
having the code get me out of the button. The new button is dead, and the
alias and level fields in my alias table are still not populating.

The good news is that my crosstab report looks great. The column headings
are populating from a query of the query that the crosstab is based on, and
they line up perfectly. The crosstab report is a subreport in a detail-
level report by part number, and they look great on the page together. So
once the crosstab query values start populating into the crosstab report it
will be awesome. This is automating a report that a guy used to populate
by hand by manually parsing the numbers from one excel sheet into several,
one for each part number, can you imagine? Once this thing goes the whole
process will be automatic, the only manual part will be downloading the
info from the accounting package. And they update this report at least
once a week. I'm so excited.

So if you could help me figure out what I'm missing that would be great.
I'm starting to run out of ideas about where to look for errors and
omissions.

Any ideas?

Much thanks,

Tanya

 

[Duane Hookom]

You should right click on your new command button and view the code to make
sure the proper code is there. Once you have it set properly, paste your
code in a reply.

 

[Tanya Sweeney via AccessMonster.com]

Dear Duane,

Here's my new code.

Option Compare Database
Option Explicit

Private Sub PreviewProjectionReport_Click()
On Error GoTo Err_PreviewProjectionReport_Click

Dim stDocName As String

stDocName = "ProjectionReport"

If PNOrderDateAlias = 0 Then stDocName = "ProjectionReport"
DoCmd.OpenReport stProjectionReport, acViewPreview
Else
MsgBox "There was an error computing values. Can't run report."
vbCritical vbOKOnly, "Error"
End If

DoCmd.OpenReport stDocName, acPreview

Exit_PreviewProjectionReport_Click:
Exit Sub

Err_PreviewProjectionReport_Click:
MsgBox Err.Description
Resume Exit_PreviewProjectionReport_Click

End Sub

Private Sub PreviewOpenOrdersReport_Click()

End Sub

Much thanks - Tanya

 

[Tanya Sweeney via AccessMonster.com]

OK I noticed a naming error at the end of the code and fixed it.

End Sub

Private Sub PreviewOpenOrdersReport_Click()

End Sub

now reads

End Sub

Private Sub PreviewProjectionReport_Click()

End Sub

and I'm getting an error when I click the button that reads

"The expression On Click you entered as the event property setting produced
the following error: Ambiguous name detected: PreviewprojectionReport_Click.

* The expression may not result in the name of a macro, the name of a user-
defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro."

To me this is an improvement because at least something is happening.

Any ideas?

- Tanya