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.