Calculated Field in Table?

I have a relational structure like this:
Table for Companies with whom we work
Table for each contract year for the Companies (each company can have several 1-yr contracts)
Revenue Share and Payments Tables for each Contract year

Right now I have an autonumber field as the Primary key for the Contract Year Table.

What I'd like to do is create an ID automatically that is more meaningful.  Say, "CompanyID-01, CompanyID-02, etc." rather than a) have the autonumber assign it or B) have to manually enter it.  

Is there a way to do that?
BBluAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You COULD ... do this in A2007/10 (for sure  A2010) ... but you cannot create a calculated field at the Table level prior to A2007/10

mx
0
mbizupCommented:
I'd leave the autonumber as-is, and display the text you want for he users' benefit with a textbox whose control source is something like:

= "CompanyID-" & format(Me.AutonumberField,000)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BBluAuthor Commented:
I am using 2010.  How do I do that DatabaseMX
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I have not used that feature nor would I ... apparently it has issues.  I would go with mbizup's approach if you can ...

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
See this:

http://www.allenbrowne.com/casu-14.html
v
v
"What about Calculated fields in Access 2010?"
0
BBluAuthor Commented:
Okay.  So we'll have, as an example, for contract year records for Company 13571:

13571-003
13571-015
13571-124

Is that correct?  Where they won't necessarily (probably not) be in succession?
0
BBluAuthor Commented:
Thanks for the link, DatabaseMX.  I guess that page/article verifies yours and mbizup's concerns about using this.  I'll stay clear of it, then.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yeah ... I personally would for SURE ....

mx
0
BBluAuthor Commented:
ok.  Got it.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The problem is, unfortunately that ... Microsoft typically does not fully implement these sorts of new 'features', and you end up with gotchas ... like the many issues with Name Auto Correct - also mentioned on Allen's site.

mx
0
mbizupCommented:
One thing you can do to get the type of sequence that you are looking for is to create a seperate field that simply 'counts' (per company, per year, or whatever), without having it calculate from anything else.  An example we have used at work is a numbering scheme for Discrepancy Reports, with each project having a seperate set of reports.

The current event of the form where such records are added can be used to do something like this:

If Me.NewRecord then
      ContractYearID = DMax("ContractYearID", "YourTableName", "CompanyID = " Me.CompanyID) + 1
End if

Open in new window

That simply keeps a sequential track of CompanyYearID per Company

Then rather than combining that with any other field, display the format you need in a textbox like this:

= [CompanyID] & "-" & format([CompanyYearID],000) 

Open in new window


The idea per Allen Browne's article is to not store redundant data which can be calculated from other fields...

And additionally to NOT rely on this type of sequence as a primary key - stick to your autonumber.
0
BBluAuthor Commented:
Got it.  Thank you both so much!
0
BBluAuthor Commented:
Thanks, Guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.