Last record of crosstab repeats in report

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

I have a linked ODBC database that I have created some
crosstab queries, forms and reports for in Access. I have
a form on which the user selects the options they want to
select the proper query. The individual queries work fine
when run by themselves. When generated from within the
report all but two repeat the last record. The number of
repeats appears to be random (from 4 to 158). (If all of
the queries repeated the last record, I'd be able to track
the issue.)

I can watch the Detail_format event get called several
times after the last record from the query has been
processed. I addded the else code and that eliminates the
printing of the duplicate records but leaves a gap between
the last record and the report totals. Where the
duplicates were 158 this means the totals are several
pages later. Not a tenable solution. (The code is borrowed
from the Developers Solutions sample for the employee
sales crosstab report.) I could really use some
suggestions!!!! The code from the detail_fromat section is
below:
-----------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
' Place values in text boxes and hide unused text boxes.
Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into
' text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls(rstReport
(intX - 1))
Next intX
'cntx = cntx + 1
'Debug.Print cntx
'cntx = 1
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
End If
End Sub
 
Saw the crosstab demo. It's more complex in some ways,
less in others but doesn't answer all of my issues. One of
the queries is below. The columns are dynamic in that the
query determines the selection of either store locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both the
detail and page header, just give me an affirmative and
I'll go with that. The impression I got from this same
forum when I started the project was that I could not use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask for
some suggestions as to why the detail section keeps going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ), Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms!MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short, Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms!MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy",[SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between [Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]![MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds].[Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]![txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]![txtEndLoc],5)) And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);
 
The query you provided pivots [Loc]. The crosstab sample would handle this
well. If you want to use date intervals, you should design the report for a
particular number of columns. Then use relative dates as column headings.
You can search google groups on my name and relative dates and "Mth1".

--
Duane Hookom
MS Access MVP


Rod said:
Saw the crosstab demo. It's more complex in some ways,
less in others but doesn't answer all of my issues. One of
the queries is below. The columns are dynamic in that the
query determines the selection of either store locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both the
detail and page header, just give me an affirmative and
I'll go with that. The impression I got from this same
forum when I started the project was that I could not use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask for
some suggestions as to why the detail section keeps going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ), Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms!MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short, Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms!MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy",[SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between [Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]![MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds].[Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]![txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]![txtEndLoc],5)) And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);

-----Original Message-----
You might want to check out the Crosstabs demos from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb has a
much more efficient and flexible solution. There is no code in the report
and sums are easy to create. If you are using date intervals as column
headings there are much better solutions.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
Not really interested in static dates like the sample and
absolutely can't have a generic column heading. They look
at the data by month, quarter and year but there is no set
report that way. They frequently want to see the sales
(they are a dairy brokerage firm) YTD by month or quarter.
(Or they want to compare last years sales during the prior
quarter to this years quarter. If they enter time
period 'q' and specify the same start and end period and
specify two years they can get a quarter to quarter
comparison this year over last year.) This type of report
would put anywhere from 1 to 25 stores as the data columns
with the time periods as multiple rows for the time
periods per item. Again, the columns must remain dynamic.
I'll play with it this weekend to see what I can get but
like I said, this forum recommended the way I have it as
being the only way to accomplish it with dynamic columns.

-----Original Message-----
The query you provided pivots [Loc]. The crosstab sample would handle this
well. If you want to use date intervals, you should design the report for a
particular number of columns. Then use relative dates as column headings.
You can search google groups on my name and relative dates and "Mth1".

--
Duane Hookom
MS Access MVP


Rod said:
Saw the crosstab demo. It's more complex in some ways,
less in others but doesn't answer all of my issues. One of
the queries is below. The columns are dynamic in that the
query determines the selection of either store locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both the
detail and page header, just give me an affirmative and
I'll go with that. The impression I got from this same
forum when I started the project was that I could not use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask for
some suggestions as to why the detail section keeps going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ), Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms!MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short, Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms!MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left ([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy",[SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between [Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]![MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds].[Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]! [txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]![txtEndLoc],5)) And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);

-----Original Message-----
You might want to check out the Crosstabs demos from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
Click to expand...
The
Crosstab.mdb has a
much more efficient and flexible solution. There is no code in the report
and sums are easy to create. If you are using date intervals as column
headings there are much better solutions.

--
Duane Hookom
MS Access MVP


I have a linked ODBC database that I have created some
crosstab queries, forms and reports for in Access. I have
a form on which the user selects the options they
Click to expand...
want
to
select the proper query. The individual queries work fine
when run by themselves. When generated from within the
report all but two repeat the last record. The number of
repeats appears to be random (from 4 to 158). (If all of
the queries repeated the last record, I'd be able to track
the issue.)

I can watch the Detail_format event get called several
times after the last record from the query has been
processed. I addded the else code and that eliminates the
printing of the duplicate records but leaves a gap between
the last record and the report totals. Where the
duplicates were 158 this means the totals are several
pages later. Not a tenable solution. (The code is borrowed
from the Developers Solutions sample for the employee
sales crosstab report.) I could really use some
suggestions!!!! The code from the detail_fromat
Click to expand...
section
is
below:
-----------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
' Place values in text boxes and hide unused text boxes.
Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into
' text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls(rstReport
(intX - 1))
Next intX
'cntx = cntx + 1
'Debug.Print cntx
'cntx = 1
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal
Click to expand...
(intX) +
Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
End If
End Sub


.
Click to expand...
Click to expand...


.
Click to expand...
 
You could spend tens of hours attempting to make one report handle both Loc
and date/time intervals as column headings or you could create about 4-6
reports using the sample I mentioned for the Loc and relative date intervals
for the others. The relative date solution would be set up to always return
a specific number of columns such as a '13 week' or '7 month' report.

Your result would be a few more objects in your database but 1) you could
easily total the results in a report 2) much less coding 3) much better
performance (my tests versus the Solutions.mdb were up to 10x faster) 4)
Pivot Loc could contain from 1 to 100s of columns if needed.



--
Duane Hookom
MS Access MVP


