Creating an Append Query in Microsoft Access

Creating an Append Query in Microsoft Access

This article talks about the importance of the Append Query and the appropriate way of creating them in Microsoft Access.

In Microsoft Access and all the other database management systems, queries as the heart of the software system which can execute numerous actions to make your database more systematised and functional. Queries prove to be useful for working on various tasks including returning recordsets in an ordered and filtered way, updating values, editing or deleting data, and even making a new table in the database.

Action Queries can be used for adding, changing, or deleting numerous records from a table, record or field at a click of a button.

The additional advantage of an Action Query is that user can preview their query results in Microsoft Access before running it.

Microsoft Access provides the following types of Action Queries:

  • Append
  • Update
  • Delete
  • Make-Table

Note that users cannot undo an action query and therefore, they must create a backup of the data that they want to update using the query.

Steps for creating an Append Query

Append query is a kind of (SQL statement) action query which lets users add records to a table. It is also called an Insert Query since it uses ‘INSERT INTO’ command as its SQL syntax. Users can use Append Query to add new tables or data to another table. It can likewise be used for fetching data from multiple tables.

To make this process extra clear, let’s use two of our existing tables — StaffID and StaffSalary.

Step 1: Open the database that has the records that you want to copy.

Step 2: Click the Create tab and select Query Design to open the query designer.image1

Step 3: At this point, you should be able to see the Show Table dialog box. Double-click the tables or queries that hold the records that you want to copy. For our example, we are going to use StaffID and StaffSalary. Click Close.image2

Step 4: Double-click each field that you want to append. Each of the fields you select will be shown in the Field row of the query design grid.image3

Step 5: Click Run on the Design tab to see the query result.

Step 6: Verify that the query returned the records that you want to copy. If you see the need to add or remove some fields, return to Design view and add or delete the fields.

Step 7: Convert the select query to an append query. To do this, select DesignView from the View drop-down menu on the Home tab.image4

Step 8: Click the Append button and a dialog box will show.image5


Step 9: Next, you need to specify whether to append records to a table in the current database or to a table in another database. If the table is in the current database, click Current Database in the dialog box and choose the table from the drop-down list. If the table is in another database, click Another Database then click Browse to select the destination database. Once you’ve chosen the database, select or enter the name of the destination table in the Table Name box. For our example, we will use the Staff table. After selecting, click the OK button.

Step 10: You then choose the destination fields. The way you select destination fields depends on how you created your SELECT query. If you added all the fields from your source table, Access will add all the fields in the destination table to the Append to row in the design grid. If you added every field to the query or used expressions, and the field names in the source and destination tables match, Access will automatically add the matching destination fields to the Append torow in the query. If you added individual fields or used expressions, and any of the names in the source and destination tables do not match, Access will add the matching fields and leave unmatched fields blank. In this case, you can click a cell in the Append to row and choose a destination field.

Step 11: Preview the query before running. Do this by switching to Datasheet View.

Step 12: After that, return to Design View and click Run to append the records.

You will then be able to see all the additional data which have been appended to the destination table.

In case you encounter a crash in MS Access, do not hesitate to use a specialised ‘accdb’ recovery tool. It can be the only thing to save you from the hassles of data loss. But remember, taking backups first will save you time and inconvenience.


Ben Beitler

United Kingdom

Planning Tips To Building A Microsoft Access Database

Planning Tips To Building A Microsoft Access Database

In my latest eBook on how to build a Microsoft Access database, I cover a section about planning your database before developers should delve in and implement. Before they know it, it’s too late when they realise how off track they are and now left with potentially a reversing and undoing exercise!

So, to get started on the right track, here’s an extract for your reference…

Reverse Engineering? I’m often asked How do you design a good database? My simple answer, IReverse engineer it!

It is a technique that I have used many times and it works very well for me. The process to a good database design has nothing to do with Microsoft Access or any other database application.

The methodologies I’ve come across over the years leave me with a lot of questions about “Do the methodologies often discussed really justify the end result?”

The amount of time and complexity of the techniques in database methodologies and system analysis is sometimes a huge overhead of your time and the theories are sometimes questionable – it may work for others and that’s great!

