Microsoft Office Access 2007 Forms Reports And Queries Ebook Library

Microsoft Office Access 2007 is the most popular Windows database program. A major reason for its success is its revolutionary query interface. Once data is collected in a database, analysis and updates need to be performed. Queries offer the ability to retrieve and filter data, calculate summaries (totals), and update, move and delete records in bulk. Mastering Microsoft Access queries will improve your ability to manage and understand your data and simplify application development.

The visual representation of tables and the graphical links between them makes Microsoft Access queries extremely easy to use. Fortunately, the nice user interface also allows very powerful and advanced analysis. The entire query engine is modeled on SQL systems and allows switching between the graphical query design and SQL syntax. Many Microsoft Access users and developers learned SQL from this feature.

Knowing the many features of Microsoft Access queries allows you to perform advanced analysis quickly without programming. This presentation covers the basics of queries revealing a variety of subtleties. It quickly moves to more advanced topics with hints and techniques for creating sophisticated queries. Finally, programmatic use of queries is presented.

Microsoft Office Access 2007 Forms Reports And Queries Ebook Library. Microsoft Access Products. If you need help using Microsoft Office programs. Microsoft Office Access 2007. Queries: Book IV: Forms: Book V: Reports: Book VI. Your use of the Open Library is subject to the Internet Archive's Terms.

Microsoft Access supports many types of queries. Here is a description of the major categories: • Select Queries Retrieve records or summaries (totals) across records.

Also includes cross-tabulations. • Make Table Queries Similar to Select Queries but results are placed in a new table. • Append Queries Similar to Select Queries but results are added to an existing table • Update Queries Modify data in the records. • Delete Queries Records are deleted from a table. Select queries are the most common queries and can be used for viewing and a data source for forms, reports, controls, and other queries. The other queries create or change data and are known collectively as Action queries.

The first step in creating a query is to specify the table or tables to use and the fields to display. Selecting tables is simple.

Just choose the table from the list when the query is first created or use the Add Table command from the Query menu. The selected table is placed on the upper portion of the query design window.

From there you can select the fields for the query by double-clicking on them or selecting several fields (by using Shift-Click or Ctrl-Click) and dragging them to the bottom portion of the query by example (QBE) grid. Make sure that the Show option is checked to display the field. Sorting and Reordering Fields.

In addition to retrieving fields from a table, a Select query can also display calculations (expressions). Of course, expressions cannot be updated because they do not exist in the original table. Expressions are extremely powerful and allow you to easily display complex calculations. There is an Expression Builder that simplifies the selection of fields and functions. By default, expression fields are named Expr1, Expr2, and so on; therefore, you usually want to rename them to something more understandable.

Setting Query Properties. By default this is set to No and all records are retrieved. For one-table queries, this property is ignored.

For multi-table queries, if it is set to Yes, (similar to using a DISTINCTROW in a SQL statement) only unique records in the underlying tables are retrieved. The Unique Records and Unique Values properties are linked and only one can be set to Yes (both can be No). When Unique Records is Yes, Unique Values is automatically set to No.

When both properties are set to No, all records are returned. Difference between DISTINCT vs. These options sometimes appear to provide the same results, but there are significant differences.

DISTINCT checks the results of query and eliminates duplicate rows. These queries (Unique Values = Yes) are not updateable. They are a snapshot of your data and don't reflect subsequent data modifications by users. This is similar to running a Totals Query (for example, using a Group By clause). DISTINCTROW checks all the fields in the table and then eliminates the duplicate rows.

The results of a query with DISTINCTROW (Unique Records = Yes) are updateable and reflect changes to retrieved records (but the query does not automatically run again if the data changes to retrieve different rows). So the difference is that DISTINCT only checks the fields in the results, while DISTINCTROW checks all the fields in the underlying tables. If your query joins several tables and only displays records from one, the DISTINCTROW option lets you view and edit the results. For more information, see. SQL Server Properties.

Sometimes, you need to search for a particular letter or digit. Combined with the Like command, you can use wildcards to specify such criteria. Microsoft Access uses the following wildcard characters: •? Single character • * Any number of characters • # Single digit • [.] Character list • [!] Not in character list For example, if you are interested in a text field where the second letter is 'a', the criteria would be: Like '?a*'. If you were seeking values where the second letter could be an 'a' or 'e', the criteria would be: Like '?[ae]*'.

