Custom Label Report

[Dave L]

I am looking for a code example to print a label report where each label
would show the first and last values in the account number field from the
source table for a specified interval.

If the specified interval were 20 records, the first label would show the
first and the 20th account numbers, the second would show the 21st and 40th
account numbers and so on.

 

[Jeff Boyce]

Dave

It may take a bit more description of YOUR data before folks can provide a
definitive answer.

For example, how are these records sorted? Are you saying that the account
numbers are used to sort the records? Are they sequential? Do they have
any 'gaps' in the sequence?

I would think that a first step would be to define the query (SQL) that
returns ALL the records, sorted in the sequence you need.

If you want to specify an "interval", you could use a form to collect that
information, for subsequent use.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dave L]

Jeff,

Thanks for the quick reply. The purpose of the report is to create labels to
place on boxes where loan files will be stored to indicate the range of loan
files in each box. In other words, if a series of boxes will each hold 20
loans, they must each be labeled with the START and END loan numbers.

The record source is a table ("BOC") of loan accounts ordered by the loan
number field ("ALT_ID") but there are gaps in the numbers. The SQL to select
all the records would simply be:

Select [ALT_ID] from BOC order by [ALT_ID]

I am using an unbound field in the report header to request and store the
interval (i.e. 20, 50, 75). The interval remians constant for the length of
the report. I am assuming that each label would have a [Start_ID] and
[End_ID] text control to display the Starting and Ending loan numbers for
each interval. For example, if the loan numbers began with 1001 and ran
consecutively (they don't) and the interval were 20, the Start_ID on the
first label would be 1001 and the End_ID would be 1020. The second label
would be 1021 and 1040, etc.

I am looking for the best method of moving through the database to populate
the controls. Your assistance would be greatly appreciated.

Dave

 

[Jeff Boyce]

Dave

There is undoubtedly a more 'elegant' solution, but ...

One approach might be to use a procedure to first retrieve the recordset,
then use .MoveNext and a For/Next loop to cycle through, capturing the
[StartNumber] and [EndNumber] before writing those out to a temporary table,
for subsequent use by the report.

(not pretty, I know!)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the quick reply. The purpose of the report is to create labels
to
place on boxes where loan files will be stored to indicate the range of
loan
files in each box. In other words, if a series of boxes will each hold 20
loans, they must each be labeled with the START and END loan numbers.

The record source is a table ("BOC") of loan accounts ordered by the loan
number field ("ALT_ID") but there are gaps in the numbers. The SQL to
select
all the records would simply be:

Select [ALT_ID] from BOC order by [ALT_ID]

I am using an unbound field in the report header to request and store the
interval (i.e. 20, 50, 75). The interval remians constant for the length
of
the report. I am assuming that each label would have a [Start_ID] and
[End_ID] text control to display the Starting and Ending loan numbers for
each interval. For example, if the loan numbers began with 1001 and ran
consecutively (they don't) and the interval were 20, the Start_ID on the
first label would be 1001 and the End_ID would be 1020. The second label
would be 1021 and 1040, etc.

I am looking for the best method of moving through the database to
populate
the controls. Your assistance would be greatly appreciated.

Dave

Dave

It may take a bit more description of YOUR data before folks can provide
a
definitive answer.

For example, how are these records sorted? Are you saying that the
account
numbers are used to sort the records? Are they sequential? Do they have
any 'gaps' in the sequence?

I would think that a first step would be to define the query (SQL) that
returns ALL the records, sorted in the sequence you need.

If you want to specify an "interval", you could use a form to collect
that
information, for subsequent use.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dave L]

Thanks Jeff. Elegant isn't required.

I was toying with your suggestion, however is there a method of populating
the values in the report itself (detail section?) using a for/next loop or
would that not be feasible.

Dave

There is undoubtedly a more 'elegant' solution, but ...

One approach might be to use a procedure to first retrieve the recordset,
then use .MoveNext and a For/Next loop to cycle through, capturing the
[StartNumber] and [EndNumber] before writing those out to a temporary table,
for subsequent use by the report.

(not pretty, I know!)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the quick reply. The purpose of the report is to create labels
to
place on boxes where loan files will be stored to indicate the range of
loan
files in each box. In other words, if a series of boxes will each hold 20
loans, they must each be labeled with the START and END loan numbers.

The record source is a table ("BOC") of loan accounts ordered by the loan
number field ("ALT_ID") but there are gaps in the numbers. The SQL to
select
all the records would simply be:

Select [ALT_ID] from BOC order by [ALT_ID]

I am using an unbound field in the report header to request and store the
interval (i.e. 20, 50, 75). The interval remians constant for the length
of
the report. I am assuming that each label would have a [Start_ID] and
[End_ID] text control to display the Starting and Ending loan numbers for
each interval. For example, if the loan numbers began with 1001 and ran
consecutively (they don't) and the interval were 20, the Start_ID on the
first label would be 1001 and the End_ID would be 1020. The second label
would be 1021 and 1040, etc.

I am looking for the best method of moving through the database to
populate
the controls. Your assistance would be greatly appreciated.

Dave

Dave

It may take a bit more description of YOUR data before folks can provide
a
definitive answer.

For example, how are these records sorted? Are you saying that the
account
numbers are used to sort the records? Are they sequential? Do they have
any 'gaps' in the sequence?

I would think that a first step would be to define the query (SQL) that
returns ALL the records, sorted in the sequence you need.

If you want to specify an "interval", you could use a form to collect
that
information, for subsequent use.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am looking for a code example to print a label report where each label
would show the first and last values in the account number field from
the
source table for a specified interval.

If the specified interval were 20 records, the first label would show
the
first and the 20th account numbers, the second would show the 21st and
40th
account numbers and so on.

 

[John Spencer]

If Alt+ID is unique then you could use a ranking query to assign one up
numbers to all the records

Ranking query: saved as qOne
SELECT A.Alt_ID, Count(B.ALT_ID) as Rank
FROM Boc as A LEFT JOIN BOC as B
ON A.Alt_ID > B.AltID
GROUP BY A.Alt_ID

Range Query: uses saved query to get results - probably SLOW if you have a
large number of records. You might need to get range start in one query and
range end in another and then join them

SELECT Alt_ID as RangeStart
, (SELECT Min(Alt_ID)
FROM qOne as Temp
WHERE Temp.Rank Mod 20 = 19
AND Temp.Rank > qOne.Rank) as RangeEnd
FROM qOne
WHERE Rank Mod 20 = 0
ORDER BY Ra



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dave

There is undoubtedly a more 'elegant' solution, but ...

One approach might be to use a procedure to first retrieve the recordset,
then use .MoveNext and a For/Next loop to cycle through, capturing the
[StartNumber] and [EndNumber] before writing those out to a temporary table,
for subsequent use by the report.

(not pretty, I know!)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the quick reply. The purpose of the report is to create labels
to
place on boxes where loan files will be stored to indicate the range of
loan
files in each box. In other words, if a series of boxes will each hold 20
loans, they must each be labeled with the START and END loan numbers.

The record source is a table ("BOC") of loan accounts ordered by the loan
number field ("ALT_ID") but there are gaps in the numbers. The SQL to
select
all the records would simply be:

Select [ALT_ID] from BOC order by [ALT_ID]

I am using an unbound field in the report header to request and store the
interval (i.e. 20, 50, 75). The interval remians constant for the length
of
the report. I am assuming that each label would have a [Start_ID] and
[End_ID] text control to display the Starting and Ending loan numbers for
each interval. For example, if the loan numbers began with 1001 and ran
consecutively (they don't) and the interval were 20, the Start_ID on the
first label would be 1001 and the End_ID would be 1020. The second label
would be 1021 and 1040, etc.

I am looking for the best method of moving through the database to
populate
the controls. Your assistance would be greatly appreciated.

Dave

Dave

It may take a bit more description of YOUR data before folks can provide
a
definitive answer.

For example, how are these records sorted? Are you saying that the
account
numbers are used to sort the records? Are they sequential? Do they have
any 'gaps' in the sequence?

I would think that a first step would be to define the query (SQL) that
returns ALL the records, sorted in the sequence you need.

If you want to specify an "interval", you could use a form to collect
that
information, for subsequent use.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am looking for a code example to print a label report where each label
would show the first and last values in the account number field from
the
source table for a specified interval.

If the specified interval were 20 records, the first label would show
the
first and the 20th account numbers, the second would show the 21st and
40th
account numbers and so on.

 

[Jeff Boyce]

Dave

You are welcome to explore that possibility ... I haven't tried it but you
could post back here with a solution that we all can learn from.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff. Elegant isn't required.

I was toying with your suggestion, however is there a method of populating
the values in the report itself (detail section?) using a for/next loop or
would that not be feasible.

Dave

There is undoubtedly a more 'elegant' solution, but ...

One approach might be to use a procedure to first retrieve the recordset,
then use .MoveNext and a For/Next loop to cycle through, capturing the
[StartNumber] and [EndNumber] before writing those out to a temporary
table,
for subsequent use by the report.

(not pretty, I know!)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the quick reply. The purpose of the report is to create
labels
to
place on boxes where loan files will be stored to indicate the range of
loan
files in each box. In other words, if a series of boxes will each hold
20
loans, they must each be labeled with the START and END loan numbers.

The record source is a table ("BOC") of loan accounts ordered by the
loan
number field ("ALT_ID") but there are gaps in the numbers. The SQL to
select
all the records would simply be:

Select [ALT_ID] from BOC order by [ALT_ID]

I am using an unbound field in the report header to request and store
the
interval (i.e. 20, 50, 75). The interval remians constant for the
length
of
the report. I am assuming that each label would have a [Start_ID] and
[End_ID] text control to display the Starting and Ending loan numbers
for
each interval. For example, if the loan numbers began with 1001 and ran
consecutively (they don't) and the interval were 20, the Start_ID on
the
first label would be 1001 and the End_ID would be 1020. The second
label
would be 1021 and 1040, etc.

I am looking for the best method of moving through the database to
populate
the controls. Your assistance would be greatly appreciated.

Dave

:

Dave

It may take a bit more description of YOUR data before folks can
provide
a
definitive answer.

For example, how are these records sorted? Are you saying that the
account
numbers are used to sort the records? Are they sequential? Do they
have
any 'gaps' in the sequence?

I would think that a first step would be to define the query (SQL)
that
returns ALL the records, sorted in the sequence you need.

If you want to specify an "interval", you could use a form to collect
that
information, for subsequent use.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am looking for a code example to print a label report where each
label
would show the first and last values in the account number field
from
the
source table for a specified interval.

If the specified interval were 20 records, the first label would
show
the
first and the 20th account numbers, the second would show the 21st
and
40th
account numbers and so on.

 

[Dave L]

Thanks John!

Your code ran flawlessly. The rank query ran slowly, but still was
acceptable. Upon further review I determined that a sequence field numbering
each record existed in the BOC table and was just not being used.
Consequently, I replaced your rank query with a simple select query and just
subtracted 1 from the rank so the correct records were chosen in the Range
query. The report runs perfectly now.

Your efforts are greatly appreciated.

Dave

If Alt+ID is unique then you could use a ranking query to assign one up
numbers to all the records

Ranking query: saved as qOne
SELECT A.Alt_ID, Count(B.ALT_ID) as Rank
FROM Boc as A LEFT JOIN BOC as B
ON A.Alt_ID > B.AltID
GROUP BY A.Alt_ID

Range Query: uses saved query to get results - probably SLOW if you have a
large number of records. You might need to get range start in one query and
range end in another and then join them

SELECT Alt_ID as RangeStart
, (SELECT Min(Alt_ID)
FROM qOne as Temp
WHERE Temp.Rank Mod 20 = 19
AND Temp.Rank > qOne.Rank) as RangeEnd
FROM qOne
WHERE Rank Mod 20 = 0
ORDER BY Ra



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dave

There is undoubtedly a more 'elegant' solution, but ...

One approach might be to use a procedure to first retrieve the recordset,
then use .MoveNext and a For/Next loop to cycle through, capturing the
[StartNumber] and [EndNumber] before writing those out to a temporary table,
for subsequent use by the report.

(not pretty, I know!)

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the quick reply. The purpose of the report is to create labels
to
place on boxes where loan files will be stored to indicate the range of
loan
files in each box. In other words, if a series of boxes will each hold 20
loans, they must each be labeled with the START and END loan numbers.

The record source is a table ("BOC") of loan accounts ordered by the loan
number field ("ALT_ID") but there are gaps in the numbers. The SQL to
select
all the records would simply be:

Select [ALT_ID] from BOC order by [ALT_ID]

I am using an unbound field in the report header to request and store the
interval (i.e. 20, 50, 75). The interval remians constant for the length
of
the report. I am assuming that each label would have a [Start_ID] and
[End_ID] text control to display the Starting and Ending loan numbers for
each interval. For example, if the loan numbers began with 1001 and ran
consecutively (they don't) and the interval were 20, the Start_ID on the
first label would be 1001 and the End_ID would be 1020. The second label
would be 1021 and 1040, etc.

I am looking for the best method of moving through the database to
populate
the controls. Your assistance would be greatly appreciated.

Dave

:

Dave

It may take a bit more description of YOUR data before folks can provide
a
definitive answer.

For example, how are these records sorted? Are you saying that the
account
numbers are used to sort the records? Are they sequential? Do they have
any 'gaps' in the sequence?

I would think that a first step would be to define the query (SQL) that
returns ALL the records, sorted in the sequence you need.

If you want to specify an "interval", you could use a form to collect
that
information, for subsequent use.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am looking for a code example to print a label report where each label
would show the first and last values in the account number field from
the
source table for a specified interval.

If the specified interval were 20 records, the first label would show
the
first and the 20th account numbers, the second would show the 21st and
40th
account numbers and so on.