ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Developing a database application -- queries

Updated on April 25, 2013

Queries are the heart of a DBA’s work. Just collecting data is worthless if you cannot analyze the information. Relational databases are better for analysis because they function faster and use less memory. Most relational databases are SQL-based, including Oracle, SQL Server and MS Access. For this reason they can interchange information easily. Microsoft Access enables you to write coded queries in MS SQL or use its Query object, which is sufficient for the majority of work you will do.

Queries enable you to filter data on just about any criteria you want, check database integrity, clean up lists and generate reports of all kinds.

There are several types of queries, including:

  1. the select query – this is the one you are most familiar with; you select particular data to be displayed, from one or more tables; this can include created fields and/or calculations
  2. the summation query – this will group, add, count, and several other functions to one or more linked tables
  3. the append query – adds new records onto an existing table
  4. make-table query – creates a new table
  5. update query – updates existing information on an existing table
  6. delete query – deletes records on an existing table that meet specified criteria (be careful with this one)
  7. parameter query – enter a message into the criteria section of a field wrapped in square brackets. This pops up a message box prompting the user for search data

There is rarely justification for a one-table query – you can select information to be displayed in a report directly. So even to make select queries, you will want to relate two or more tables. Perhaps you have created a base table and you want the coded field to display as the verbal value. Perhaps you are creating an invoice and you want to bring in the customer’s name and address. These are the minimal queries.

As you may have noticed, I don’t tend to develop in Access according to the normal setups. Therefore I do not create relationships until they are needed; I do not assign ID numbers to every table (often don’t use a key at all) and I create queries before forms. Granted, you may want to create data-entry forms for the tables’ initial population. And if you created a data type of Lookup on a table, the form automatically gives you a drop-down list for those fields. But the beauty of forms goes further than this – it allows us to make our queries user-friendly too.

Query fields can be set up to ask for information automatically. The problem with this is that the user needs to know what information is acceptable to begin with – a daunting task once the database gets going. So I create a Parameters table of a single record. This allows the user to enter a date, date brackets, or any other field definition. I then set up a query using that field as a parameter by linking the optional field to the data table

Update queries are great when you create a new field in a table. You would need a known relationship (say, device serial number to new asset number) for the update. This is also a good way to change inventory data – decrement the number of an item that are in stock, or increment by the amount received from the supplier.

Append queries are a great way to archive information – move outdated information into an ‘archive’ table based on some flag. You would then use a deletion query to remove those same flagged records from the active data. You may also want to save selections like recipes to a ‘favorites’ table.

Make-table queries are usually one-time queries, unless you are setting up a complex amount of data for an analysis. Access will only handle queries 3-deep. So, for instance, when I need to do a quarterly report for a client, I collect all the relevant raw data into a make-table query first, then analyze, decode it and create the reports.

The summation query is very handy for counting occurrences under different criteria, and sorting it. I usually set up the fields I need first, then click the summation button (looks like a capital E, or an epsilon). You can then select what you want to do with each field – Group By means just that – it is a sort, but if you are counting occurrences, it will not repeat this field but rather increment the count each time the value is encountered. You can use the Where parameter, which allows you to set up a condition to be met. The Expression lets you write a formula to be calculated. The Count lets you count the number of instances. For instance, if you wanted to know how many cities are in each county of your state, you would group the Cities table by counties, then count the City field. This is a very versatile query format, as you will see.

To create a query, select the query section and click New. There are some automatic queries available for you and I use two of them regularly for checking data integrity – Find Duplicates (check a table for duplicate values in a particular field) and Find Unmatched (compare two tables and list those items which are not in field A of table A but are in field B of table B). The query wizard is a waste of time – it only makes simple select queries. The best bet – select Design View. This will give you a grid for creating any type of query. Select the tables you want to use (you can also select existing queries). Define the relationships between the tables if there are any, such as connecting the serial_number of one table to SN of another. Anything being displayed will have the same value in both fields. You can create multiple relationships like this but then all values must be equal. Drag the fields you want displayed down to the grid (or double-click on them). Set up any criteria you may need, such as Is Null (empty field) or <1/1/2004 (all dates before 1/1/04). Check the datasheet view to see if you are successful. Of course, you need to populate the tables with at least 10 records (I use 25) of real or test data to be sure the queries work. Manually figure what your test data should display, then compare that to the results of the query.

If you are using a primary key from one table to find a field in another table, you need to 'link' these, or as it is called by MS, create a relationship. For instance, you have an invoice table and a customer table; you would drag the CustID from the Invoice table in the upper part of the grid to the CustID field in the Customer table. Now double-click all fields you need for the query and they will appear on the first line of the grid. For instance, if you chose InvoiceID (Invoice table) and LastName (Customer table), you will display all invoices with a corresponding CustID. Note, if the Cust ID is not found in the Customer table, the invoice will not display.

Next check the Show box on any field you want to display in the query datasheet. Times when you won't want to show a field -- when the field is used in a calculation such as a total. Third line on the grid is where you put any constraints on the information, such as all invoices after a particular date, or only Last names that begin with S, or a specific sale item. Remember that this line reads as "AND", so a record will only show if it meets ALL constraints defined on this row. The next lines on the grid are "OR" constraints; for instance you might put "Mystic" on the third line Customer City field, "Norwich" on the same field on the fourth line, and "Greenville" on the fifth line. This query would show only those invoices for customers who live in either Mystic, Norwich or Greenville.

If you've never created queries before, I recommend trying it out in steps. Start with one table and display maybe 4 fields. Then set up a single criterion in one field and see how many records are shown, then add and link another table and see how may records are shown. And so on.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    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)