Rod said:
Not really interested in static dates like the sample and
absolutely can't have a generic column heading. They look
at the data by month, quarter and year but there is no set
report that way. They frequently want to see the sales
(they are a dairy brokerage firm) YTD by month or quarter.
(Or they want to compare last years sales during the prior
quarter to this years quarter. If they enter time
period 'q' and specify the same start and end period and
specify two years they can get a quarter to quarter
comparison this year over last year.) This type of report
would put anywhere from 1 to 25 stores as the data columns
with the time periods as multiple rows for the time
periods per item. Again, the columns must remain dynamic.
I'll play with it this weekend to see what I can get but
like I said, this forum recommended the way I have it as
being the only way to accomplish it with dynamic columns.

-----Original Message-----
The query you provided pivots [Loc]. The crosstab sample would handle this
well. If you want to use date intervals, you should design the report for a
particular number of columns. Then use relative dates as column headings.
You can search google groups on my name and relative dates and "Mth1".

--
Duane Hookom
MS Access MVP


Rod said:
Saw the crosstab demo. It's more complex in some ways,
less in others but doesn't answer all of my issues. One of
the queries is below. The columns are dynamic in that the
query determines the selection of either store locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both the
detail and page header, just give me an affirmative and
I'll go with that. The impression I got from this same
forum when I started the project was that I could not use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask for
some suggestions as to why the detail section keeps going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ), Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms!MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short, Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms!MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left ([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy",[SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between [Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]![MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds].[Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]! [txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]![txtEndLoc],5)) And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);


-----Original Message-----
You might want to check out the Crosstabs demos from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The
Crosstab.mdb has a
much more efficient and flexible solution. There is no
code in the report
and sums are easy to create. If you are using date
intervals as column
headings there are much better solutions.

--
Duane Hookom
MS Access MVP


I have a linked ODBC database that I have created some
crosstab queries, forms and reports for in Access. I
have
a form on which the user selects the options they want
to
select the proper query. The individual queries work
fine
when run by themselves. When generated from within the
report all but two repeat the last record. The number of
repeats appears to be random (from 4 to 158). (If all of
the queries repeated the last record, I'd be able to
track
the issue.)

I can watch the Detail_format event get called several
times after the last record from the query has been
processed. I addded the else code and that eliminates
the
printing of the duplicate records but leaves a gap
between
the last record and the report totals. Where the
duplicates were 158 this means the totals are several
pages later. Not a tenable solution. (The code is
borrowed
from the Developers Solutions sample for the employee
sales crosstab report.) I could really use some
suggestions!!!! The code from the detail_fromat section
is
below:
-----------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
' Place values in text boxes and hide unused text boxes.
Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into
' text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls(rstReport
(intX - 1))
Next intX
'cntx = cntx + 1
'Debug.Print cntx
'cntx = 1
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" +
Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal (intX) +
Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) =
lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
End If
End Sub


.
Click to expand...


.
Click to expand...
Click to expand...
 
Actually there are two reports and two forms. They choose
based on whether they want to look at a single site (Pivot
time period) or multiple sites (pivot location). The issue
again is being able to maintain the dynamic column
headings. Otherwise, every time they add a customer
location they have to come back to me for mods. While I
don't mind the work :), it really is a disservice to my
customer to force them to come to me when one of their
customers adds a location. I have to be able to maintain
the dynamic column headers. So, I'm back to square one. Do
you have any suggestions as to why the report would keep
on running through the detail section after the recordset
has reached EOF?

-----Original Message-----
You could spend tens of hours attempting to make one report handle both Loc
and date/time intervals as column headings or you could create about 4-6
reports using the sample I mentioned for the Loc and relative date intervals
for the others. The relative date solution would be set up to always return
a specific number of columns such as a '13 week' or '7 month' report.

Your result would be a few more objects in your database but 1) you could
easily total the results in a report 2) much less coding 3) much better
performance (my tests versus the Solutions.mdb were up to 10x faster) 4)
Pivot Loc could contain from 1 to 100s of columns if needed.



--
Duane Hookom
MS Access MVP


Rod said:
Not really interested in static dates like the sample and
absolutely can't have a generic column heading. They look
at the data by month, quarter and year but there is no set
report that way. They frequently want to see the sales
(they are a dairy brokerage firm) YTD by month or quarter.
(Or they want to compare last years sales during the prior
quarter to this years quarter. If they enter time
period 'q' and specify the same start and end period and
specify two years they can get a quarter to quarter
comparison this year over last year.) This type of report
would put anywhere from 1 to 25 stores as the data columns
with the time periods as multiple rows for the time
periods per item. Again, the columns must remain dynamic.
I'll play with it this weekend to see what I can get but
like I said, this forum recommended the way I have it as
being the only way to accomplish it with dynamic columns.

-----Original Message-----
The query you provided pivots [Loc]. The crosstab
Click to expand...
sample
would handle this
well. If you want to use date intervals, you should design the report for a
particular number of columns. Then use relative dates
Click to expand...
as
column headings.
You can search google groups on my name and relative dates and "Mth1".

--
Duane Hookom
MS Access MVP


