ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to use Queries in Access

Updated on July 29, 2012
Types of Queries
Types of Queries | Source

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.

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

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.

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.

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.

Select Query with the Formula Builder box open
Select Query with the Formula Builder box open | Source

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.

Make Table Query Screenshot
Make Table Query Screenshot | Source

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.

Append Query Screenshot
Append Query Screenshot | Source

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.

Update Query Screenshot
Update Query Screenshot | Source

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 Screenshot
Cross Tab Screenshot | Source

Cross Tab Queries

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.

Delete Query
Delete Query | Source

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 useable format.

How many different types of queries have you tried before?

See results

Comments

    0 of 8192 characters used
    Post Comment

    • ercramer36 profile imageAUTHOR

      Eric Cramer 

      6 years ago from Chicagoland

      lol Access can be fun, but also very frusterating when you are close to figuring something out and just can't quite get there. I was lucky and my previous boss taught me the basics and then I was able to play around and learn more.

    • watergeek profile image

      watergeek 

      6 years ago from Pasadena CA

      I started to design a database in Access once. It was confusing, but fun. Over time i began to get things clear and especially loved getting creative with the looks of my forms. But now . . . just the thought of it confuses me again, I don't know why.

    • Riverfish24 profile image

      Riverfish24 

      6 years ago from United States

      Gee, I don't know MS Access at all..shame! This hub is for keeps! Nice work and useful topic!

    • ercramer36 profile imageAUTHOR

      Eric Cramer 

      6 years ago from Chicagoland

      I purchased Access as part of Office 2007. You can buy it individually. You should be able to download it after paying for it.

    • profile image

      LCJames 

      6 years ago

      Thanks for this information. How do you get Microsoft Access on your computer?

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)