So here’s how I start the Reverse Engineering process…

Planning a database can (more so for newbie’s) send you up the wall! But if you spend some time making a task list of the steps that will be needed when you start building your database, checking each item when completed, this will be a good start and will keep you focused.

But hey, even before you type a single character on that keyboard and build your first table, the following process is a pen and paper exercise (or a text editor on your computer) and there is no requirement to even start up Access at this point.

Remember, Access is a tool for database management and it’s there to serve as a tool for your processes and not dictate to you how to run your business. So, this article talks about your processes and how it will fit into a structured database.

I have a 7 step ‘reverse engineering’ plan as shown below:image1

In fact, the first five steps are critical as they will determine and ultimately define the ‘back-end’ database system. The remaining two steps handle the ‘front-end’ database system and in some cases, are never considered as users may only wish to work with the ‘back-end’.

Take a look at my eBook How To Build an Access Database to drill into the finer detail and avoid that all embarrassing ‘pear-shaped’ database.


Ben Beitler

United Kingdom

8 Tips to Create Flexible Tables in Microsoft Access

8 Tips to Create Flexible Tables in Microsoft Access

Tables should be responsive, optimised and efficiently designed for your Microsoft access database.

Databases are all about tables and how they hold and then show the information stored in them. It is vital for them to be flexible, dependable and informative. That’s the reason why it is fundamental to create and assign the appropriate properties to such tables.8 Tips to Create Flexible Tables in Microsoft Access

The following 8 simple tips will help to plan and build your Microsoft access database tables:

1. Naming of the fields

The name of a field is its identity, so it should give a reasonable idea about the field’s function and data type. Arbitrary and inept name fields make your database pointless and confusing. Microsoft access database allows up to 64 characters for a field name which can contain letters, numbers, and spaces. However, good practice is to not include spaces in the field name as this can cause issues later on for more advanced functionality especially when working with VBA and SQL codes.

2. The use of field properties


Microsoft access database provides you with the option to assign properties to the fields such as format, caption, description, validation rule, and validation text (to name a few). Each property will improve in some cases, performance as well as change the look and feel of data values. This is the first level of changes that can be applied and act as defaults when working with related objects used later on in your design process with the likes of queries, forms and reports.

3. Data type effort

It is suggested to use only text-based data type (as the default) and not necessarily number-based regardless of whether it’s for numeric data storing. It preserves a bit more of memory and is more advantageous with overall performance. Only consider non-text based values if there is a real need for it namely, to calculate values elsewhere in the database.

4. The use of field size


Selecting a suitable field size and data type makes the table adaptable and leaves little room or space for incorrect values. Ideally pick the smallest field size and data type as it helps in the keeping your tables optimised and perform better. Note that only ‘Text’ and ‘Number’ data types can have their fields sizes adjusted and that all other data types are fixed which can be a waste of memory.

5. Enhance performance through indexing

Indexes sort and prioritise the data sensibly according to its data type and if it is applied well, then it can significantly enhance the overall performance. developers should not just set multiple indexes because can hamper the data-processing in general. Users still need to know that a ‘Primary Key’ index will automatically prioritise the data without having to manually work on it and that most of the tables in your Microsoft access database should use the ‘Primary Key’.

6. AutoNumber restrictions

If you want to apply a unique number (or counter value) for the records in your database tables, you should use the ‘AutoNumber’ field and it characteristic can (and assumed to be a default) function as a ‘Primary Key’. It works as a surrogate key and a table can have just one ‘AutoNumber’ field. Even though it begins with the value ‘1’ by default, users can edit the value as well before it is used in anger.

7. The use of analysis tools

To improve the design of your tables, you can use the ‘Table Analyzer’ wizard tool and the ‘Performance Analyzer’ tool. The ‘Table Analyzer’ assists in examining the table(s) and recommends a number of potential changes which you can decide to accept and apply. While ‘Performance Analyzer’ evaluates the whole database and gives you suggestions for enhancing the overall design.

8. Table properties

