Question Expression populated field

[Connorbop]

Hello,

(I'm pretty new to database design and everything I know is 100% self taught so I apologise if I'm talking rubbish, any tips are welcome)

I have a table (tblCases) linked with a form (Case Manager) in my database and one of the text boxes (txtCaseNo) is populated with the below expression to create a unique case reference number:

=IIf([Dummy]=True,"TEST RECORD","CMS" & "" & Format([StartDate],"ddmmyy") & "/" & [CaseID] & "" & IIf([Type]="01 - Case","/C",IIf([Type]="02 - Non-Asset","/N",IIf([Type]="03 - Project","/P",""))))

This created a case number looking something along the lines of "CMS/241016/1/C".

My question is: is there a way I can save the case number created by the expression into a field within "tblCases" called "CaseNo" so I can user the number elsewhere?

This is probably a very clunky way of creating a case reference number, but as I said, this is 100% self taught via Google.

 

[pbaldy]

Some thoughts here:

http://allenbrowne.com/casu-14.html

 

[plog]

My question is: is there a way I can save the case number created by the expression into a field within "tblCases" called "CaseNo" so I can user the number elsewhere?

Yes, but you shouldn't. One of the main purposes of database is to not store redundant data. That includes data you can calculate from other pieces of data--for example you wouldn't store date of birth and age, because with date of birth you can calculate age.

Your case number is like age, in that it can be calculated from other pieces of data already saved to your table. So you shouldn't store it. Instead you would calculate it when you need it.

With that said, I think you have chosen a poor way to calculate a reference number. Looking at your fields that comprise it, it seems that your system could generate duplicate case numbers. Also, what if the type changes? Or if someone miskeys a start date and its not caught until after the case number is assigned, then the start date gets corrected?

This is why you should instead use an autonumber primary key.

 

[Connorbop]

Yes, but you shouldn't. One of the main purposes of database is to not store redundant data. That includes data you can calculate from other pieces of data--for example you wouldn't store date of birth and age, because with date of birth you can calculate age.

Your case number is like age, in that it can be calculated from other pieces of data already saved to your table. So you shouldn't store it. Instead you would calculate it when you need it.

With that said, I think you have chosen a poor way to calculate a reference number. Looking at your fields that comprise it, it seems that your system could generate duplicate case numbers. Also, what if the type changes? Or if someone miskeys a start date and its not caught until after the case number is assigned, then the start date gets corrected?

This is why you should instead use an autonumber primary key.

Hello plog,

I know the way I have formed my case reference number is clunky however it does contain a primary key already within the table called "CaseID" - Your question is going to be why don't I use this as the reference number. It's important that the reference number contains information that can be understood at a glance. For example from "CMS241016/5/N" I can tell you that it's a case started on 24/10/2016, the unique backend ID within the database is 5 and it is not an asset of my business or "CMS201016/3/P" tell me it's a case formed on 20/10/2016, ID 3 and is a project file.

The reason I would like this saved is for my "Home" form which displays active cases relevant to the user logged in. I have a subform displayed on the Homepage showing only their cases but as the expression is on my "Case Manager" form within a text box, I'm not sure how to bring this across to my subform on the Homepage.

 

[Connorbop]

Some thoughts here:

allenbrowne.com/casu-14.html

From what I gathered, this seems to hint toward using queries to form my case reference number, would this make it easier to work with?

 

[plog]

In reverse order:

From what I gathered, this seems to hint toward using queries to form my case reference number, would this make it easier to work with?

Yes you would probably use a query to calculate your CaseNumber value..however--

It's important that the reference number contains information that can be understood at a glance.

Why?

What I normally do is create a search form and display all relevant information a user would need in determining the appropriate row showing a column for each piece of data that will be necessary:

Start Date || Case ID || Type

You're just cramming the same data into a character string and thinking it posseses special properties that it doesn't have.

It all comes down to how you are going to use this case number. From the sounds of it, you haven't made a case that it needs to exist at all.

 

[pbaldy]

From what I gathered, this seems to hint toward using queries to form my case reference number, would this make it easier to work with?

More accurately, it recommends not saving the value and calculating it on the fly. It then gives you a method to save it should you decide to do so.