Indicating skips in sequential numbers on a report

Bruce

Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce

Jan 18 '07 #1

Subscribe Reply

2089

Ben

I think the easiest solution is to tweak the recordset for your report.

How about creating a master set of record sequences and then joining
them to your actual report? That way you have real data for a query
based comparison.

e.g.
Master table
Record Seq
1
2
3

Now your real record set
real records
Record Seq Title
1 Record 1
3 Record 2

Now set the Recordset for your report to this query:
SELECT [master table].[Record Seq], [real records].Title
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

Ahh, but now you want to display "record skipped" if there is no entry
for that record. So use an if statement to add that text and use this
query as your report recordset:
SELECT [master table].[Record Seq], IIf(IsNull([Title])=True,"record
skipped",[title]) AS [Report Title]
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

And there you have it...

Bruce wrote:

Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce

Jan 18 '07 #2

fredg

On 18 Jan 2007 09:35:46 -0800, Bruce wrote:

Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce

See Microsoft KnowledgeBase article:
210234 "ACC2000: How to Flag Out-of-Sequence Numbers on a Report"
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Jan 18 '07 #3

Bruce

Ben wrote:

I think the easiest solution is to tweak the recordset for your report.

How about creating a master set of record sequences and then joining
them to your actual report? That way you have real data for a query
based comparison.

e.g.
Master table
Record Seq
1
2
3

Now your real record set
real records
Record Seq Title
1 Record 1
3 Record 2

Now set the Recordset for your report to this query:
SELECT [master table].[Record Seq], [real records].Title
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

Ahh, but now you want to display "record skipped" if there is no entry
for that record. So use an if statement to add that text and use this
query as your report recordset:
SELECT [master table].[Record Seq], IIf(IsNull([Title])=True,"record
skipped",[title]) AS [Report Title]
FROM [master table] LEFT JOIN [real records] ON [master table].[Record
Seq] = [real records].[Record Seq];

And there you have it...

Thank you Ben. I had considered manipulating the recordset itself
(essentially loading it with dummy records as you describe). I had
hoped to find a solution that did not require this and instead would
perhaps utilize the report's detail format and/or print events along
with a counter of some kind.

Bruce

Jan 18 '07 #4

Bruce

fredg wrote:

On 18 Jan 2007 09:35:46 -0800, Bruce wrote:

Surely someone has done this before, and I am guessing there is a
simple solution that is eluding me. I have a simple report based on a
recordset. For each record there is a field (RecNum) that is basically
a sequential number from 1 to n. Occasionally there are skips between
numbers. I would like to print 'record skipped' for each record
missing from the sequence. For example, if there were no records
numbered 5 and 6 in a sequence of records numbered 1 to 7 I'd like to
output something like the following:

1...
2...
3...
4...
record skipped
record skipped
7...

Short of creating dummy records, how might I achieve this? Thanks.

Bruce

See Microsoft KnowledgeBase article:
210234 "ACC2000: How to Flag Out-of-Sequence Numbers on a Report"
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks Fred. This might be something I can modify to meet my needs.

Bruce

Jan 18 '07 #5

Similar topics

12702

SQL Query - Find block of sequential numbers

by: Jenn L | last post by:

I have a database that is pre-populated with sequential part numbers. As people reserve the parts I update a flag to show the # is no longer available. Now they want the ability to take out a block of "x" number of sequential part numbers - say for example 5. If my database had the following numbers available: 101 104 105 110

Microsoft SQL Server

3884

Page event "skips" Detail formatting

by: Eddy Bee | last post by:

Hi there, I'm encountering an inexplicable problem with page formatting in reports. Here's the easiest way to explain it: The Detail section of my report contains two elements: And let's say I have the following VB code for the Detail_Format event:

Microsoft Access / VBA

3206

sequential number help

by: Lapchien | last post by:

I have list of numbers in a table (originally from autonumber in a different database) from 1 to 1,000,000. The list is not in sequential order - there are loads of numbers missing. How can I identify what numbers are missing? Thanks, Lap (I'd like to then use this 'missing number list' to use for new records, instead of autonumber - I think I need to use DMax - can someone summarise

Microsoft Access / VBA

17798

Easy sequential record numbers in Access queries

by: systems analyst | last post by:

This is my modification on an original solution posted by Trevor Best (trevor@microprism.com) back in 1996. Insert the following code in a Module in your Access data base. Option Compare Database Option Explicit Dim mlngCounter As Long Function ZeroCounter() mlngCounter = 0

Microsoft Access / VBA

12031

Sort Table; Then Assign Sequential Numbers

by: amywolfie | last post by:

Hi All: I know this is simple, but I just can't seem to get there: I need to sort a table by a text field (txtDescription), then assign sequential numbers to the field SEQUENCE in table. Every time a new record is added, the all SEQUENCE #'s must be reset. The re-numbering will be triggered by an EXPORT button.

Microsoft Access / VBA

2121

guaranteeing that a sequence never skips

by: David Garamond | last post by:

Am I correct to assume that SERIAL does not guarantee that a sequence won't skip (e.g. one successful INSERT gets 32 and the next might be 34)? Sometimes a business requirement is that a serial sequence never skips, e.g. when generating invoice/ticket/formal letter numbers. Would an INSERT INTO t (id, ...) VALUES (SELECT MAX(col)+1 FROM t, ...) suffice, or must I install a trigger too to do additional checking? -- dave

PostgreSQL Database

1515

Sequential number

by: Scott | last post by:

I need to have a field to generate the report number with the following format:- The first part consists of four digits for each year. It could be obtained from the date field entered manually. The second part consists of four digits for the report number of the year. It should be generated automatically when a record is created. The number must be incremented by 1 for each new record and not repeated in the year but can be repeated...

Microsoft Access / VBA

2449

Select records in sequential chronologial order

by: maxamis4 | last post by:

Hello folks, I have two forms a parent form and a subform. The parent form is an unbound form while the subform is a form that contains all a list of what I like to call 'in stock ' phone numbers. with in that subform the user has the ability to select between to radio buttons to do a bulk selection. The first option lets the user select the first X amount of numbers that he or she wants. The second option selects X amount of numbers but...

Microsoft Access / VBA

4145

Sequential Numbers on Forms

by: Hulm1 | last post by:

I have a form "Job_Edit" with a subform. The subform called "JobTask" is a continuous form and includes a look-up combo box for a range of tasks. After I choose say 4 separate tasks, I can print a report in the correct order no problem because I inserted a text box with control source =1 and running sum in the report. However, when I close the form and re-open it, the selected tasks have been re-ordered by their TaskID no. which of course is...

Microsoft Access / VBA

8402

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8315

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8829

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8734

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8508

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

5633

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4323

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2733

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1962

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP