If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Line Counter in report
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 |
#2
|
|||
|
|||
Line Counter in report
"Mail Merge Help" wrote
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 |
#3
|
|||
|
|||
Line Counter in report
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. "Larry Linson" wrote: "Mail Merge Help" wrote 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 |
#4
|
|||
|
|||
Line Counter in report
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 "Mail Merge Help" wrote: 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 |
#5
|
|||
|
|||
Line Counter in report
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" wrote in message ... 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 "Mail Merge Help" wrote: 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 |
#6
|
|||
|
|||
Line Counter in report
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" wrote: 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" wrote in message ... 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 "Mail Merge Help" wrote: 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 |
#7
|
|||
|
|||
Line Counter in report
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Mail Merge Help" wrote: 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 |
#8
|
|||
|
|||
Line Counter in report
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Mail Merge Help" wrote: 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 |
#9
|
|||
|
|||
Line Counter in report
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 "Mail Merge Help" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 "Mail Merge Help" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|