One of the new features of Access 2010, (and one that will probably get the least ink because it is so simple), is the ability to add Calculated Fields in both Access and Access Services databases.
A Calculated Field is simply a Data Type you can select from the list of Field-types.
The obvious uses of this feature would be an extended total based on a quantity times a price. This would make reporting a lot easier for users without Access or SQL skills and removes the necessity for using a BeforeUpdate macro or VBA event fill the calculated field. Of course you could do this with a Data Macro in Access 2010 (which would allow you to produce the same result with tables linked from SQL Server or other data sources) but this is an easy and simple solution for native Access tables. Of course Calculated Fields are not scaleable – but bite me :-).
One use I will take advantage of will be the concatenation of First and Last Name in Full Name format or First Name/[Space]/Lastname or Lastname/,[Space]/Firstname depending on the requirement.
The result is like this:
In this example LastName = [LastName]&”, “&[Firstname] and Fullname =[FirstName]&” “&Lastname].
This is a very simple example, but if you control input by insisting on First and Last Names then this will work reliably and helpfully without ever having to think about it again. I like that kinda solution 🙂
Dick
Dick Moffat's Spreadsheet and BI Blog 
Putting this in SQL terms, how does this differ from using views with fields derived from multiple fields in the underlying table(s)? Just in having a GUI expression builder to define them?
I’m a bit of a normalization absolutist. This seems like a way to create ever larger flat files,and it so it’s pain deferred (and usually magnified) rather than pain eliminated.
Snarky: this looks like calculated fields from PC-File or Borland’s Reflex from way back in the late 1980s/early 1990s.
“Snarky: this looks like calculated fields from PC-File or Borland’s Reflex from way back in the late 1980s/early 1990s.”
Pretty much and yeas that’s pretty snarky. Not a big surprise.
“Putting this in SQL terms, how does this differ from using views with fields derived from multiple fields in the underlying table(s)? Just in having a GUI expression builder to define them?”
That’s the point I’m makiing, no need for generating a View or a Query or some kind of SQL statement to get the results you need. Of course it’s against the rules of purist SQL but in this instance I see many people taking advantage of this who either don’t know or don’t care about SQL purity….
“no need for generating a View or a Query or some kind of SQL statement”
Couldn’t they have the best of both worlds? If you start with the premise that Access is a GUI and not a database, then it doesn’t matter what Access does as long as it’s a normalized database underneath.
So have calculated fields, but when you put them in a query, don’t
SELECT FullName FROM Table1
instead, do
SELECT FirstName&” “&LastName As FullName FROM Table1
They’re storing that formula in the field anyway, so transfer it to the SQL statement.
It would probably still get the same reception as Lookup Fields, but it can be sold as an alternative interface for building [part] of a query. And maybe a novice user will look at the SQL one day and, god forbid, learn some SQL syntax.
Dick:
Of course creating this in a query is easy for us and might help users understand SQL. But frankly if they need stuff like this to teach them SQL then something’s wrong. But being able to not have to worry about this when using the records either in a Form or Report or inside a query without having to do the concatentation each time is handy – if not particluarly “kosher” 🙂
I stand by my support of this feature ….
Dick
So this is just “Let’s make Access like PC-File?”
There are flat file, list processing packages labeled as databases which may make more sense for people who want simplicity rather than an entry level RBBMS. Wouldn’t FileMaker make more sense for such people than Access?
If you use views, you can define derived fields just like you describe but without the storage overhead and potential problems due to data redundancy.
This is another aspect of different intended use. If all one does with data is make it into reports, then making everything flat files would simplify that process. Fine. OTOH, if one wants to analyze that data, normalization becomes more important than convenience.
Maybe no one in their right mind would ever try to use Access for any sort of analysis. If so, then Access devolving back into PC-File may make sense.
Anybody ever tell you you’re a downer?
It appears to me that the Access team has decided that Access will be used for simple lists a lot going forward. Apparently they think that will have no effect on “serious” development with Access but I doubt it. But for the kind of Access they seem to be promoting I think this is a good feature. It doesn’t prevent anybody from using the sexiest, coolest SQL or query syntax you might want regardless. Go right ahead … just don’t be so preachy…
Dick
If simple flat files with calculated fields is what users want, Excel would be a better tool than Access. The entire point to Access is to be able to do more sophisticated database tasks than Excel can handle. This isn’t one of them. And even with derived fields, I’d figure 99 out of 100 people who just want to work with flat files would find Excel easier to use than Access. Why not steer them to the better tool?
“Why not steer them to the better tool?”
Answer: Because sometimes it’s NOT the better tool.
Situation:
I have a 2T file-store that’s running out of space so – before I go out and invest $$$ in more drives – I wanted to do what “cleanup” I could.
I ran a specialized (windows) tool that recurses through the entire file structure, and prints out information about every file in the file-structure. (including SHA256 hases of each file)
I imported this into an Excel spreadsheet that became hundreds of thousands of records long. . . .
I bet you can figure out what happened next: When I tried to do *ANYTHING* with that spreadsheet, it took mortal minutes to complete – 10-15 min+ for starters.
I imported the whole shebang into an Access database, and I can slice-and-dice this data any way I see fit. And it takes place in less than 60 seconds.
Yes, the command interface may be simpler in Excel – but sometimes Excel won’t work, and not everybody out there is, needs to be, or even wants to be a PhD in SQL queries. They just want to get a job done.
The thing I *like* about Access is that it will help me create the query, and then I can flip over into SQL view and tidy the query up if I want.
So what if it has features that help the beginner / non SQL guru. IMHO if someone wants to be a SQL heavy-hitter, they won’t be using Access anyway.
What say ye?
Jim
I agree completely 🙂 …
Dick
Just curious about the kind of formulas supported in calculated fields… Can we do complex stuff as well..
You can use all the capability you could use in an Access Query:
It looks like there’s no ability to include Custom VBA functions but that sorta makes sense.
Any reason why you think they dropped Aggregate Functions which you can use in a Query Calculated column ?
Maybe that kinda makes sense in that this functionality is designed for within a single record for purposes of the record itself. Aggregations in this context wouldn’t make sense IMHO
Dick
Looking at this features convinces me that the Excel Team wants to make their product more like a database (Larger Grid, Structured References in Tables etc) and the Access team wants to make their product like Excel(Calculated Columns in Tables)
Now only if they would introduce R1C1 referencing in Access Formulas….
There was a big push a couple of years ago in the Access Team to get Excel users to move to Access. In order to do that they added functionality and UI that would be “friendly” to them. I’m not so sure that is as big a push any more.
On the Excel side the List capabilities and the Structured References are natural (and great) extensions of the Table and Database capability that has been evolving in Excel forever. I love that stuff but MS just doesn’t seem to know how to promote it.
As I’ve said before, when MS Marketing talks about Office development they just think of VBA or VSTO. The idea of Excel features having value seems to be getting lost in the message when in fact they should be the MAJOR story and VBA and VSTO secondary. If people aren’t using the Application at an advanced level then there isn’t much point in automating it really (IMHO as always).
Dick
“VBA and VSTO secondary” – I wish VBA was even secondary…. its Not.
Every thing MS Talks about has to have .NET(“real development”) & SharePoint(“IT Control”
But when the Office Marketing Team talks “Development” it’s VBA…. They will of course deny it but they seem to assume that everyone knows how to use Excel and Access after all these years – and nothing could be further from the truth.
On average my users were far more advanced 15 years ago than they are today (with a few exceptions of course). My theory about that is that because of the pressures of today’s business environment they simply don’t have the time for learning how to do thngs better – just “Git ‘er done !!”. So old habits and processes just get reused over and over again while new skills that would make them many times more productve sit idle.
I see that as an area that MS could use to get more “buy-in” for their technology (incuding Windows by the way) by encouraging better use of Excel and Access – but as I’ve said – that conflicts with their Corporate goal of helping IT get control of the Enterprise – and end-user computing is anathema to that.
It’s pretty obvious what’s going on – despite the “spin” to the contrary.
Dick
One more thing ….. .NET and VSTO are not the responsibility of “Office” Marketing. The SharePoint “Develpment” stroy can be summed up in the fact that the “Office” Developer conference last year was changed to the “SharePoint” developer conference…. Although there were apparently Excel and Access tracks they weren’t front and center (and I’m sure not well atteneded based on my experience in the prvious year at the “Office” conference)
I’m also SURE they were not about Excel spreadsheet “Best Practices” or Relational theory in Access Table, Query, Form and Report design (which is a SERIOUS problem out there)..
Dick
So, I guess calculated fields can’t call custom VBA functions, which is a bit of a bummer.
I do like the idea of the convenience of a calculated field, easily called from near its input arguments.
Regardless of your thoughts on Access users’ general ability to use SQL, you should be able to see some practical merit in not having to frequently repeat a calculation statement in every report or form in which you use it.
“Regardless of your thoughts on Access users’ general ability to use SQL, you should be able to see some practical merit in not having to frequently repeat a calculation statement in every report or form in which you use it.”
I don’t believe I said that Access people shouldn’t use Calculated Fields because they are so smart 😉 … at least I hope I didn’t. I agree TOTALLY that fields that are going to be calculated anyway every time their dropped ona form might as well be pre-calculated in a Calculated Field (i.e. The classic Extended Value – Price * Qty). I am by no mens a “purist” – if it works – use it !!
Thx for your thoughts.
Dick
Re: Calculated fields.
Sometimes I want to create another “index” column – so that I can preserve a particular sorted order – while working with the data since the original index gets scrambled if I run a query (for duplicate SHA256 hashes, for example)
What really bites is that Access can only export about 6K records at a time due to limitations on the clipboard – or on MY clipboard, whatever. So I have to break up the records to be exported into 6K chunks so I get all the data out of it. (I’m going to use the duplicates data to create a shell script that will nuke any files that are true duplicates.)
Unfortunately, Access only allows ONE “auto number” field in a table and I don’t want to fuzz arond with the original indexes, because I might need a derived table to map back to the original.
One thing that I’d really like to do is make another column with ascending numbers (1,2,3,4,…) for my own use. Unfortunately I have not seen any examples of how to do this. In Excel – trivially easy. In Access, darn near impossible!
What say ye?
Jim
I have created my own unique record numbers in a field in Access Services files by simply either having a “Before Insert” event on the entry form to add the max value+1 to athe field I want to increment. Or you could do it with a Data Macro as well in the Before Insert event as well.
“What really bites is that Access can only export about 6K records at a time due to limitations on the clipboard – or on MY clipboard, whatever. So I have to break up the records to be exported into 6K chunks so I get all the data out of it. (I’m going to use the duplicates data to create a shell script that will nuke any files that are true duplicates.)”
As far as that is concerned why can’t you export an entire Table to Excel? Why use the Clipboard, especially if there is this 6K limitation? Or if you’re trying to get the dat into Excel you could pull it into Excel using VBA code or a Data Query Table. Am I missing something here?
Dick
Dick,
(as an aside, I just noticed your moniker at the top of this thread – you are a sick, twisted little man! Just like me. 🙂 )
Back to business:
First, I have a database that has, literally, hundreds of thousands of records in it – every file in a 2T file share – 1 file = 1 record. Each record has maybe ten or so fields describing the file in detail – path, type, SHA1 and MD5 hashes, creation date, ad nauseum.
When I use the “export to Excel” function supplied on the ribbon’s “export” tool, I get that warning.
It makes absolutely no sense to me why Access has to use the clipboard to export to Excel. You’d think – both of them being Microsoft products – that Access could output directly to an .xls[x] file. After all, Access has absolutely no difficulty *importing* the data.
==============================
Unlike most of the SQL Gods that have weighed in on this thread, I am a relative newcomer to this stuff, and I have a job to do. I cannot afford to take two semesters of “Database and SQL” just so that I can sort a multi-gig file listing for potential duplicates.
Now don’t get me wrong. I think SQL rocks. And I “get it” enough so that most of the time when I flip to SQL view I can see where the query may not be going precisely where I want it to – and fix it.
As far as Access being “dumbed-down” is concerned, let me ask the detractors this: When was the last time you had to code anything of non-trivial size in Assembler? In one sense, assembly language is just like a live grenade: It’s very powerful, but you have to REALLY pay attention.
Languages like Basic, C, C+, C++, C+++, (etc), were ways that “assembly language” was “dumbed down” so that you didn’t have to go through all the pain of coding – and then debugging – a huge assembler file.
So, does that mean everyone who *DOESN’T* program assembly language is an idiot noob with no [testicles]? I don’t think so. And I am sure you would agree.
Likewise, I really appreciate the fact that Microsoft has taken what is actually a nice little database tool and made it so you can have it both ways. The “experts” can code in SQL (assembly language) if they want, and the rest of us can use a higher level interface to get things done.
What say ye?
Jim
Dick,
You said:
“I have created my own unique record numbers in a field in Access Services files by simply either having a “Before Insert” event on the entry form to add the max value+1 to [the] field I want to increment. Or you could do it with a Data Macro as well in the Before Insert event as well.”
Dick,
These sound like great solutions. With one teeny-tiny fly in the ointment: I really have no idea how to do that. I have spent much time searching both Microsoft’s many technical blogs and threads, I have searched the web in painful detail, and I have not found anything that describes what you are telling me.
i.e. What is the “before insert” event, and where do I find it?
Likewise, I get the idea of a “max_value=1”, but how do I start it with an initial value? If I start with a blank field, the initial value in that field starting with the first record is “null.” How do I go about telling Access how to add one to “null”? Note that this value is “null,” not “zero”. What’s the size of infinity if you add 1? I’ve never even THOUGHT I’d have to use L’hopital’s Rule in a database query!
As far as using VBA is concerned – this has gone far-and-away from the simple VBA I used with Access back in ’98-’99. I try to code something simple in VBA and I am simply stonkered. Where do I start? *HOW* do I start. Again, the usual Fountains of Wisdom on the ‘net start with the assumption that you already know what you’re doing.
And a “data table query” is the same as a “use this query to create a new table in your database container, right? I already did that to make a sub-table of my original data that contains *ONLY* the duplicated records. And it is this sub-table that I am trying to export to Excel. I don’t understand how an *additional* table query will make this any better as it’s already been filtered the way I want it.
Yes, everybody, I’m an absolutely clueless noob. That’s why I’m here, so that I can actually learn something from people who actually *HAVE* a clue.
What say ye?
Jim
Hi,
can you create a calculated field that records the current date and time when the record is created?
thanks
Peter
Actually SharePoimt by default creates 4 fields to capture the user who created the record and when and the last person who edited the record and when. For free…. Way cool…
Dick
I forgot to add, the database is a web database, I get an error about web-compatibilty when I use =Now()
Peter