Much the same as fields, tables also have properties to describe their purpose. You can get to the table properties by opening the table in Design view. There you have the choice of properties under the View menu. There are properties such as Order By and Subdatasheet Name which indicates the order of sorting in Microsoft access database . For the most part, sorting is executed according to the ‘Primary Key’ although you can change according to your preferences. You can also disable the sub-datasheet name property by setting it to ‘None’.

The additional benefit is the ability to validate between two or more fields (using logical expressions) to help control and store logical data values.

On the off chance that you ever experience a data error while working on an Microsoft access database , you may want to take regular back-up and have several restore points to work with. Also, learn to regularly run a ‘Compact and Repair’ action which is a built-in tool for keep your data organised and error-free!

Bonus tip:

For a field name where it appears that more than one word has been applied and spaces can be frowned upon as poor practice, consider using the underscore character ( _ ) as the separator or Initial capitalise each word with no spaces to make it easier to read.

6 Advantages of Utilising Microsoft Access Database

6 Advantages of Utilising Microsoft Access Database

Access Database is an effective tool that supports businesses in completing complex processes and enhances the way corporations work. It enables them to consolidate hectic organisational responsibilities like saving data, record keeping, designing user-friendly forms and professional looking Reports.

With the added level of Microsoft Access programming (macros and VBA coding), experts can keep their business records reorganised smoothly and mimic user activities and support Business workflows. This advanced database system has decreased the level of ‘data-disarray’ and settled the issue of data loss significantly. 6 Advantages of Utilising Microsoft Access Database

To help understand the benefits, here are the 6 advantages of utilising Microsoft Access database:

1. Sample Databases.

The Microsoft Access database application includes simple and beneficial samples databases and capture data examples for clients, suppliers and general work processes. These models can be used to study about the real-world forms, reports, queries, tables. Learning these models makes it more effortless for users to make their own powerful database and use the contemporary styles they offer.

2.  Wizards (built-in tools)

Microsoft Access is considerably simple to use, and credit goes to the wizards. There are various wizards you can utilise to design a database instantly and easily. You are provided with numerous choices to select the desired sample for your form, query or table. You can even modify the samples conforming to your needs. Wizards make it fast and simple to run the Microsoft Access database .

3. Understanding the structure

To understand the structure of the database, it is important to build up an understanding of all relationships (RDBMS) that are included. Microsoft Access database has a relationship window that shows all database relationships. It enables end-users to specify the relationship between tables and queries which are use to support the forms and reports instantly and effortlessly.

4. Simple Integration

Databases created in MS Access can be exported without difficulty to other Microsoft Office applications. Microsoft Access database utilises standard language to carry out distinctive operations that are quickly understood by other software programs in Windows. For example, data created in Microsoft Access can be opened and edited in MS Excel too.

5. Easy Programming 

Microsoft Access database has an easy-to-use program that makes database management simpler. With the use of simple code, you can systematise the repetitive tasks which some are pre-built ready to apply. The feature of VBA (Visual Basic for Application) enables end-users to make more intricate and responsive code procedures without putting in too much effort. MS Access programming allows users to compose efficient procedure with the support of clues and graphical shortcuts (which are known as macros).

6. Cost-effective

The benefit that makes MS Access constantly is the database of choice as it costs are much cheaper compared to other elective database systems. Unlike larger database frameworks, MS Access does not necessitate a big amount of maintenance-charge. Seeing the total cost of development and maintenance, this virtually free database framework is much more economical and simply affordable as it’s normally shipped with most versions of Office 365.

MS Access programming isn’t a suggestion but an essential tool for businesses who need to keep their records orderly and tidy. This cost-effective database system accompanies a remarkable storage capacity and also gives multi-user support.

Despite the fact that that SQL (Structured Query Language) Server isn’t as full-bodied as with the likes of Microsoft Access database , makes this application the choice for most users , keeping the cost per ownership to an absolute minimum while maintaining a good level of performance.


Ben Beitler

United Kingdom


MS Access Database Images: How To Correctly Handle Them ?

MS Access Database Images: How To Correctly Handle Them ?