The opposite of this (all values that do not have 'a' or 'e' as the second letter) is performed by adding an exclamation point (!) as follows: Like '?[!ae]*'. Finally, to select a range of letters (say 'a' through 'e'), add a dash between the letters: Like '?[a-e]*'. To search for a wildcard character, enclose the value in brackets. For example, to find values that end in a question mark, use this: Like '*[?]'. Often it is not possible to know in advance the criteria for a query field. In those cases, where the filter values are not known until runtime, a variable (parameter) can be used. When these queries are run, the user is prompted for the value of each parameter.

(The parameters can also be assigned programmatically). Using parameters in queries is extremely powerful and converts static 'hard-coded' queries to flexible, dynamic ones. The use of parameters can significantly reduce the number of queries you need, makes queries more useful, and simplifies database maintenance. It is easy to add parameters. Instead of typing the value of a criterion, type (between the brackets) the prompt that you want the user to see when the query is run. The value that the user types replaces the parameter in the query.

In the following example, a parameter [Enter State Name:] is the criteria in the [State] field, and [Enter Minimum Age:] is the parameter in the [Age] field. When this query is run, the user is prompted for the state desired and minimum age, and the records matching that value are retrieved. Query using a built-in VBA function This query selects the Country names in descending order of name length. The second field renames itself to [Length], uses the LEN function to calculate the length of each country name, sorts the length in descending order, and excludes any records with 10 letters or fewer.

While this might not seem particularly useful, there are many situations where using Access functions is extremely useful and eliminates the need to program. The string functions in particular ( Left$, Right$, Trim$, Mid$, UCase$, LCase$, and so on.) are useful for manipulating portions of strings and changing case. Public Function StripLead(pstrPhrase As String) As String ' Comments: Get rid of leading A, An, or The in a phrase. ' Used for card catalog sorting.

' In: pstrPhrase Phrase to examine ' Returns: The input phrase without the 'useless' first word. ' Returns the same phrase if the first word isn't an issue Dim strFirstWord As String Dim strReturn As String Dim intPos As Integer strReturn = pstrPhrase intPos = InStr(pstrPhrase, ' ') If intPos >0 Then strFirstWord = Left$(pstrPhrase, intPos - 1) Select Case strFirstWord Case 'A', 'An', 'The' strReturn = Right$(pstrPhrase, Len(pstrPhrase) - intPos) End Select End If StripLead = strReturn End Function And this is the result.

Notice how the sorting of the [Adjusted] field differs from the [Original] field. Select queries retrieve all the records that meet your criteria. There are occasions where you only want a subset; the top or bottom number of records. Similarly, you might just want the top or bottom percent of records.

Just create a regular Select query that retrieves the records you want. By changing the Top Values query property (right-click the top portion of the query), you can specify the number of records to display.

The example below (query: Other: Top 10 Auto Companies) shows only the top 10 records. Querying the top values Notice the query is retrieving records in Descending order so the Top Values option retrieves the largest values. It simply runs the query and displays the specified number of records in the query output's order. To display the lowest values, the query should retrieve records in Ascending order. Top Percent of Records Sometimes, you want a percentage of records and not a fixed number. To retrieve the top n% of the query, type a percentage (for example, 10%) instead of a value in the Top Value option.

Total Queries. Crosstab of Patients and with State name Notice the link on the [State] fields and the [Name] field from the States table in the query. To create multi-table queries, the Table row should be displayed. This can be activated from the View Table Names menu. Even better, the default query options should set Show Table Names to Yes.

There are several ways to join tables in a query. The previous example was the most common which is an exact match between fields, sometimes called an inner join. Another join includes retrieving all records from one table regardless of whether there are matches in the second table.

This is called a left join. If the logic is reversed (all records from the second table and matching records from the first) it is called a right join. These options can be selected by double-clicking on the linking line and choose among the three options. Left Join Between Tables Here is an example of a query with a left join and the results. Results of a left join query No Joins Between Tables Queries with multiple tables do not even require a line between the tables. Canon Mf4100 Series Printer Driver more.

If no lines are specified, a record by record link is assumed. That is every record in the first table is linked to every record in the second table. This is most useful when one of the tables only has one record. Finally, tables can be linked through an expression that establishes a partial match or match based on a range of values. Examples are shown later.