Saw the crosstab demo. It's more complex in some ways,
less in others but doesn't answer all of my issues.
Click to expand...
One
of
the queries is below. The columns are dynamic in that the
query determines the selection of either store locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both the
detail and page header, just give me an affirmative and
I'll go with that. The impression I got from this same
forum when I started the project was that I could not use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask for
some suggestions as to why the detail section keeps going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ), Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms! MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short, Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms! MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd], [SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left ([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy", [SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between [Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]! [MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds].[Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]! [txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]![txtEndLoc],5)) And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]), DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);


-----Original Message-----
You might want to check out the Crosstabs demos from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The
Crosstab.mdb has a
much more efficient and flexible solution. There is no
code in the report
and sums are easy to create. If you are using date
intervals as column
headings there are much better solutions.

--
Duane Hookom
MS Access MVP


I have a linked ODBC database that I have created some
crosstab queries, forms and reports for in Access. I
have
a form on which the user selects the options they want
to
select the proper query. The individual queries work
fine
when run by themselves. When generated from within the
report all but two repeat the last record. The number of
repeats appears to be random (from 4 to 158). (If all of
the queries repeated the last record, I'd be able to
track
the issue.)

I can watch the Detail_format event get called several
times after the last record from the query has been
processed. I addded the else code and that eliminates
the
printing of the duplicate records but leaves a gap
between
the last record and the report totals. Where the
duplicates were 158 this means the totals are several
pages later. Not a tenable solution. (The code is
borrowed
from the Developers Solutions sample for the employee
sales crosstab report.) I could really use some
suggestions!!!! The code from the detail_fromat section
is
below:
-----------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
' Place values in text boxes and hide unused text boxes.
Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into
' text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls (rstReport
(intX - 1))
Next intX
'cntx = cntx + 1
'Debug.Print cntx
'cntx = 1
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" +
Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal (intX) +
Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) =
lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
End If
End Sub


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
The crosstab.mdb at http://www.invisibleinc.com/divFiles.cfm?divDivID=4
provides dynamic column headings. My suggestion regarding relative date
periods creates "relative date ranges" so the user selects an Ending Date
for a report and the columns automatically adjust with no extra coding or
design work.

--
Duane Hookom
MS Access MVP


Rod said:
Actually there are two reports and two forms. They choose
based on whether they want to look at a single site (Pivot
time period) or multiple sites (pivot location). The issue
again is being able to maintain the dynamic column
headings. Otherwise, every time they add a customer
location they have to come back to me for mods. While I
don't mind the work :), it really is a disservice to my
customer to force them to come to me when one of their
customers adds a location. I have to be able to maintain
the dynamic column headers. So, I'm back to square one. Do
you have any suggestions as to why the report would keep
on running through the detail section after the recordset
has reached EOF?

-----Original Message-----
You could spend tens of hours attempting to make one report handle both Loc
and date/time intervals as column headings or you could create about 4-6
reports using the sample I mentioned for the Loc and relative date intervals
for the others. The relative date solution would be set up to always return
a specific number of columns such as a '13 week' or '7 month' report.

Your result would be a few more objects in your database but 1) you could
easily total the results in a report 2) much less coding 3) much better
performance (my tests versus the Solutions.mdb were up to 10x faster) 4)
Pivot Loc could contain from 1 to 100s of columns if needed.



--
Duane Hookom
MS Access MVP


Rod said:
Not really interested in static dates like the sample and
absolutely can't have a generic column heading. They look
at the data by month, quarter and year but there is no set
report that way. They frequently want to see the sales
(they are a dairy brokerage firm) YTD by month or quarter.
(Or they want to compare last years sales during the prior
quarter to this years quarter. If they enter time
period 'q' and specify the same start and end period and
specify two years they can get a quarter to quarter
comparison this year over last year.) This type of report
would put anywhere from 1 to 25 stores as the data columns
with the time periods as multiple rows for the time
periods per item. Again, the columns must remain dynamic.
I'll play with it this weekend to see what I can get but
like I said, this forum recommended the way I have it as
being the only way to accomplish it with dynamic columns.


-----Original Message-----
The query you provided pivots [Loc]. The crosstab sample
would handle this
well. If you want to use date intervals, you should
design the report for a
particular number of columns. Then use relative dates as
column headings.
You can search google groups on my name and relative
dates and "Mth1".

--
Duane Hookom
MS Access MVP


Saw the crosstab demo. It's more complex in some ways,
less in others but doesn't answer all of my issues. One
of
the queries is below. The columns are dynamic in that
the
query determines the selection of either store locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both
the
detail and page header, just give me an affirmative and
I'll go with that. The impression I got from this same
forum when I started the project was that I could not
use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask
for
some suggestions as to why the detail section keeps
going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ),
Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms!MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms! MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short,
Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms! MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]) AS Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd], [SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left
([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy", [SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between
[Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]! [MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds].[Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]!
[txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]![txtEndLoc],5))
And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]),
DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]),
DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);


-----Original Message-----
You might want to check out the Crosstabs demos from
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
The
Crosstab.mdb has a
much more efficient and flexible solution. There is no
code in the report
and sums are easy to create. If you are using date
intervals as column
headings there are much better solutions.

--
Duane Hookom
MS Access MVP


I have a linked ODBC database that I have created
some
crosstab queries, forms and reports for in Access. I
have
a form on which the user selects the options they
want
to
select the proper query. The individual queries work
fine
when run by themselves. When generated from within
the
report all but two repeat the last record. The
number of
repeats appears to be random (from 4 to 158). (If
all of
the queries repeated the last record, I'd be able to
track
the issue.)

I can watch the Detail_format event get called
several
times after the last record from the query has been
processed. I addded the else code and that eliminates
the
printing of the duplicate records but leaves a gap
between
the last record and the report totals. Where the
duplicates were 158 this means the totals are several
pages later. Not a tenable solution. (The code is
borrowed
from the Developers Solutions sample for the employee
sales crosstab report.) I could really use some
suggestions!!!! The code from the detail_fromat
section
is
below:
-----------------------------
Private Sub Detail_Format(Cancel As Integer,
FormatCount
As Integer)
' Place values in text boxes and hide unused text
boxes.
Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset
into
' text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls (rstReport
(intX - 1))
Next intX
'cntx = cntx + 1
'Debug.Print cntx
'cntx = 1
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail
section.
lngRowTotal = lngRowTotal + Me("Col" +
Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal
(intX) +
Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) =
lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
End If
End Sub


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Duane, I really appreciate your comments. Your crosstab
query is below:

TRANSFORM "~" & Sum(DateDiff("n",[TimeIn],[TimeOut])/60)
& "~" & Count([SchedID]) AS Expr1
SELECT SCHEDULE.USER
FROM SCHEDULE
GROUP BY SCHEDULE.USER
PIVOT Format([DATE],"yyyymm") In
(199801,199802,199803,199804,199805,199806,199807,199808,19
9809,199810,199811,199812);