Microsoft access database was never really designed to handle images the same way other Office applications do and maybe it was deemed as bit of an afterthought.

Different versions lead to different challenges and the latest version (2016) still have issues ending sometimes up with invisible images on those forms and reports. This is further complicated by having a 32-bit version of Access 2016 (16.0.4229.1024) and perhaps not the 64-bit version installed but that may not be a good enough reason to use the 64-bit version at all (unless of course images are your thing!).

In Access, images should appear properly but some simply do not. The original Images used this application was the BMP file format and they continue to properly show up however, other graphic types such as GIF, JPG and PNG formats may end up as a blank non-starter!

One thing you could check out is the database’s Picture Property Storage Format when the picture was added to the form or report. This can be found under the ‘Access Options’ setting for the Current Database:

There two options are:

  • Preserve source image format (smaller file size)
  • Convert all picture data to bitmaps (compatible with Access 2003 and earlier)

If the image is added when the above option is set to the second option (Convert), the non-BMP graphics do not appear in the 2016 version.

The recommended approach here for a non-visible image is to check the above option and choose the older (second version) to see if it makes a difference.

I would recommend actually removing and re-inserting the image again, save and run the form or report. Repeat the same steps again but choose with the first option instead (and it should really be this option in an ideal world).

Still having problems?

As with earlier version of Microsoft Access, there are other techniques available to handling image files in forms, reports as well as tables:

  • Store the image in an OLE field and use a bound object frame to display the image.
  • Store the path to the image in a text field, using an image control to display the image.
  • Store the image as a binary large object bitmap (BLOB) in an OLE field, extract the image when required and use an image control to display the image.

Again, no method above is perfect but the first option maybe the easiest to add but can be the most problematic too as not everyone will want to use OLE due to bloated file size and memory allocation it uses, the old MS Paint application to create and save images and server side processing errors when displaying such images really do not help.

The second technique is generally regarded as the preferred option and is also fairly easy to implement. For a table, the path to the image is stored in a text field and the image is displayed using a standard image control object to the form or report.  However, a bit of VBA code is required to set the image control’s “picture” property to the path that is stored and keeps the database size to a minimum, increasing the overall performance of your MS Access application.

The VBA code will need to call the object name like for instance your named image control called ‘MyImageCtrl’, and one of its properties called ‘Picture’ and then set it to the string (text) full path like (example only):

MyImgaeCtrl.Picture = “C:\MyDocuments\AccessDB\Images\MyImage.Gif”

But it’s not a perfect solution either leaving it and will require more VBA code to handle multiple records which may have individual images too (not covered in this blog) leaving with the last option being the trickiest to complete as it also requires VBA code and has the advantage of storing images inside the database, meaning that password protection is possible and also avoids the bloated files first mentioned.  It will require the use of either DAO or ADO to collect the image file and read it into the OLE field (and is not covered here in this blog – please do some further research on this).

So, images may not be a perfect fit and web based applications which Microsoft access database  also can use maybe a good balance and workaround.

It still requires good form and report designs will compliment your images but don’t focus on these images too much and be distracted by the real reason and power that Microsoft access database offers.


Ben Beitler

United Kingdom

Access Queries: Date Criteria May Not Always Work with MS Access Query

Access Queries: Date Criteria May Not Always Work with MS Access Query

Learning about ms access query  is the key to a good database management system as it is the heart of any database application.

There are many ways and questions to ask a database using queries and mastering the special conventions and criteria will pay dividend and avoid silly mistakes, illogical record set results and even errors.

One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and it’s conventions.

Take a look at the ms access query below showing orders before the year 2016 and the design of the

query which suggested a date range from 1st January 2016 to 31stDecember 2016.

The criteria for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range).

It’s missing the ‘And’ operator and better still adding the ‘Between’ operator will capture the correct range too. The # (hash sign) is the correct convention and good check to ensure it’s the right data type too (date/time in this case).

A revised example is shown below:

If the field is a genuine date/time data type, you do not need to enter the # (hash sign) as it will automatically fill this in for you when moving the cursor elsewhere in the gird (another convention checker for you).

