10 Ways to Use Queries In Microsoft Access

10 Ways to Use Queries In Microsoft Access

Microsoft Access queries let you questions of the data added to your database. You can view or manipulate the data based on criteria specified in the query creation. There is a query wizard to help beginners use queries. For those with lots of experience, you can utilize formulas or SQL to further define your criteria. Check our this hubpage by ercramer for 10 different ways to use Access database queries:

How to use Queries in Access

In my job as an accountant, I have learned to use Microsoft Access on a regular basis. It is a great compliment to Excel. Using Access, allows you to easily manage large chunks of data and manipulate it into a more useful format. When you are done, you can either create a report in Access or export the information directly into Excel. The tool that is used to manipulate data in Access is called a query.

#1 Creating Access Queries

Click on Access’ “Create” menu and click on the “Query Design” button. A box will pop up will come up titled, “Show Table”. This box will list all of the tables that you have set up in your database. It will also contain any queries that you have set up. You can choose to close the box or to select one or more tables or queries. Any field in a query can be limited or exclude data by using the “Criteria” line in a query. If you do not want any rows that are blank in a given field, you would enter “Is Not Null”. If you wanted only the blanks, it would be “Is Null”. You can exclude any rows in a field that has a certain word.

 

Basic Introduction to Database Queries in Access

#2 Linking Tables or Queries in a Query

If you wish to link a table or query, for example, click on “Customer Name” in table T001 and drag your mouse over to “Customer Name” in table T002. Right-click on the line between the tables, select “Join Properties”, which will open up a pop up window. You have three choices to choose from: Only include rows where the joined fields from both tables are equal, include all records from T001 and only those from T002 that are equal with T001, and include all records from T002 and only those from T001 that are equal. For our example, I would choose the third option because I want to link the customer information with my invoice detail.

# 3 Formulas in Queries

Access also allows formulas to be entered into queries. Some of the functions are the same as Excel; however, many of them are different. A great example of this is the “IF” statement in Excel, is actually “IFF” in Access. The other thing to keep in mind is that you need to bracket “[]” field names when using them in formulas. If both tables have the same exact field name, you will have to include the table name like this:

Customer: =[T001]![Customer Name]

This will make a new field in the query called “Customer” and pull the customer name from table T001.

#4 Types of Queries in Microsoft Access

There are six different types of Access database queries. I will explain how to use each type and to give an example of how each is used. For illustration purposes, I created a simple Access database with two tables in it. The first table is called T001, which is designed to hold customer information. It has the following fields: Customer Name, Customer Address, State, Zip Code, Phone Number, and Email Address. The second table, T002, lists invoice data with the follow fields: Customer Name, Invoice Number, Invoice Date, and Invoice Amount.

#5 Select Queries

Select queries are the default selection and the most commonly used type of query. They are usually built off one or more tables or queries. If you select more than one table or query, you need to have a common field, such as “Customer Name” in our example. I use them all of the time to link tables/queries, run calculations, and to limit the data that I want to return. The biggest thing to remember with select queries is that they are independent in nature. That is, you can run them repeatedly without affecting anything else in the database.

how to use access queries

#6 Make Table Queries

Make table queries are used to create tables based on what information that you have in the query. They are created the same way as select queries are. Once you have selected your tables/queries, on Access’ “Design” menu, click on the “Make Table” button. A Dialogue box will open up asking you to enter a table name. I enter “T003” and clicked “Ok”. Once this query is run, it will create a table with any fields that you have created and populate the table that they query returns. If it is run again, it will replace the contents of what is in T003. The few times that I have used this type of query, I have run it once and then changed it to an “Append” query, which we will talk about next.

#7 Append Queries

Append queries are similar to make table queries, in that, they will take whatever the query returns and populates the selected table with that information. They are different, in that, the table already needs to be established and append queries will not replace the data already in the table. When you click on the “Append” query button, it will ask you to select a table to append. Select the appropriate table and click “Ok”. A new row will show up on the bottom called “Append To:”. Select the appropriate field from the table that you are appending. Sometimes Access will auto populate this row, but it is a good idea to check to see if all of the fields are correct. I use this type of query regularly in my Access database at work. I take several tables or queries and append them to one table. A great example of this is all of my journal entry queries that pull information from the tables that contain the monthly data files and append it to a single table that become the journal entry table.