By specifying the items the pivot can be 'in' you don't
have dynamic columns except 'within' that group.

My customer has customers with 1 to 28 locations. The
customer who has 28 locations is adding a store about
every 3 or 4 months. They group these stores by region. I
need to be able to allow them to have a 'few' reports that
give them flexibility. I use the queries to break out the
regions but also give them the flexibility to limit store
selection just about any way they want to. I can't have a
pivit that has limitations like you have in the query
above. Again, if I did that, they'd have to call me every
time their customers added a location or realigned regions.

For instance, right now they have a Florida region with
several stores in it. Under your type of query, I'd have
to go in and modify it if they added a store there. With
my query that is not necessary because a selection
criteria for the Florida region is based on state. Simpler
for me and them and also less costly for them.

I'd really like to solve my original question.
-----Original Message-----
The crosstab.mdb at http://www.invisibleinc.com/divFiles.cfm?divDivID=4
provides dynamic column headings. My suggestion regarding relative date
periods creates "relative date ranges" so the user selects an Ending Date
for a report and the columns automatically adjust with no extra coding or
design work.

--
Duane Hookom
MS Access MVP


Rod said:
Actually there are two reports and two forms. They choose
based on whether they want to look at a single site (Pivot
time period) or multiple sites (pivot location). The issue
again is being able to maintain the dynamic column
headings. Otherwise, every time they add a customer
location they have to come back to me for mods. While I
don't mind the work :), it really is a disservice to my
customer to force them to come to me when one of their
customers adds a location. I have to be able to maintain
the dynamic column headers. So, I'm back to square one. Do
you have any suggestions as to why the report would keep
on running through the detail section after the recordset
has reached EOF?

-----Original Message-----
You could spend tens of hours attempting to make one report handle both Loc
and date/time intervals as column headings or you could create about 4-6
reports using the sample I mentioned for the Loc and relative date intervals
for the others. The relative date solution would be set up to always return
a specific number of columns such as a '13 week' or '7 month' report.

Your result would be a few more objects in your
Click to expand...
database
but 1) you could
easily total the results in a report 2) much less
Click to expand...
coding
3) much better
performance (my tests versus the Solutions.mdb were up
Click to expand...
to
10x faster) 4)
Pivot Loc could contain from 1 to 100s of columns if needed.



--
Duane Hookom
MS Access MVP


Not really interested in static dates like the sample and
absolutely can't have a generic column heading. They look
at the data by month, quarter and year but there is
Click to expand...
no
set
report that way. They frequently want to see the sales
(they are a dairy brokerage firm) YTD by month or quarter.
(Or they want to compare last years sales during the prior
quarter to this years quarter. If they enter time
period 'q' and specify the same start and end period and
specify two years they can get a quarter to quarter
comparison this year over last year.) This type of report
would put anywhere from 1 to 25 stores as the data columns
with the time periods as multiple rows for the time
periods per item. Again, the columns must remain dynamic.
I'll play with it this weekend to see what I can get but
like I said, this forum recommended the way I have it as
being the only way to accomplish it with dynamic columns.


-----Original Message-----
The query you provided pivots [Loc]. The crosstab sample
would handle this
well. If you want to use date intervals, you should
design the report for a
particular number of columns. Then use relative
Click to expand...
dates
as
column headings.
You can search google groups on my name and relative
dates and "Mth1".

--
Duane Hookom
MS Access MVP


Saw the crosstab demo. It's more complex in some ways,
less in others but doesn't answer all of my
Click to expand...
issues.
One
of
the queries is below. The columns are dynamic in that
the
query determines the selection of either store locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both
the
detail and page header, just give me an
Click to expand...
affirmative
and
I'll go with that. The impression I got from this same
forum when I started the project was that I could not
use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask
for
some suggestions as to why the detail section keeps
going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ),
Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms! MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short, Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms! MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short,
Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms! MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE])
Click to expand...
AS
Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd], [SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase ([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left
([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy", [SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between
[Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]! [MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds]. [Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]!
[txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]! [txtEndLoc],5))
And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds]. [Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]),
DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds]. [Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]),
DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);


-----Original Message-----
You might want to check out the Crosstabs demos from
http://www.invisibleinc.com/divFiles.cfm? divDivID=4.
The
Crosstab.mdb has a
much more efficient and flexible solution. There
Click to expand...
is
no
code in the report
and sums are easy to create. If you are using date
intervals as column
headings there are much better solutions.

--
Duane Hookom
MS Access MVP


I have a linked ODBC database that I have created
some
crosstab queries, forms and reports for in Access. I
have
a form on which the user selects the options they
want
to
select the proper query. The individual queries work
fine
when run by themselves. When generated from within
the
report all but two repeat the last record. The
number of
repeats appears to be random (from 4 to 158). (If
all of
the queries repeated the last record, I'd be
Click to expand...
able
to
track
the issue.)

I can watch the Detail_format event get called
several
times after the last record from the query has been
processed. I addded the else code and that eliminates
the
printing of the duplicate records but leaves a gap
between
the last record and the report totals. Where the
duplicates were 158 this means the totals are several
pages later. Not a tenable solution. (The code is
borrowed
from the Developers Solutions sample for the employee
sales crosstab report.) I could really use some
suggestions!!!! The code from the detail_fromat
section
is
below:
-----------------------------
Private Sub Detail_Format(Cancel As Integer,
FormatCount
As Integer)
' Place values in text boxes and hide unused text
boxes.
Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset
into
' text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls (rstReport
(intX - 1))
Next intX
'cntx = cntx + 1
'Debug.Print cntx
'cntx = 1
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail
section.
lngRowTotal = lngRowTotal + Me("Col" +
Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal
(intX) +
Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) =
lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
End If
End Sub


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
The multi-value crosstab is the one that I suggested you to use. I have
suggested at least twice where I think you can find workable solutions. You
haven't provided any specifications that make me think the solutions won't
work.

--
Duane Hookom
MS Access MVP


Rod said:
Duane, I really appreciate your comments. Your crosstab
query is below:

TRANSFORM "~" & Sum(DateDiff("n",[TimeIn],[TimeOut])/60)
& "~" & Count([SchedID]) AS Expr1
SELECT SCHEDULE.USER
FROM SCHEDULE
GROUP BY SCHEDULE.USER
PIVOT Format([DATE],"yyyymm") In
(199801,199802,199803,199804,199805,199806,199807,199808,19
9809,199810,199811,199812);

By specifying the items the pivot can be 'in' you don't
have dynamic columns except 'within' that group.

My customer has customers with 1 to 28 locations. The
customer who has 28 locations is adding a store about
every 3 or 4 months. They group these stores by region. I
need to be able to allow them to have a 'few' reports that
give them flexibility. I use the queries to break out the
regions but also give them the flexibility to limit store
selection just about any way they want to. I can't have a
pivit that has limitations like you have in the query
above. Again, if I did that, they'd have to call me every
time their customers added a location or realigned regions.

For instance, right now they have a Florida region with
several stores in it. Under your type of query, I'd have
to go in and modify it if they added a store there. With
my query that is not necessary because a selection
criteria for the Florida region is based on state. Simpler
for me and them and also less costly for them.

I'd really like to solve my original question.
-----Original Message-----
The crosstab.mdb at http://www.invisibleinc.com/divFiles.cfm?divDivID=4
provides dynamic column headings. My suggestion regarding relative date
periods creates "relative date ranges" so the user selects an Ending Date
for a report and the columns automatically adjust with no extra coding or
design work.

--
Duane Hookom
MS Access MVP


Rod said:
Actually there are two reports and two forms. They choose
based on whether they want to look at a single site (Pivot
time period) or multiple sites (pivot location). The issue
again is being able to maintain the dynamic column
headings. Otherwise, every time they add a customer
location they have to come back to me for mods. While I
don't mind the work :), it really is a disservice to my
customer to force them to come to me when one of their
customers adds a location. I have to be able to maintain
the dynamic column headers. So, I'm back to square one. Do
you have any suggestions as to why the report would keep
on running through the detail section after the recordset
has reached EOF?


-----Original Message-----
You could spend tens of hours attempting to make one
report handle both Loc
and date/time intervals as column headings or you could
create about 4-6
reports using the sample I mentioned for the Loc and
relative date intervals
for the others. The relative date solution would be set
up to always return
a specific number of columns such as a '13 week' or '7
month' report.

Your result would be a few more objects in your database
but 1) you could
easily total the results in a report 2) much less coding
3) much better
performance (my tests versus the Solutions.mdb were up to
10x faster) 4)
Pivot Loc could contain from 1 to 100s of columns if
needed.



--
Duane Hookom
MS Access MVP


Not really interested in static dates like the sample
and
absolutely can't have a generic column heading. They
look
at the data by month, quarter and year but there is no
set
report that way. They frequently want to see the sales
(they are a dairy brokerage firm) YTD by month or
quarter.
(Or they want to compare last years sales during the
prior
quarter to this years quarter. If they enter time
period 'q' and specify the same start and end period and
specify two years they can get a quarter to quarter
comparison this year over last year.) This type of
report
would put anywhere from 1 to 25 stores as the data
columns
with the time periods as multiple rows for the time
periods per item. Again, the columns must remain
dynamic.
I'll play with it this weekend to see what I can get but
like I said, this forum recommended the way I have it as
being the only way to accomplish it with dynamic
columns.


-----Original Message-----
The query you provided pivots [Loc]. The crosstab
sample
would handle this
well. If you want to use date intervals, you should
design the report for a
particular number of columns. Then use relative dates
as
column headings.
You can search google groups on my name and relative
dates and "Mth1".

--
Duane Hookom
MS Access MVP


Saw the crosstab demo. It's more complex in some
ways,
less in others but doesn't answer all of my issues.
One
of
the queries is below. The columns are dynamic in that
the
query determines the selection of either store
locations
or time periods (months, quarters or years) as the
columns. If I can do this with bound controls in both
the
detail and page header, just give me an affirmative
and
I'll go with that. The impression I got from this
same
forum when I started the project was that I could not
use
bound controls. (BTW, they run a separate form/report
combo for each pivot.) If not, then I would again ask
for
some suggestions as to why the detail section keeps
going
after the last record is processed.

PARAMETERS Forms!MultiSiteMrB!txtVend Text ( 25 ),
Forms!
MultiSiteMrB!txtCust Text ( 5 ), Forms! MultiSiteMrB!
txtStYr Short, Forms!MultiSiteMrB!txtEndYr Short,
Forms!
MultiSiteMrB!txtTimePrd Text ( 1 ), Forms!
MultiSiteMrB!
txtStrPrd Long, Forms!MultiSiteMrB!txtEndPrd Short,
Forms!
MultiSiteMrB!txtStrLoc Text ( 5 ), Forms!
MultiSiteMrB!
txtEndLoc Text ( 5 );
TRANSFORM Sum([Totalpounds].[TotWgt]) AS Wgt
SELECT [Totalpounds].[Vnd], [Totalpounds].[Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]) AS
Yr,
DatePart([Forms]![MultiSiteMrB]![txtTimePrd],
[SHIPDATE])
AS Period
FROM Totalpounds
WHERE (((UCase([Totalpounds].[Vnd]))=UCase ([Forms]!
[MultiSiteMrB]![txtVend])) And ((UCase(Left
([Totalpounds].
[Cust],5)))="JETRO") And ((DatePart("yyyy",
[SHIPDATE]))
Between [Forms]![MultiSiteMrB]![txtStYr] And [Forms]!
[MultiSiteMrB]![txtEndYr]) And ((DatePart([Forms]!
[MultiSiteMrB]![txtTimePrd],[SHIPDATE])) Between
[Forms]!
[MultiSiteMrB]![txtStrPrd] And [Forms]!
[MultiSiteMrB]!
[txtEndPrd])) And UCase(Left([Totalpounds]. [Loc],5))
Between ucase(left([Forms]![MultiSiteMrB]!
[txtStrLoc],5))
And ucase(left([Forms]![MultiSiteMrB]! [txtEndLoc],5))
And
([Totalpounds].[State] Not In ("IL","WI","FL")
GROUP BY [Totalpounds].[Vnd], [Totalpounds]. [Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]),
DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
ORDER BY [Totalpounds].[Vnd], [Totalpounds]. [Cust],
[Totalpounds].[Item], DatePart("yyyy",[SHIPDATE]),
DatePart
([Forms]![MultiSiteMrB]![txtTimePrd],[SHIPDATE])
PIVOT Left([Totalpounds].[Loc],9);


