Storing a calculated Field on a table

[GAB]

Hi,

I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.

Gary

 

[RuralGuy]

I believe you have no choice but to code your way out of the corner into which you have yourself painted. This application will probably be painful for its entire life. What do you need from us?

 

[Jman883]

GAB, I have just recently accomplished this. Follow the link it will explain how this is done.

http://support.microsoft.com/kb/209172

Good luck

 

[GAB]

Thank you both Ruralguy and Jman for your quick response. Jman the link you supplied did the trick. Thank you for the insight. I will mark this solved

Gary

 

[RuralGuy]

Glad to hear you got it sorted.

 

[simple matt]

The right/wrong way

Hi, I hope you don't mind me posting after your problem has been sorted but I am creating a database and I need to create a job number which is made up from several delimeters from a record. Obviously this is a calculated field, I would like to store it, but would be more appreciative if you could tell me the best way around this as your previous posts indicated this is a bad way to do it. Am just looking to do things correctly.
Thanks for any help.
Matt

 

[RuralGuy]

If your job number is made up from several (fields?) in the record then it can easily be created any time you want it with a query. Why do you feel you need to store it?

 

[neileg]

Just my two penn'orth.

Sometimes (maybe 1 in 100 situations) it is pragmatic to store a calculated field, but you need to be sure that the data that forms the basis of the calculation will never change. If it can then don't think about storing the calculated field. Sooner or later, you will end up with the result no longer matching the underlying data. And which will be correct, the result or the data?

 

[simple matt]

Reason

Hi guys, thanks for your posts on this.
The reason I need to store it as opposed to a query is that later on I will need a function to search on this field. I am trying to create a job number from 2 letters made up from a company division and company branch, attached to the primary key (which is an autonumber). ie uk, parts, record 6 would be UP-6. This then gives users an indication as to whether the job is being dealt with by the right department (plus a primary key!).
I am probably approaching this in completely the wrong direction so any assistance or help is greatly appreciated.
Thanks Matt

 

[John_W]

Whatever function you're using to search could probably just as easily search on a query which has just generated the job number.

 

[RuralGuy]

You are aware that AutoNumbers do not guarantee sequential numbers, right?

 

[GAB]

Hi Matt,

I have attached a link to another forum that has an article of a work around to allow editable autonumbering. It allows you to modify an automated number and it will store the modified number on a table. Hope this helps.

Gary

http://www.vbaexpress.com/forum/forumdisplay.php?f=91

 

[simple matt]

Gab,
thanks for that link it looks as though it might help. I'm just at a bit of a loss as I'm still at the start stage and could do with knowing the correct method to do this.

Yes I am aware that autonumber may not be sequential, I'm just after a unique identifier, the letters I want to put with the number are purely for visible reference outside of the system while the autonumber is the record identifier - however the users aren't capable of understanding that when they perform a search they just type in the numerical part of the number, so it would be easier to resolve this issue before they even see the system.

Any further thoughts would be great, thanks again for all your help guys.
Matt

 

[The_Doc_Man]

It was stated earlier in this thread. With queries you can build fields that look like what they are really not. And SEARCH them just as though they were real. So any time you want to build something that is searchable but that hides or at least obscures something in the real table, think QUERY. A query will do ALMOST anything that a real table can do, and often can do it better.