If however you see double-quotes (“ “) wrapped around the date expression, this suggests either you chosen the wrong field or the date field is actually a text data type and it will simply not work as expected.

One final point to consider and even check for you ms access query database and those queries is the regional setting of the operating system you are using. UK versus USA setting often throw up confusion and inaccuracies to so ensure you are using the correct date/time formats.

MS Access queries are very powerful indeed and there are many permutations available – take a look at my eBook Microsoft Access 2016 Database Queries.


Ben Beitler

United Kingdom

How to create an embedded macro?

How to create an embedded macro?

image13An embedded macro is a macro that is not displayed in the Navigation Pane under Macros; it is stored in the event properties of forms, reports, or controls. This can make your access database easier to manage because you don’t need to monitor separate macro objects that contain macros for a form or a report. To create an embedded macro, follow the steps below:

  1. Open the form or report that will contain the macro in Design view or Layout view. To open a form or report, right-click it in the Navigation Pane, and then click Design View or Layout View.image7
  2. If the property sheet is not yet displayed, press F4 to display it.
  3. Click the section that contains the event property in which you want to embed the macro. To select the entire form or report, choose Form or Report from the drop-down list at the top of the property sheet.image8
  4. In the property sheet, click the Event.image9
  5. Click the event property in which you want to embed the macro, and then click next to the box.image11
  6. In the Choose Builder dialog box, click Macro Builder, and then click OK.image12
  7. Choose an action from the drop-down list.
  8. Fill in any required information for the action.
  9. Repeat steps 7 and 8 until your macro is complete.
  10. Click Save to save your macro.

The macro will run each time the event is triggered. Access database lets you create a macro group as an embedded macro. However, only the first macro in the group runs when the event is triggered. Succeeding macros are disregarded unless they are called from within the embedded macro itself (for example, by the OnError action).

Example: Embedding a macro in the On No Data event of a report

When you run a report and its data source does not have any records, the report shows an empty page. You may prefer a message box shown instead, and that the report not be displayed at all. An embedded macro is the perfect answer for this circumstance.

  1. Open the report in Design view or Layout view.
  2. Press F4 to display the Property Sheet.
  3. Click the Event tab on the property sheet.
  4. Click On No Data.
  5. Click .
  6. In the Choose Builder dialog box, click Macro Builder, and then click OK.
  7. Add MessageBox as the first action.
  8. Enter the following arguments.image13
  9. Add CancelEvent as the second action.image14
  10. .Close the macro. You should be able to see [Embedded Macro] in the On No Data.image15
  11. Save and close the report.

When you run the report and no records are found, the message box will be shown. If you click OK in the message box, the report will cancel without displaying the empty page.

Edit a macro

To insert an action

Click the action below which you want to insert the new action, then select from the drop down option an action that you want to be inserted.

To delete an action

Click the action you want to delete, then clickimage16

To move an action

Click the action that you want to position, drag and drop it at the desired place or you may position it by clicking image17 Or image18

Run a macro

Standalone macros can be run in any of the following ways: directly, in a macro group, from another macro, from a VBA module, or in response to an event that occurs on a form, report, or control. Embedded macros can be run by clicking the Run Macro button which can be found on the Design tab while the macro is in Design view; otherwise, the macro will only run when its associated event is triggered.

Run a macro directly

To run a macro directly, do one of these steps:

  • Locate the macro in the Navigation Pane, then double-click the name.
  • On the access database Tools tab, in the Macro group, click Run Macro, then select the macro from the list, and then click OK.

Run a macro that is in a macro group

To run a macro that is in a macro group, do one of the following:

  • On the access database Tools tab, in the Macro group, click Run Macro button, and then select the macro from the list. access database includes an entry for each macro in each macro group, in the format macrogroupname.macroname
  • Click OK.
  • Specify the macro either as an event property setting on a form or report or as the Macro Name argument of the RunMacro action. Refer to the macro by using this syntax: macrogroupname.macroname.
  • Run a macro that is in a macro group from within a VBA procedure by using the RunMacro method of the DoCmd object, and by using that syntax to refer to the macro.