-----Original Message-----
You might want to check out the Crosstabs demos from
http://www.invisibleinc.com/divFiles.cfm? divDivID=4.
The
Crosstab.mdb has a
much more efficient and flexible solution. There is
no
code in the report
and sums are easy to create. If you are using date
intervals as column
headings there are much better solutions.

--
Duane Hookom
MS Access MVP


message
I have a linked ODBC database that I have created
some
crosstab queries, forms and reports for in
Access. I
have
a form on which the user selects the options they
want
to
select the proper query. The individual queries
work
fine
when run by themselves. When generated from within
the
report all but two repeat the last record. The
number of
repeats appears to be random (from 4 to 158). (If
all of
the queries repeated the last record, I'd be able
to
track
the issue.)

I can watch the Detail_format event get called
several
times after the last record from the query has
been
processed. I addded the else code and that
eliminates
the
printing of the duplicate records but leaves a gap
between
the last record and the report totals. Where the
duplicates were 158 this means the totals are
several
pages later. Not a tenable solution. (The code is
borrowed
from the Developers Solutions sample for the
employee
sales crosstab report.) I could really use some
suggestions!!!! The code from the detail_fromat
section
is
below:
-----------------------------
Private Sub Detail_Format(Cancel As Integer,
FormatCount
As Integer)
' Place values in text boxes and hide unused text
boxes.
Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset
into
' text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls
(rstReport
(intX - 1))
Next intX
'cntx = cntx + 1
'Debug.Print cntx
'cntx = 1
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab
value,
' compute total for current row in detail
section.
lngRowTotal = lngRowTotal + Me("Col" +
Format$(intX))
' Add crosstab value to total for current
column.
lngRgColumnTotal(intX) = lngRgColumnTotal
(intX) +
Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail
section.
Me("Col" + Format$(intColumnCount + 1)) =
lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To
conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To
conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
End If
End Sub


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Duane,

I haven't said your solution won't work. What I've been
saying is that it is not a viable solution for my customer
nor is it the type of solution I would like to present to
them.

"Mr. Customer, here's your reports. Oh, BTW, if one of
your customers adds a store you'll have to come back to me
to modify the reports. Oh, BTW, if your customer closes a
store you'll have to come back to me to modify the
reports. Oh, BTW, if your customer changes a store number
or name you'll have to come back to me to modify the
reports. Oh, BTW, if your customer realigns reporting
groups (regions/divisions) you'll have to come back to me
to modify the reports."

I can't in all good conscience deliver that type of
solution.

Here's what I have.
Master query that contains the following fields:
[VendorID]
[ShipCo]
[Company]
[ProductID]
[ProductID]+"-"+[ProductDesc]
[Quantity]
[Quantity]*[CaseWeight]
[SHIPDATE]
[State]
[Zip]

A form and report that you can look at
http://www.sandorustech.com/PLform.gif
http://www.sandorustech.com/PLreport.gif

The report open even builds the SQL statement based on
criteria selected on the form.
-------------------------
Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim qryset1 As Boolean
Dim qdf As QueryDef
Dim sql$, sql1$, sql2$, sql3$, sql4$, sqlall$, grpord$
Const conFormDesign = 0
Dim frm As Form
Dim qrystr$, yrprt$

If Not (IsLoaded("MultiSiteMrB")) Then
Cancel = True
MsgBox "To preview or print this report, you must
open " _
& "the MultiSiteMrB Form first.", vbExclamation, _
"Must Open MultiSiteMrB"
Exit Sub
End If

Set dbsReport = CurrentDb
Set frm = Forms!MultiSiteMrB
strtclmn% = 6

'Build SQL string base
sql$ = "SELECT Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart('yyyy',[SHIPDATE]) AS Yr,
DatePart('" + Trim(frm!txtTimePrd) + "',[SHIPDATE]) AS
Period FROM Totalpounds "

sql$ = sql$ + "Where Totalpounds.Vnd='" + Trim(frm!
txtVend) + "'"

sql$ = sql$ + " AND UCASE(LEFT(Totalpounds.Cust,5))='" +
Trim(frm!txtCust) + "'"

sql$ = sql$ + " AND (DatePart('yyyy',[SHIPDATE]) Between "
+ Str$(frm!txtStYr) + " And " + Str$(frm!txtEndYr) + ") "

sql$ = sql$ + " AND ((DatePart('" + frm!txtTimePrd + "',
[SHIPDATE])) Between " + Str$(frm!txtStrPrd) + " And " +
Str$(frm!txtEndPrd) + ") "

sql$ = sql$ + " AND ((UCase(Left([Totalpounds].[Loc],5)))
Between UCase(Left('" + frm!txtStrLoc + "',5)) And UCase
(Left('" + frm!txtEndLoc + "',5))) "

'set Transform line of SQL
If Forms!MultiSiteMrB!optCases.Value = 0 Then
sql1$ = "TRANSFORM Sum(Totalpounds.TotWgt) AS Wgt "
Else
sql1$ = "TRANSFORM sum([Totalpounds].[Quantity]) AS
Cases "
End If
'Set Pivot line of SQL
sql4$ = " PIVOT Left([Totalpounds].[Loc],9);"
'Set Group and Order by line
grpord$ = " BY Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart('yyyy',[SHIPDATE]), DatePart('"
+ frm!txtTimePrd + "',[SHIPDATE]) "

'build Where part of SQL based on selections on form
If Forms!MultiSiteMrB!optJCC.Value = -1 Then sql2$ = " And
ucase([Totalpounds].[Loc]) like '*JETRO CASH*'"

