Line Counter in report

[Mail Merge Help]

Hi

Hi have a column report where I want to number the line. I've created an
unbound textbox and used Running Sum= Over all. Which works fine.

My issue is that it also number the blank record of a column. ie, if column
1 doesn't have any record, it will count as line 1, then column 2 have 3
records, the counting continue, ie 2, 3, 4, then column 3 is blank but it's
still count as line 5.

I would appreciate any help on how to eliminate this problems, ie don't
count line number for blank record.

Thanks

 

[Larry Linson]

Hi have a column report where I want
to number the line. I've created an
unbound textbox and used Running
Sum= Over all. Which works fine.

My issue is that it also number the blank
record of a column. ie, if column 1 doesn't
have any record, it will count as line 1, then
column 2 have 3 records, the counting
continue, ie 2, 3, 4, then column 3 is blank
but it's still count as line 5.

I don't understand what you mean by "the blank record of a column." If there
is no Record, in a Report there will be no Line -- so you must have Records
for which the Field that is ControlSource for the Control in Column 1 is
blank. If you create an unbound Control, you are correct, for every Line
(and that is, for every Record), it will exist and be included in the
Running Sum, whether there's a value in every Field or not.

And, by the way, in Access-speak, "Columns" do not "have Records."

Rather than you relying on our psychic abilities to determine what you
actually have and what you want to do with it, perhaps you could clarify.

I would appreciate any help on how to eliminate
this problems, ie don't count line number for
blank record.

I'm certain that someone here will try to offer a useful suggestion, if you
will only help us to understand what the problem really is. Here are a
couple of "shots in the dark," just guessing at what you may have and what
you are asking.

(1) Perhaps, if they are not to be counted, that means the "blank records"
are not needed and should be deleted from the table -- if they are used
elsewhere, though, that would not be a good solution.

(2) Alternatively, depending on whether the Field is actually "blank" or
"Null", you might use Criteria in the Query that is the Report's
RecordSource to not select them. If however, there is other information in
those Records that you want to display, but not to count, then that would
not be a good solution.

Larry Linson
Microsoft Access MVP

 

[Mail Merge Help]

Hi Larry

for example,

Below is my 3 column report

Surname in the Header area : Column 1 Column 2 Column 3
Smith Mason
Bryce


Book order in Details area 1 2 bookA 4
bookC
3 bookB


From the above sample, Smith doesn't have any book, but in the details
field, the running sum display 1. Where I want 1 to start from BookA under
Mason surname.

I hope the above example clear the cloud a bit.

Thanks for your help.

 

[Mail Merge Help]

Cheese, my example doesn't display properly.

On the head of each column is the surname , Smith in Column1, Mason in
Column 2 and Bryce in Column 3.

In details area, Smith doesn't have any book, Mason have bookA and BookB,
and Bryce have book C.

At the moment, the running sum, show line 1 under Smith where there is no
record, and line 2 for bookA, 3 for Book B, 4 for Book C.

What I want is for the number to start counting there is record, ie start 1
from Book A under Mason surname.

Thanks

 

[Larry Linson]

Examples of the output are nice, and helpful, but to help, we need to know
what your data is in the tables and records... Do you have "Smith", "Mason",
and "Bryce" all in one Record, or is each in a separate record?

From your description, it would seem that you have joined the Person and
related Books records, with a Join described as "All Records from the
Persons table and only those that match from the Books table" which results
in a Record for the Person, whether there are any related Books or not, thus
your problem. (And that the columns you describe are actual columns in the
Report... defined in Print Layout.)

I can think of two ways to handle that:

(1) instead of the Query with a Join, make the Report based on the Persons
Table, and the related Books information in a Subreport, with the counter in
the Subreport. That way, if there's no book, there will not be a record to
be counted.

(2) instead of placing the 1 in an unbound Field on the Form, create the 1
as a Calculated Field in the query when there is a book, bind that Field,
and set its Running Sum property.

If I've misunderstood, post back to clarify. If not, please post back to
confirm.

Larry Linson
Microsoft Access MVP

 

[Mail Merge Help]

Hi Larry

I have followed your suggestion in Point 1, it works but it start a new line
number on every column, ie it doesn't continue the line number to the next
column. Any way to fix this?

I don't know how to work out your suggestion in point 2.

Thanks

 

[Larry Linson]

You see, that is the problem with having to guess about information that was
not provided. You did read my statement that the two suggestions were based
on _guessing_ at the information you had not provided, didn't you. I am not
surprised that my guess was not correct -- as I implied before, answering
questions about Access is a strong point of this newsgroup; guessing at
missing information is that we have requested is not our strong point; but,
the information I requested (before and here) would be a good starting
point.

I still don't know what the underlying data tables are, what your Report's
RecordSource is, or what the Control Source is for each of these Controls on
the Report, so I cannot be of assistance. I'm sorry, but it it just not
possible to work entirely from what your Report output is. If you don't
provide more information, I doubt anyone here can help you (to do so they
would have to 'just happen' to guess correctly about the missing
information). Remember, you have the database in front of you, but we only
have what you write to go by.

Larry Linson
Microsoft Access MVP

 

[Mail Merge Help]

Larry

But you have guess it correctly.

I have two tables, one for Surname where I data entry Smith, Mason and
Victor, etc. Another one to Many relationship to Book table.

I have follow your previous suggestion in Point one where I created a report
for Surname to have Smith, Mason and Victor on the Surname Header. In 3
column report.

Then another subreport for Book in the details area.


The Display on the report is ok. There is no line number for where there is
no book in a column.

But line number start as 1 on every column, it does not show continuously.


say, 3 columns report where Smith in column 1, Mason in Column 2 and Victor
in Column 3

There is no book for Smith, therefore the is no data below this column.

in Column 2 under Mason, there are 2 books. So the report display as line
number 1 for BookA and line number 2 for Book B.

In Column 3 under Victor, there are 1 book, instead of showing continuously
as line number 3 for book C, it restart the line number, ie, line number 1
for Book C.

I hope this will help a bit. As you know I have a database in front of me,
but it's hard to write out how the structure of the database in word.

Thank you very much for your help.

Have a nice day

 

[Larry Linson]

No, clearly I have not "guessed correctly" because I do not know the
following, so PLEASE copy the RecordSource of your Report, and if it is just
a Query name, also copy and paste the SQL; if it is just a Table name, also
list the Field names; then PLEASE list the Sections of your report, and
within each section list each Control, and for each control copy and paste
the ControlSource property.

I am sorry that I seem unable to convey to you that a description of the
report output is not enough information to be able to assist you -- we need
information about the underlying data which you are reporting.

Larry Linson
MIcrosoft Access MVP