Looking for missing numbers

[why]

The all_matters table has two fields called “ClosedFile1” and “ClosedFile2” unfortunately the data in these fields is not as standard as it should be so I do a regex pattern match and copy any number pattern “##-####” into a new table with the row ID called Closednumbers

The date looks like this see attached

I then have another table called Closed_File_Numbers that has just Closednumbers. I created it in excel and imported it.
The reason for this second table is the closed number is supposed to be sequential the first two digits are the year and the last four will start at 0001 and go up by 1 for every closed file. So, I added every number sequentially between 0001 and then the last number I could find for each year after 91.
Here is a screenshot

I then linked the two tables with a left outer join So I can see all numbers in the Closed_File_Numbers. See screenshot.

This works but it has several design flaws.

• The closed number has to be in Closed_File_numbers or it does not show up at all. We currently have about 2000 before 91
• We have to manually update Closed_File_Numbersduring the current year and add to it. such as 20-0100,20-101 etc.
• I need to incorporate a new table that has a close number and the other information called notinMS This table has data from physical files that are not in the other system or the all_matters table.

Goals.
Be able to see all Closed numbers in one list. Be able to see what Closed numbers are missing base on serialization of the closed number. Incorporate this using both data from all_matters and notinLMS.
Also, find a better way to handle the serialization numbers of future closed numbers.

 

[theDBguy]

Hi. Would you mind adding/posting an image of the result of your left outer join query and tell us why it's wrong or what's missing from it? Thanks.

 

[why]

Hi. Would you mind adding/posting an image of the result of your left outer join query and tell us why it's wrong or what's missing from it? Thanks.

So here is the image you requested. The left outer join is working as you can see below. Here is the issue. For example, let say someone finds a file with the closed number 00-0330 When they add it if it does not exist in the all_matters table it is placed in the notinms table. My issue is I can't figure out how to incorporate this table into what I have.

 

[theDBguy]

When they add it if it does not exist in the all_matters table it is placed in the notinms table. My issue is I can't figure out how to incorporate this table into what I have.

Hi. Thanks for posting the image as requested. Unfortunately, since I/we are not familiar with your database, I don't think I understand what you just said. What is all_matters and notinms tables and how do they related to Closed_File_Numbers table?

Can you provide us your business process? How does a "number" get "placed" into a table and why and how is that done?

 

[why]

Hi. Thanks for posting the image as requested. Unfortunately, since I/we are not familiar with your database, I don't think I understand what you just said. What is all_matters and notinms tables and how do they related to Closed_File_Numbers table?

Can you provide us your business process? How does a "number" get "placed" into a table and why and how is that done?

Thank you for trying to help!! Sorry for being unclear. I am linking to a previous post. that talks about the excel file. . I have an excel file I am getting from a separate system. This other system has most of the closed numbers. In the other system the fields Closed File 1 and Closed file 2 hold the closed number and the field is an unfiltered text field)

The excel file is Named all_matters1 is a linked table. I am updating and appending a table called all_matters. (Thanks The_Doc_Man) all_matters looks identical to the excel file. The Closed file 1 and closed file 2 fields the data is not clean. So in an effort to clean it up, I create a table called ClosedNumber and Closednumber1. This is where I use a regex to put the RowID and Closedfile pattern match. I delete these tables and recreate them every time I import the excel sheet. (This might not be the best) ClosedNumber contains all closed numbers from closedfile1 plus I append the ones from closed file2 that don't match closedfile1. So this is how I get most of the closed numbers.

NotinMS is a Table that has the exact fields as the all_matters table. I use it for a way to enter physical files that we find that the RowID is not in the all_matters table but that have a valid closed number.
(I have thought about the idea of adding these directly to the all_matters table. My concerns are 1. I would need to add a way in all_matters to determine the source like a boolean value. 2. and more importantly How to deal with the ROWID. I might need to copy it to the current not in LMS table first to get a rowID then append it to the all_matters table. This could present a problem because I could either use 1-10,000 for the ROWID or I would need to pick a number so high that the appends from the other system would not try and use the same ROWID)

The Closed_Files_Number was a way that I came up with to try and create a way to find the missing closed numbers.

 

[why]

I have resolved the first part of the issue by copying the data from notinMS to the all_matters table. This puts everything in one table.

 

[theDBguy]

I have resolved the first part of the issue by copying the data from notinMS to the all_matters table. This puts everything in one table.

Hi. Congratulations! So, what else is left?