If Forms!MultiSiteMrB!optRD.Value = -1 Then sql2$ = " And
ucase([Totalpounds].[Loc]) like '*RESTAURANT DEPOT*'"

If Forms!MultiSiteMrB!optNY.Value = -1 Then sql2$ = " And
([Totalpounds].[State] Not In ('IL','WI','FL','OH') And
left([loc],3)<>'131' And right([loc],3)<>'113') AND
[Totalpounds].[Zip] not like '15%'"

If Forms!MultiSiteMrB!optFL.Value = -1 Then sql2$ = " and
[Totalpounds].[State]='FL'"

If Forms!MultiSiteMrB!optNE.Value = -1 Then sql2$ = " And
[Totalpounds].[State] Not In ('IL','WI','FL')"

If Forms!MultiSiteMrB!optNEng.Value = -1 Then sql2$ = "
And ([Totalpounds].[State] In ('NY','MA') Or
([Totalpounds].[State]='NJ' And val([zip])<8000))"

If Forms!MultiSiteMrB!optMW.Value = -1 Then sql2$ = " And
([Totalpounds].[State] In ('IL','WI','OH') OR
([Totalpounds].[Zip] like '15%'))"

If Forms!MultiSiteMrB!optMA.Value = -1 Then sql2$ = " And
([Totalpounds].[State] Not In
('IL','WI','FL','NY','MA','NJ','OH') Or ([Totalpounds].
[State]='NJ' And val([zip])>8000)) And [totalpounds].[zip]
not like '15%'"

If Forms!MultiSiteMrB!txtVend = "AMPI" Then

If Forms!MultiSiteMrB!optCheese.Value = -1 Then sql3$ = "
And ([totalpounds].[id] Like ('1*'))"

If Forms!MultiSiteMrB!optButter.Value = -1 Then sql3$ = "
And ([totalpounds].[id] Like ('4*'))"

If Forms!MultiSiteMrB!optOther.Value = -1 Then sql3$ = "
And ([totalpounds].[id] Not Like ('1*')) And
([totalpounds].[id] Not Like ('4*'))"

End If

'Put SQL statement all together from pieces built above
sqlall$ = sql1$ + sql$ + sql2$ + sql3$
sqlall$ = sqlall$ + " Group " + grpord$
sqlall$ = sqlall$ + " Order " + grpord$
sqlall$ = sqlall$ + sql4$

' Open Recordset object with built query string
Set rstReport = dbsReport.OpenRecordset(sqlall$)

' Set a variable to hold number of columns in crosstab
query.
intColumnCount = rstReport.Fields.Count

End Sub 'end report_open
----------------------
The detail section is below:
---------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
' Place values in text boxes and hide unused text boxes.

Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into
text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls(rstReport
(intX - 1))
Next intX
'--------------------------------------------------
'This section moved here from Detail_print event
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
'end of transplanted Detail_print section
'--------------------------------------------------
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else 'this code added to debug blank line issue
cntx = cntx + 1
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
Debug.Print cntx
End If
End Sub 'end of detail section
-------------------------------------------
A report with predefined column header labels is not
viable since stores change. This works except for the
issue I described originally and is much more flexible for
my customer. They don't have to call me every time a
customer adds or closes a store. They may need to call me
if they realign divisions but that has only happened once
in 6 months and was a relatively quick fix. A sample run
to look at is at:

http://www.sandorustech.com/PLreport2.gif

The text you see is the total # of records returned. The
totals for those records was printed 53 lines later. The
code in the Else clause gets hit 52 times after the query
has reached EOF. Sorry for the length of this.
 
Rod,
My solution does not ever require anyone to add any controls to the report
or make any modifications to any query or any other object. The crosstab
report in the Crosstab.mdb will handle all of this automatically. There is
very little code and it runs much more efficiently than the Solutions.mdb.

Make sure you are looking at the demo that comes up with a dialog form that
allows you to enter a date range and select the number of columns (2-6) from
a list box. The report displays with "Sales Report By Employee and
Customer". It will accept any number of Customers as column headings.
Setting the number of columns will not limit the number of actual customers
displayed. It will only limit the number displayed across the page.
Additional customer columns will "wrap" under the first group of customers.

I believe the demo is Access 97. If you convert it to 2000 or later, make
sure you compile the code prior to running.

--
Duane Hookom
Microsoft Access MVP


Rod said:
Duane,

I haven't said your solution won't work. What I've been
saying is that it is not a viable solution for my customer
nor is it the type of solution I would like to present to
them.

"Mr. Customer, here's your reports. Oh, BTW, if one of
your customers adds a store you'll have to come back to me
to modify the reports. Oh, BTW, if your customer closes a
store you'll have to come back to me to modify the
reports. Oh, BTW, if your customer changes a store number
or name you'll have to come back to me to modify the
reports. Oh, BTW, if your customer realigns reporting
groups (regions/divisions) you'll have to come back to me
to modify the reports."

I can't in all good conscience deliver that type of
solution.

Here's what I have.
Master query that contains the following fields:
[VendorID]
[ShipCo]
[Company]
[ProductID]
[ProductID]+"-"+[ProductDesc]
[Quantity]
[Quantity]*[CaseWeight]
[SHIPDATE]
[State]
[Zip]

A form and report that you can look at
http://www.sandorustech.com/PLform.gif
http://www.sandorustech.com/PLreport.gif

The report open even builds the SQL statement based on
criteria selected on the form.
-------------------------
Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim qryset1 As Boolean
Dim qdf As QueryDef
Dim sql$, sql1$, sql2$, sql3$, sql4$, sqlall$, grpord$
Const conFormDesign = 0
Dim frm As Form
Dim qrystr$, yrprt$

If Not (IsLoaded("MultiSiteMrB")) Then
Cancel = True
MsgBox "To preview or print this report, you must
open " _
& "the MultiSiteMrB Form first.", vbExclamation, _
"Must Open MultiSiteMrB"
Exit Sub
End If