Run a macro from another macro or from a VBA procedure

Add the RunMacro action to your macro or VBA procedure.

  • To add the RunMacro action to a macro, select RunMacro from the action list, then set the Macro Name argument to the name of the macro that you want to run.
  • To add the RunMacro action to a VBA procedure, add the RunMacro method of the DoCmd object to your procedure, and then specify the name of the macro that you want to run.

Run a macro in response to an event on a form, report, or control

While you can now insert macros directly into event properties of forms, reports, and controls, you can also still create stand-alone macros and then bind them to events.

  1. After building your stand-alone macro, open the form or report in Design view or Layout view.
  2. Open the property sheet for the form or report, or for a section or control on the form or report.
  3. Click the Event tab.
  4. Click the event property for the event that you want to trigger the macro.
  5. In the drop-down list, click the name of the standalone macro.
  6. Save the form or report.

Ben Beitler

United Kingdom

Create a standalone macro– Part 2

Create a standalone macro– Part 2

As a reminder, Please review Create a standalone macro – Part 1image2

A standalone macro is a macro that is displayed under the Macro in the Navigation Pane.

To create a standalone macro, take the following steps:

  1. Click the Create
  2. Click Macro in the Macros & Code
  3. You should be able to see the Macro Designer by now. To add an action, type in the macro action or click on the drop-down menu to display the list from which you choose the action that you want to use.
  4. To add more actions to the macro, move to another action row, and then repeat the previous step. Access carries out the actions in the order in which you list them.
  5. Click Save when your done.

Creating a macro group

Create a macro group for several related macros.

  1. Click the Create
  2. Click Macro in the Macros & Code
  3. Once the designer is displayed, select Group from the drop-down list.image5
  4. Input the name you want to use for the macro group in the text box.image6
  5. Start adding the actions that you want to use. Input the macro action or use the drop-down list to select an action.
  6. Fill in the required information for the action.
  7. To add more actions, repeat steps 5 and 6.
  8. Click Save.

Ben Beitler

United Kingdom

What’s the new Macro Features from the release of Access database 2010?

What’s the new Macro Features from the release of Access database 2010?


In previous versions of Access database, many usually used functions could not be executed without writing VBA code. With the release of Access database 2010, new features and macro actions have been added to help remove the need for code. This makes it less demanding to add functionality to your database and helps make it more secure.

  • Embedded macros: You can now embed macros in any of the events given by a form, report, or control. An embedded macro is not displayed in the Navigation Pane; it becomes part of the form, report, or control in which it was created. If you make a duplicate of a form, report, or control that has embedded macros, the macros are also present in the duplicate.
  • Increased security: When the Show All Actions button is not highlighted in the Macro Builder, the only macro actions and Run Command arguments that are accessible for usage are those that do not require trusted status to execute. A macro built with these actions will execute even when the database is in disabled mode. Databases that contain macro actions that are not on the trusted list need to be explicitly granted trusted status.


  • Error handling and debugging: Access database 2010 provides new macro actions, which include OnError and ClearMacroError, that enable you to run certain actions when errors occur while your macro is running. Moreover, the new SingleStep macro action lets you enter single-step mode at any point in your macro, so that you can see how your macro performs one action at a time.


  • Temporary variables: Three new macro actions — SetTempVar, RemoveTempVar, and RemoveAllTempVars — let you make and use temporary variables in your macros. You can use these in conditional expressions to control running macros, or to pass data to and from reports or forms, or for any other purpose that needs a temporary storage place for a value.

    Create a macro

    As of Access database 2010, a macro or macro group can be held in a macro object, or a macro can be inserted into any event property of a form, report, or control. Embedded macros become part of the object or control in which they are inserted. Standalone macros are viewable in the Navigation Pane, under Macros, but embedded macros are not.

    Macro Designer features

    Access database 2010 (including the later versions too) has a new Macro Designer that allows effortless creation of complex macros at the same time decreasing coding errors.

    The designer is used to build the list of actions that you want to execute when the macro runs.

    To display the Macro Designer, click Macro on the Create tab.

    When you first open the designer, Access will display a drop-down list from which you select an action.image4