Basing Queries on Other Queries. So far, the queries presented are only based on tables. However, Microsoft Access lets you also base queries on other queries. This ability to filter and analyze data across several levels is extremely powerful. The queries themselves behave identically whether the data comes from tables or queries. Basing queries on other queries can also simplify maintenance of your database by letting you create standard queries that other queries can use.

This can be particularly useful in reports. Of course, you need to be careful modifying the 'core' queries. Additionally, when you generate reports in multi-user databases, make sure that you don't use queries when you should use temporary tables that are generated by Make Table and Append queries. Queries always retrieve the most current data. If you are printing many reports while others are modifying the data, and consistency between reports is important (the numbers need to tie), you must create temporary tables with the data you need prior to printing. You can then base your queries on those 'static' tables.

For a field, calculating the percent of each record to the total for an entire table is useful for determining the relative size of each group. This can be achieved by creating a summary query and using that in another query with the original table. In this example, we use the Fortune100 table containing sales and profits data for 100 large corporations; and two queries (Fortune 100 Totals and Fortune 100 PercentOfTotals). Here is how they work: Step 1: Create a Query calculating the Totals This is a simple query that sums the values in the two fields: Sales and Profits.

For clarity, the resulting fields are named TotalSales and TotalProfits. Frequency distributions reveal the number of records that contain values within numeric ranges. In this example, we want to know how many patients fall into different age categories (under 25, 25 to 40, 40 to 50, 50 to 60, and 60+). A simple two-table query calculates these results even when the size of the numeric ranges are not identical. In this example, we use two tables (Age Groups and Patients), and one query (Frequency: Age Distribution). Just follow these steps: Step 1: Create a table defining the groups and numeric ranges Create a table defining the groups and numeric ranges.

Create a table with four fields: Group ID (counter), Group Name (text), Minimum (number), and Maximum (number). For each record, define the groups and its low and high values. Define the high and low values for each group in a Group definitions table Notice how the Maximum value of one record is smaller than the Minimum value of the next record. They cannot be identical or else such values would fall into two groups.

In our example, the Age data are integers so using integers in this table is okay. Otherwise, you can use numbers very close to each other (for example, ). You can name and specify as many groups as you like. Step 2: Create multi-table Select query Create a Totals Select query with the data table and the Group definition table defined above.

Query to calculate frequency distribution Notice that the two tables boxes are not linked to each other. The first two fields in the query come from the group table: the Group ID field controlling the sort order, and the Group Name description.

The third field is the count of the Patient (data) table's ID field (this field is used because it is not null for every record). The final field defines the link between the two tables. Using the Patient table's Age field, the criterion is Between [Minimum] And [Maximum]. This 'bins' the Patient data into the age groups defined by the Group table.

Step 3: Run the Query Running the query provides the results. Earlier we showed how to use parameters in queries. If you want to run a query that has parameters from within your code, you must specify the parameters in your code. Otherwise, the user is prompted for the parameter value when the query is executed. To pass a parameter value to a query, you need to create a QueryDef and reference its parameters collection.

From there you can specify each of the query's parameters. When you are finished, you can create a recordset from it (if it is a Select query) or execute it if it is an Action query. To learn more about QueryDefs and the parameters collection, refer to the online Help section under QueryDefs. Public Sub RunActionQuery_DAO(pstrQueryName As String) ' Comments: Sample code of running a stored (action) query ' Input: pstrQueryName Name of saved query to run DoCmd.SetWarnings False CurrentDb.Execute pstrQueryName DoCmd.SetWarnings True End Sub This procedure sets up a database variable referencing the current database, and creates a QueryDef based on the query name.

The Warning message is temporarily turned off before executing the query and reset afterwards. DoEvents and DBEngine.Idle commands are used to make sure that the Jet Engine has completed its tasks and releases its locks. Creating a table with a Make Table Query SQL string. Luke Chung founded in 1986 to provide custom database solutions. He has directed the company’s product development and consulting services efforts as the database industry evolved. In addition to being a primary author and designer of many FMS commercial products, Luke has personally provided consulting services to a wide range of clients.