Set dbsReport = CurrentDb
Set frm = Forms!MultiSiteMrB
strtclmn% = 6

'Build SQL string base
sql$ = "SELECT Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart('yyyy',[SHIPDATE]) AS Yr,
DatePart('" + Trim(frm!txtTimePrd) + "',[SHIPDATE]) AS
Period FROM Totalpounds "

sql$ = sql$ + "Where Totalpounds.Vnd='" + Trim(frm!
txtVend) + "'"

sql$ = sql$ + " AND UCASE(LEFT(Totalpounds.Cust,5))='" +
Trim(frm!txtCust) + "'"

sql$ = sql$ + " AND (DatePart('yyyy',[SHIPDATE]) Between "
+ Str$(frm!txtStYr) + " And " + Str$(frm!txtEndYr) + ") "

sql$ = sql$ + " AND ((DatePart('" + frm!txtTimePrd + "',
[SHIPDATE])) Between " + Str$(frm!txtStrPrd) + " And " +
Str$(frm!txtEndPrd) + ") "

sql$ = sql$ + " AND ((UCase(Left([Totalpounds].[Loc],5)))
Between UCase(Left('" + frm!txtStrLoc + "',5)) And UCase
(Left('" + frm!txtEndLoc + "',5))) "

'set Transform line of SQL
If Forms!MultiSiteMrB!optCases.Value = 0 Then
sql1$ = "TRANSFORM Sum(Totalpounds.TotWgt) AS Wgt "
Else
sql1$ = "TRANSFORM sum([Totalpounds].[Quantity]) AS
Cases "
End If
'Set Pivot line of SQL
sql4$ = " PIVOT Left([Totalpounds].[Loc],9);"
'Set Group and Order by line
grpord$ = " BY Totalpounds.Vnd, Totalpounds.Cust,
Totalpounds.Item, DatePart('yyyy',[SHIPDATE]), DatePart('"
+ frm!txtTimePrd + "',[SHIPDATE]) "

'build Where part of SQL based on selections on form
If Forms!MultiSiteMrB!optJCC.Value = -1 Then sql2$ = " And
ucase([Totalpounds].[Loc]) like '*JETRO CASH*'"

If Forms!MultiSiteMrB!optRD.Value = -1 Then sql2$ = " And
ucase([Totalpounds].[Loc]) like '*RESTAURANT DEPOT*'"

If Forms!MultiSiteMrB!optNY.Value = -1 Then sql2$ = " And
([Totalpounds].[State] Not In ('IL','WI','FL','OH') And
left([loc],3)<>'131' And right([loc],3)<>'113') AND
[Totalpounds].[Zip] not like '15%'"

If Forms!MultiSiteMrB!optFL.Value = -1 Then sql2$ = " and
[Totalpounds].[State]='FL'"

If Forms!MultiSiteMrB!optNE.Value = -1 Then sql2$ = " And
[Totalpounds].[State] Not In ('IL','WI','FL')"

If Forms!MultiSiteMrB!optNEng.Value = -1 Then sql2$ = "
And ([Totalpounds].[State] In ('NY','MA') Or
([Totalpounds].[State]='NJ' And val([zip])<8000))"

If Forms!MultiSiteMrB!optMW.Value = -1 Then sql2$ = " And
([Totalpounds].[State] In ('IL','WI','OH') OR
([Totalpounds].[Zip] like '15%'))"

If Forms!MultiSiteMrB!optMA.Value = -1 Then sql2$ = " And
([Totalpounds].[State] Not In
('IL','WI','FL','NY','MA','NJ','OH') Or ([Totalpounds].
[State]='NJ' And val([zip])>8000)) And [totalpounds].[zip]
not like '15%'"

If Forms!MultiSiteMrB!txtVend = "AMPI" Then

If Forms!MultiSiteMrB!optCheese.Value = -1 Then sql3$ = "
And ([totalpounds].[id] Like ('1*'))"

If Forms!MultiSiteMrB!optButter.Value = -1 Then sql3$ = "
And ([totalpounds].[id] Like ('4*'))"

If Forms!MultiSiteMrB!optOther.Value = -1 Then sql3$ = "
And ([totalpounds].[id] Not Like ('1*')) And
([totalpounds].[id] Not Like ('4*'))"

End If

'Put SQL statement all together from pieces built above
sqlall$ = sql1$ + sql$ + sql2$ + sql3$
sqlall$ = sqlall$ + " Group " + grpord$
sqlall$ = sqlall$ + " Order " + grpord$
sqlall$ = sqlall$ + sql4$

' Open Recordset object with built query string
Set rstReport = dbsReport.OpenRecordset(sqlall$)

' Set a variable to hold number of columns in crosstab
query.
intColumnCount = rstReport.Fields.Count

End Sub 'end report_open
----------------------
The detail section is below:
---------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
' Place values in text boxes and hide unused text boxes.

Dim intX As Integer
Dim lngRowTotal As Long
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into
text boxes in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls(rstReport
(intX - 1))
Next intX
'--------------------------------------------------
'This section moved here from Detail_print event
lngRowTotal = 0
For intX = strtclmn% To intColumnCount
' Starting at first text box with crosstab value,
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me
("Col" + Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
'end of transplanted Detail_print section
'--------------------------------------------------
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
Else 'this code added to debug blank line issue
cntx = cntx + 1
For intX = 1 To intColumnCount
Me("Col" + Format$(intX)) = Null
Next intX
For intX = intColumnCount + 1 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
Debug.Print cntx
End If
End Sub 'end of detail section
-------------------------------------------
A report with predefined column header labels is not
viable since stores change. This works except for the
issue I described originally and is much more flexible for
my customer. They don't have to call me every time a
customer adds or closes a store. They may need to call me
if they realign divisions but that has only happened once
in 6 months and was a relatively quick fix. A sample run
to look at is at:

http://www.sandorustech.com/PLreport2.gif

The text you see is the total # of records returned. The
totals for those records was printed 53 lines later. The
code in the Else clause gets hit 52 times after the query
has reached EOF. Sorry for the length of this.
Click to expand...
 
Back
Top