Report from Crosstab Query

[Duane Hookom]

I expect this is not right. You aren't referring to my original code and
values.

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

Try
Dim intNumberOfColumns as Integer
intNumberOfColumns = 10
If PNOrderDateAlias(intNumberOfColumns) = 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

This assumes you have a module with the function PNOrderDateAlias.

 

[Tanya Sweeney via AccessMonster.com]

Dear Duane,

I'm having a problem constructing a crosstab query with all those elements
you mentioned.

When I put the alias table and the part number table (equivalent to your
salesperson in your model, I have about 80 active part numbers) into the
design of my crosstab it generates over a thousand records where it should
have about 75, even though I link the part number PNID fields. Because of
that, I'm not sure how to get the fields from tables other than the one
holding the transaction records into my crosstab query.

Also, I'm not sure if your model is designed to handle this, but I have
over 10,000 possible end customers (used as the row heading), even though
the bulk of the traffic is for a scant 20 or so. (My report is based on
info downloaded from a massive international
accounting/inventory/manufacturing & production database.) Given that,
should my alias still be just a byte, or do I need a larger size?

Much thanks,

Tanya

 

[Duane Hookom]

I think I am ready for you to send me your required tables as well as you
report specification (if you want). Please don't do this without emailing me
first about what I expect to receive. My email automatically deletes emails
with attachments unless you follow some specifc instructions.

There are no fees or charges for this so don't be shy.