The following table shows the commands that are available on the Design tab of Macro.

Group Command Description
Tools Run Performs the actions listed in the macro.
Single Step Executes the macro one at a time.  When you click the Single Step and then click Run, the Macro Single Step dialog box displays, and you will see three command buttons: Step, Stop All Macros, and Continue. Once you click Step command button, the macro executes from one macro to another. To stop all the macro actions, click Stop All Macros command button. To continue to run the macro, click Continue command button.
Convert Macros to Visual Basic Use this to convert a macro to Visual Basic code.
Collapse/Expand Expand Actions Click this when you want to display the actions of a macro.
Collapse Actions This works opposite to the Expand Actions tool.
Expand All It expands the macro actions and the collapsed block in the Macro Designer.
Collapse All It collapses the macro actions and expanded block in the Macro Designer.
Show/Hide Action Catalog Click this if you want to display the Action Catalog pane.
Show All Actions When selected, all the macro actions are displayed in the drop-down list of the Action column. If you choose a macro action from this list, you may need to grant the database explicit trust status before you can run the action.

Ben Beitler

United Kingdom

Building Your First Database with Microsoft Access

Building Your First Database with Microsoft Access 3If you possess a version of Microsoft Office that incorporates Access (Office Professional 2016 is the latest version), yet you have never used it, you are neglecting an effective tool for organizing and analysing business data.

Working with simple lists does not require you to use Access — you can do it with Excel. Microsoft Excel is a spreadsheet which offers fundamental tools for such tasks as sorting, filtering, and computing values. If you are working with complex data, Microsoft Access is the software to use. Even though you could store complex data in list form in Excel, doing so normally brings about a considerable measure of data duplication and the danger of data-entry errors. Furthermore, storing data in list form requires you to have special Excel tools like pivot tables, so you can analyse and view the data in a useful approach. That is not the situation with Access.use Pivot Tables

Here is a real-world scenario associating complex data that is a decent possibility for being stored and managed in Access rather than in Excel: For instance, your business requires to manage records as to which workers have been designated company assets such as mobile phones, PCs, tablets, or video projectors. Every worker may have any number of these assets in their ownership, and you have to store a description and an ID number for every electronic gadget a worker has (you need not restrain your data to electronic gadgets, obviously).

If you were to make this record keeping scheme as a list in Excel, you would follow either of the two methods below:

Method #1: Apportion one row in a worksheet for every worker and dedicate pairs of columns across that row to contain the description and ID for every asset the worker has been designated. If for example one individual had 15 gadgets you’d make 30 such columns. If one individual had just a single piece, then you’d require only two columns. Because the quantity of data stored for every worker would differ, searching for a specific item in the worksheet wouldn’t be simple. It likewise would not be anything but difficult to view the data if somebody had several equipment, as you would need to set up more columns that would fit well in the Excel window.

Method #2: Designate one row in the worksheet to each electronic gadget. This setup is just as lumbering although for various reasons. In this case, you would need to restate every worker’s name and worker number for every gadget in their ownership. That implies you would be monotonously entering the worker data, and you would end up with a ton of duplicate data stored in the Excel document. What’s more, the spreadsheet would be cumbersome to work with, it is quite possible that after some time you or an associate would enter some worker data mistakenly. You might end up with what looks like two different workers, for example, simply because somebody entered a record for “Bill Smith” and another person made a second record for the same individual as “Billy Smith.”accessdatabasetutorial

For such situations, Microsoft Access is an endlessly better tool than Excel. In Access, you would make one table (a list) of workers, with each record containing the worker’s name and ID number. In another table (another list), you would enter the description and ID number for every gadget, in addition to the ID number for whichever worker is in control of that thing. The only duplicate data in this arrangement is the worker ID number, which serves to connect the two records. This is what is called a relational database, and Access makes such a database simple to make.

To view Part-2 of this series, (by Ben Beitler ), Click here >>

Kind regards,

Ben Beitler – “Your Access Database Expert!