#9 Cross Tab Queries/ Update Queries

Update queries are a lot like the find and replace dialogue boxes, but they are must more powerful. When this type of query is run, it will find all of the records that match your criteria and will update them. It is important to remember that update queries cannot add or delete rows. In my personal experience, I rarely use this type of query. I would be more likely to use find and replace inside of a table.

Cross tab queries look much like a spreadsheet. This type of query is great for taking large amounts of data and summarizing it. Personally, I have never used this type of query.

#10 Delete Queries

Delete queries are commonly used with an Append query following it. They are used to delete the contents of an entire table or just certain records depending on how the query is set up. I use this query all of the time. As discussed above, I use this as part of calculating my journal entries each month. I run a delete query and then several append queries to dump the journal entry calculations in to one table.

Microsoft Access has a lot of power if you know how to use it. Queries are a big part of harnessing that power and manipulating data into a usable format.

Article Source: How To Use Queries In Access

 

How to import a spreadsheet into an Access table?

How to import a spreadsheet into access table?

If you have data that resides in Excel and you want to put it in Access, don’t fret. You can import the data with ease. Importing is done by using the External Data tab on the ribbon.
import-spreadsheet-into-access
  1. Click the External Data tab.
  2. Select Excel in the Import & Link group.
  3. Select the type of import when the Get External Data dialog opens.
  4. Select Browse to find the Excel spreadsheet.
  5. Locate the spreadsheet and click Open.
  6. Click Ok.
  7. Notice the Import Spreadsheet dialog window appear.
  8. Select the name or range that contains the data you are importing.
  9. Click  Next.
  10. Specify if your data contains column headers.
  11. Click Next.
  12. Click on the field names and make necessary adjustments (or decided if you will not import certain fields).
  13. Click Next.
  14. Decide if your data contains a primary key. If it does, select that column. If not, let Access specify a primary key.
  15. Type a name for your newly imported table.
  16. Click Finish.
  17. Review your newly imported table.

Have you seen the yellow access security warning?

Have you seen the yellow access security warning?

Even thought there are many Access database templates, you may encounter the access security warning when you download them onto your computer. The bright yellow warning has an enable button that must be clicked before the database is fully functional. It keeps certain content disabled in the database until you manually click the enable button. access security warning

This is helpful especially if you have gained access to the database from a site or source that you are unsure of. Always scan software or downloads prior to running them on your computer. A few minutes of proactive scanning can prevent a virus or other malware from loading on your precious computer!

 

2 Simple Ways To Create Access Database Queries

2 Simple Ways To Create Access Database Queries

Access database queries let you extract or pull data from the database. They let you ask questions of your data and find results using various criteria methods withcreate Access database queries your queries. When creating the query, they can be compiled simply by adding fields from a table. You can get more elaborate by adding fields from related tables. You can also add expressions and SQL refinements to get the exact answers you are seeking from your query. Below are two ways to create queries in Access 2010:

Query Wizard

The Query Wizard is located on the Create tab of your Access Database ribbon. When this button is selected, the wizard takes you through a series of steps to create an Access query. Use this wizard to select your tables and fields you want to see in your finalized query.

Query Design

Query Design is located on the Create tab near the Query Wizard button. Use this to select tables and/or queries for your query. This graphical interface lets you see the fields in the tables and manually drag them to your query.  

How to Specify a Caption in Access 2010

How to Specify a Caption in Access

Access 2010 provides a database creation and management tool to Microsoft Office Professional users. Databases are used to manage large volumes of data. When adding data to the database, users can create table and forms to assist with this process. Captions can assist with the table usability by explaining what certain fields represent. The captions appear as a hovering box above the field when the mouse is placed near the field.

caption in access

 

  1. Open Access 2010 and click the “File” tab. Click the “Recent” button in the left task pane and select of the recently used databases. The database opens.
  2. Right click on the tables in the Navigation Pane. Select “Open”. The table opens. Notice the “Table Tools” tab that appears on the ribbon.
  3. Click on the column headers on the table. Select the “Field” tab under the “Table Tools” tab. Select “Name and Caption.” A dialog box appears.
  4. Enter the caption in the Caption box. The caption will display whenever a user’s mouse gets near this field. Add a descriptive caption that will explain what type of data is displayed in this named field. Click “Ok.”
  5. Use the mouse to point near the field and notice the newly added caption.