A recognized database expert and highly regarded authority in the Microsoft Access developer community, Luke was featured by Microsoft as an Access Hero during the Access 10-year anniversary celebration. Luke is a popular speaker at conferences in the US and Europe, and has published many articles in industry magazines. He is a past president of the Washington, DC chapter of the Entrepreneurs Organization (EO Network), serves on the Fairfax County School Superintendent's Community Advisory Council, and is a graduate of Harvard University with Bachelor and Master Degrees in Engineering and Applied Sciences.

If you want to extend the power of Access queries with more advanced numerical analysis, learn more about the Total Access Statistics program from FMS. It is the leading. It analyzes your Access table, linked table, or query in an MDB, ACCDB or ADP, and puts its results in tables. Generate percentiles, frequency distributions, regressions, correlations, rankings, data normalization, advanced crosstabs, t-Tests, ANOVA, non-parametrics, probabilities, and so on. Interactive Wizard and VBA programmatic interfaces are included with a runtime distribution library.

Adding advanced numerical analysis couldn't be easier! Get including a free trial version.

Recordset Builder in Total Visual CodeTools. If you want to simplify the creation and maintenance of Access/VBA code, learn about the Total Visual CodeTools program from FMS. It helps, clean up existing code, and deliver more robust solutions. Two of its builders related to queries are: • The lets you point to a database, select a table or query/view, select all or some of the fields, and automatically generate code to browse, edit, or add records to it. Choose whether you want it to use ADO or DAO, and whether you reference the current database or an external one. • The converts SQL from a query into a quoted string that you can add to your VBA Modules, while handling quotes and smart line continuations so parts like FROM, GROUP BY, WHERE, and so on start new lines.

Get including a free 30 day trial version. Database Maintenance and Macro Scheduler. As you add more data to your database, make sure that you compact it regularly for optimal results and that you make backups. Additionally, check for any tasks that need to run repeatedly, such as a particular set of queries, data downloads, exports, or batch of reports that you must print. You can automate these with a macro or some code.

To launch this on a regular schedule, use Total Visual Agent from FMS. Total Visual Agent is a on an hourly, daily, weekly, monthly, or one time event.

Easily manage an unlimited number of databases across your network. Get including a free trial version. Other Technical Papers and Tips.

Books.google.com.tr - “Everything you need to master Access 2007 forms, reports, and queries.” –Charles Carr, Reviews Editor, ComputorEdge Magazine Create Forms for Business Ensure Data Entry Accuracy Build Elegant Form Interfaces Collect Data Via Email Design Effective Business Reports Make an Invoice Report Create. Microsoft Office Access 2007 Forms, Reports, and Queries. “Everything you need to master Access 2007 forms, reports, and queries.” –Charles Carr, Reviews Editor, ComputorEdge Magazine Create Forms for Business Ensure Data Entry Accuracy Build Elegant Form Interfaces Collect Data Via Email Design Effective Business Reports Make an Invoice Report Create Mailing Labels Extract Data Work with Multiple Tables Calculate Discounts Analyze Data Develop your Microsoft Access expertise instantly with proven techniques Let’s face it: Microsoft Access is a large, intimidating program. Most people never progress beyond creating simple tables and using wizards to build basic forms and reports.

At the same time, you need information and you know that what you seek is embedded somewhere in your Access database. Without a more sophisticated knowledge of how to extract and present that data, you’re forced to rely on office gurus and overworked IT people to provide canned reports or one-size-fits-all solutions. This book changes all that by giving you the skills to build efficient front-ends for data (forms), publish the results in an attractive and easy-to-read format (reports), and extract the data you need (queries).

This book shuns the big Access picture and instead focuses intently on forms, reports, and queries. This in-depth approach will give you the knowledge and understanding you need to get at the data and prove the old saw that knowledge is power. Focuses on the three technologies that you must master to get the most out of Access: forms, reports, and queries.

Avoids database theory in favor of practical know-how that you can put to use right away. Packed full of real-world examples and techniques to help you learn and understand the importance of each section. Covers what’s new and changed in Microsoft Access 2007. Paul McFedries is the president of Logophilia Limited, a technical writing company.

Now primarily a writer, Paul is well known as a teacher of Microsoft Office and Microsoft Windows, and has worked as a programmer, consultant, database developer, and website developer. He has written more than 50 books that have sold more than three million copies worldwide. These books include Tricks of the Microsoft Office 2007 Gurus (Que, 2007), Formulas and Functions with Microsoft Excel 2007 (Que, 2007), VBA for the 2007 Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).