Access 2013: Creating a Calculated Field

Often times the information you need isn’t stored as a field within a database. As a matter of fact, certain fields make more sense to calculate whenever they are needed instead of storing the value in a table.

Microsoft Access 2013 makes this very easy.

To create a calculated you will need a query in Design View. Select the Create tab and in the Queries group click Query Design button. The QBE (Query by Example) window opens and the Show Table dialog box is ready for you to add the table or tables you need.

create-query-design

add-tables

Once the table / tables are in place you can insert the fields necessary for your query.

insert-fields

The calculated field will be entered in the next available column. It is often easier to see what you are typing if you open the Zoom window. That can be accomplished by Right-clicking the open field and selecting Zoom from the Context Menu.

zoom-tool

With the Zoom window open we can enter our calculation. The information to the left of the colon represents the caption we want at the top of the column. The information to the right of the colon is our calculation. In this case it is the curUnitPrice field. No table name is necessary because we are only using one table. The field name is wrapped in a pair of square brackets and is being multiplied by 0.1.

enter-code

Once the calculation is completed click OK on the Zoom window, save the query, and it is ready to run.

save-the-query

calculated-field

Access 2013: Saving a Filter as a Query

You find yourself working in an Access table filtering out some unnecessary data and it dawns on you this filtering is something you’ll end up doing frequently. It would make sense to store this process as a query in the database.

filter-number-filyers-greater-than

enter-a-value

filter-results

Okay, so now I have to switch over to the Create tab and design a new query based on the filter I just applied. Or do I? You can actually save the filter you just applied as a query. The process is simple.

Go to the File tab and choose Save As > Save Object As > Save As.

file-save-as-save-object-as

Once you click the Save As button you will be prompted to name the results as either a query, form, or a report.

as-query

It really is that simple. The filter you just ran is now a query and can be used whenever necessary.

query-results

 

Show Hidden Tables in Microsoft Access

If you’ve ever inherited a database someone else created in Microsoft Access you may have run across the dreaded “hidden-table” scenario. This is when the original developer of the database has hidden (probably in an effort to keep anyone from disturbing them) the source tables that contain your data from the Navigation Pane in Microsoft Access. You’ll realize this has happened, typically, because you’ll go to create a query and you’ll see a tables or tables as being available to include in your query, but strangely, you don’t see those same tables listed in the Navigation Pane in Access. To fix this problem in Microsoft Access 2010 you can:

  1. Right-click on the title of the Navigation Pane (It probably displays the words “All Access Objects” but may say “Tables” or “Queries,” etc. if you have changed your Navigation Pane View.
  2. Click on “Navigation Options” from the shorcut menu.
    access navigation pane
  3. In the Navigation Options dialog box, check the option to “Show Hidden Objects”
    show-hidden-objects
  4. The hidden Access tables should now appear, but are “grayed out.”  You can still open them, but to unhide them permanently, right-click on the table you want to unhide and select “Unhide in this Group”
    unhide-in-this-group

Want to learn more about Microsoft Access? See our Microsoft Access Training Courses or view our Microsoft Access Tutorials on YouTube.

How to Use VBA in Microsoft Access Tutorial


In this Microsoft Access Tutorial, you’ll see how to begin using VBA in Microsoft Access 2007. The basic concepts of objects, properties methods and events are explained and then you will see how to attach VBA code to the load event of a Microsoft Access Form and to the click event of a Command Button on a form. This tutorial is from our live, instructor-led online course: Microsoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.

Using ADO to Retrieve Data : Microsoft Access Tutorial

access-ado-vba-data
In this Microsoft Access Tutorial you’ll see how to connect to a database using ADO Connection, Command and Recordset objects in VBA code, how to populate text boxes from fields in the data retrieved, and how to create command buttons that use ADO Recordset methods to allow the user to move throughout the data. This content is from our live, instructor-led online MIcrosoft Access 2007 VBA Training course. To learn more, visit our Microsoft Access Courses Page.

Microsoft Access Training

SkillForge is proud to announce the most comprehensive Microsoft Access Training schedules available anywhere! Offering Introduction to Advanced courses in Visual Basic for Applications (VBA) – SkillForge offers training for students wanting to learn more about the Microsoft Access application no matter what level of experience they have.

Students can choose from:

And topics range from the basics of creating tables and queries to writing VBA code. And as a special bonus – all students enrolling in a Microsoft Access Training course will receive free either a self-paced, interactive e-learning course to review after class, or a Cert-Blaster skills assessment exam to assess their knowledge both pre and post-class! See our complete list of Access training courses and find your training in Access today!

MS Access Resources

ms-accessMicrosoft has recently published a very thorough set of MS Access 2010 resources to the TechNet site. Access has always been the part of the Office Suite that straddles the end-user/IT Professional fence. The resources on TechNet are definitely geared toward Access Developers rather than folks that might be using MS Access more casually. (more…)