Home Forums Discussions Problems Patient chart numbers

At some point in the past, after we created a new patient and AC assigned the chart #8033, the program skips assigning #8034 all the way to 9031.

So we have a pt acct 8033 and the next one is 9032 with nothing in between.

How can we create a new patient with chart numbers 8034, 8035, etc, all the way to 9031?

We are currently at chart #9367 and I don't want it to hit 10000, so I want to fill in the 8000s that are currently blank.

Anyone know why this would have happened?

Hello Serene,

Amazing Charts uses a SQL Server feature called an "Identity" to create numbers for the ID field of a patient. Consider the following statement to create a table in a SQL Server database:

CREATE TABLE Demographics (
PatientID INT IDENTITY(1,1) NOT NULL,
FirstName NVARCHAR(20) NULL,
LastName NVARCHAR(20) NULL,
)

The second line which reads "PatientID INT IDENTITY(1,1) NOT NULL means the following:

* PatientID: The name of the column. You see this name and use this name when referring to the data in that column.
* INT: The data type. The type of data stored in this column is to be an Integer. Meaning, of course, numbers such as 1, 2, 3, 4.
* IDENTITY(1,1): The Identity specification. The two numbers in parenthesis are the seed and the increment. The seed means to start at the number 1. The increment means to increment by 1. You can have different seeds and increments. For example: IDENTITY(500,100) would mean to start at 500 and increment by 100 every time a new record is added. So 500, 600, 700, and so on.
* NOT NULL: The not null specification means this column can never be blank. It MUST contain a value whenever a new row is added to the table.

The PatientID of the demographics table in AC always starts at 1000 from what I've seen so it's safe to say it is IDENTITY(1000,1).

With that setting, every time a new patient is added to AC, it should go 1000, 1001, 1002, and so on. However, sequential is NOT guaranteed with IDENTITY. Therefore, it is possible to see some skipping. For example, if SQL Server caches the next 100 identity numbers but then the server restarts, some of the values might be lost so when the server comes back up the next IDENTITY will be 105 (assuming the last used was 5). So, yes, skipping is normal.

Also, an IDENTITY column is IMMUTABLE. That means user code cannot change it. Only the system can.

The only way to fix this issue is to perform surgery on your database. The steps would be to create a temporary column in your Demographics table, copy the existing PatientID values to that temp column, delete the PatientID column, create a new PatientID column and number it with fresh numbers that are sequential, then use the temp column as a reference to update all other tables that have a foreign key relation to your Demographics table with the new IDENTITY. When the time comes, you may wish to reach out to AC support to see what they have to say about this issue.

If they are of no help, then I can, of course, do this kind of work.

James

Hi Serene,

Pardon my curiosity, but why don't you want your AC chart numbers to hit 1000?

Even a single skipped chart number would keep the likes of me awake at night, but I'm hoping that you have a specific problem about hitting chart number 1000 that might interest other AC users.

Cheers,

Carl Fogel

James, thanks for the detailed explanation! AC just said that's the way it is and didn't address the missing chart numbers. I had a feeling it wasn't doable to fill in the missing numbers, unless you perform surgery like you said. I've actually been trying to use duplicate chart numbers for new patients.. but maybe this is not a great idea. I just.. also want to get rid of dups. The merge chart feature still leaves the dup chart there.

Carl, it's not a technical reason. We use the chart number a lot and I just don't want to write/remember 5 digits. I like my 4 digit patient numbers. We've had 4 digit chart numbers for 10 years, mistakes might be made when there are 5 digits? Just a guess.

Hi Serene,

The bad news is that there's no easy way to use your skipped-over 4-digit chart-numbers.

The good news is that you'll probably find the 5-digit chart-numbers almost as easy to remember as the 4-digit ones, since they'll all begin with 1 for the foreseeable future, much like an 800 telephone number prefix.

Cheers,

Carl Fogel

OH, doh. That's true.

Serene,

Let me address your first mention about creating patients with the same ID first.

Creating duplicate patient ID numbers is not a good idea. Consider the following scenario:

You have a patient called Serene Smith. Her ID is 2004. In the Demographics table, her record looks like this:

PatientID FirstName LastName AddressOne City State Zip
2004 Serene Smith 113 Deen St Warsaw NC 28408

You need to look up her insurance. So you go to the insurance table and look up the insurance for patient 2004.

InsuranceID PatientID Primary Secondary Tertiary
1004 2004 BCBS AETNA NONE

In Amazing Charts, to show you which insurance policy belongs to patient 2004, a query is executed like this:

SELECT Demographics.PatientID, FirstName, LastName, Primary, Secondary, Tertiary FROM Demographics INNER JOIN Insurance ON Demographics.PatientID = Insurance.PatientID

Your results will look like this:

PatientID FirstName LastName Primary Secondary Tertiary
2004 Serene Smith BCBS AETNA NONE

Now, imagine two patients had the same PatientID. You would get back two results. And this would be confusing because you really would have no idea which insurance went with which patient. There simply would be no one-on-one guarantee.

PatientID FirstName LastName Primary Secondary Tertiary
2004 Serene Smith BCBS AETNA NONE
2004 James Summerlin TRICARE UNITED BCBS

OR

PatientID FirstName LastName Primary Secondary Tertiary
2004 Serene Smith TRICARE UNITED BCBS
2004 James Summerlin BCBS AETNA NONE

With two patients having the same ID, both the results above are valid results as the IDs match. Unfortunately, you're not guaranteed which result to get back. Again, no one-to-one guarantee.

In database design theory, having two or more patients with the same ID would be in violation of what we call the First Normal Form. You can read more about First Normal Form here: First Normal Form Wiki

There are, in fact, several normal forms of database design. The first three are the most important.

Second Normal Form: Second Normal Form Wiki

Third Normal Form: Third Normal Form Wiki

And for the truly OCD there is Boyce-Codd Normal Form: Boyce-Codd Normal Form Wiki

Rarely will you see a database attain Boyce-Codd normal form. I've done it before, of course, but only in Academic environments (to get an A in my Database Theory class in college). For the vast majority of database designs, obtaining the first three normal forms is sufficient. Of course, most the EMR databases I've seen can really violate the normal forms often.

James

Serene,

I'll cover your second mention, getting rid of duplicate patients, here.

You are correct in that AC, and most EMR systems, do not actually delete a patient. Rather what they will do is mark a patient record has "hidden" so the particular record in question does not show up in search or reporting. This approach is made for compliance reasons. However, as you are finding, over time the duplicates can start to amass unless strict controls are placed over data entry.

Duplicate records are the bane of any database. They create lots of confusion and no one knows which record for a patient is really THE record.

Fortunately, I wrote a program some time ago that will de-duplicate an AC database. The program will merge chart records from one patient account to another then delete - actually delete - the duplicate record. There are some caveats to this program that you will need to be aware of. Hit me up if you would like to discuss some day.

While I do have this program available, I must stress there really is no substitute for training staff to ensure they do not enter a person into a system more than once. While things like duplication are expected to happen here and there, it should not reach a critical mass quickly.

James

Ok, I won't do it! It has already caused some problems