LEARN MS-ACCESS TIPS AND TRICKS

Easy-Read Reports

a.p.r. pillaiMarch 24, 2011Reading, Report, Reports No comments

Easy-Read Reports.

When computer reports, accounting ledger statements, or purchase invoices are printed with closely spaced lines, readability improves if alternate lines are shaded with a light background color. Traditionally, pre-printed stationery was used for this purpose. Line printers typically print six lines of data within a one-inch vertical space. To emphasize headlines, the double-strike each character for enhanced printing style, the only advanced feature available in line printers.

Line-printer-based reports were designed on graph-paper-like sheets, with carefully written code to position headings, data lines, and summaries precisely on the pre-printed stationery.

An A4 sheet (8.5 × 11 inches) provides 66 print lines. Of these, one inch at the top and half an inch at the bottom are reserved as margins, while the alternate data lines are shaded in green—see the sample image below.

Right-click to open Large Image in New Tab/Window.

A4-size paper has 80 print positions across when a 10-character-per-inch (pitch) font size is used. This can be increased to 96 characters if a 12-character-per-inch character size is used.

The above details provide a general background about computer stationery. Reports designed for line printers were created with these specifications in mind. When necessary, you can design reports for plain paper by setting the section height and the text box control height to one-sixth an inch, matching the line spacing used by traditional line printers.

Light Shading of Alternate Report Data Lines

We will use a simple trick to print alternate lines with a shaded background, making the report easier to read. There is no need for pre-printed stationery, as the shading will be applied dynamically during printing.

You can design a Quick Report and add a few lines of VBA code in the report’s class module. If you already have a report with closely spaced detail lines, you may skip directly to step 3 below.

Import Products Table from MS-Access sample database Northwind.mdb
Use the Report Wizard to design a Tabular Report with the Products Table, like the sample image given below:

Right-click to open Large Image in New Tab/Window.
Open the Report in Design View.
Select all the controls on the Detail Section of the Report and drag them to the right to get enough space to draw a TextBox on the left side to display Serial Numbers on the Report Lines. Drag the heading lines and position them to the right.
Insert a TextBox on the left side and write the '=1' expression in the Control Source Property. Change the Name Property Value to SRL.
Change the Running Sum Property Value to Over All.
Create a Label control in the Page Header above the TextBox and write the Caption to SRL (for Serial Number).
Select all the Controls in the Detail Section and display the Property Sheet (F4 or Alt+Enter).
Change the Top Property Value to 0. All the controls are shifted to the top edge of the Detail Section.
Select the Rectangle Tool from the Toolbox and draw a rectangle around all the text boxes in the Detail Section (see the design view image above).
Display the Rectangle (F4) Property Sheet and change the Name Property Value to Box1.
Select the Send-to-Back option from the Arrange Menu to position the rectangle behind the text box controls.
Reduce the Detail Section height so that there is no empty space below the TextBox controls.
Display the VBA Code Module of the Report (ALT+F11).

Copy and paste the following lines of VBA Code into the Code Module and save the Report:

The Report Module VBA Code.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim xSrl
xSrl = [SRL]
If xSrl / 2 = Int(xSrl / 2) Then
   [Box1].BackColor = &HCCC8C2
Else
   [Box1].BackColor = &HFFFFFF
End If
End Sub

Open the report in Print Preview. You will find the result as shown in the report image at the top of this page.

Top N Records in Query

a.p.r. pillaiMarch 11, 2011Queries, Query, Records, Top Values 1 comment

Top N Records in Query.

We have seen the usage of different types of complicated Queries like the following:

Today, we will learn how to define and extract the top 100 records, or a certain percentage of the total records, based on the values in a particular Column.

Review of Rules of Queries.

You need to know only a few rules to work with this type of Query.

You can select several Columns of data from the source for output.
You must sort one or more columns of data in ascending or descending Order, and the leftmost sorted column will pick the top valuation records.
If the output contains duplicate records (i.e., two or more records with identical values in all columns), you can set the Unique Values property to Yes, which is equivalent to using a DISTINCT clause in the SQL SELECT statement, to suppress the duplicates.
If the query has more than one Table/Query as the source, and duplicate records are found in the output, set the Unique Records Property to Yes (DISTINCTROW clause in the SELECT statement) to suppress duplicates.

Create a Sample Query.

Create a new database or one of your existing databases.
Import the Order Details and Products Table from the Northwind.mdb sample Database. There is a Combobox in the Order Details Table that retrieves the Product Name from the Products Table. The Product Code in the Order Details Table is linked to the Product Code in the Products Table. Open a new Query in SQL View (without selecting a Table/Query from the displayed list).

Copy and paste the following SQL String into the SQL window, and save the Query as Order_DetailsQ.

SELECT TOP 100 [Order Details].ProductID, [Order Details].UnitPrice
FROM [Order Details]
WHERE ((([Order Details].OrderID) Between 10248 And 10300))
ORDER BY [Order Details].UnitPrice DESC;

Open the Query in Design View and check the order of field placement and the Sort Field.
Right-click an empty area above the column grid to display the Query Shortcut Menu, select the Properties… option to display the Property Sheet. Check the image below:

Right-click to open Large Image in New Tab/Window.
Ensure that the Top Values Property is set to 100 to filter the Query to 100 records with the highest Unit Price values.
Change the Query View into Datasheet View to display the output records. See the image given below:

Right-click to open Large Image in New Tab/Window.
The Order Details table contains multiple records of the same product under different OrderIDs. In this example, we have intentionally excluded the OrderID field from the data columns, using it only in the criteria to select records with OrderIDs between 10248 and 102300, which also results in some duplicate records. As shown in the image above, several duplicate product records appear in the output. This scenario provides an opportunity to experiment with the Unique Values property settings to remove duplicates.
Eliminating Duplicate Records.
Change the Top Values property to All, and the Query to Datasheet View. The output will be about 150 records for OrderID Range between 10248 and 102300.
Change the Query in Design View and display its Property Sheet.
Set the Top Values property to 100 and the Unique Values property to Yes.
Change the Query in the Datasheet View and inspect the output.
Now the duplicate records are suppressed (29 records removed), leaving only 71 records in the output. The next property, Unique Records, can be set to Yes to achieve the same result when fields from two or more tables or queries are joined in a query design. This is useful when the output contains duplicate records due to a one-to-many relationship between the tables.
We have specified 100 records in the Top Values Property, but the Unique Values property setting reduced the number of records to 71 after suppressing duplicates.
Change the Top Values Property setting from 100 to 25% and change the View to Datasheet.

Using the Percentage setting, the output returns only one-fourth of the total records. With Unique Values set to Yes, this yields 18 records, out of a total of 71. With Unique Values set to No, it returns 39 records, which is approximately one-fourth of the total 150 records.

The Top Values Property sets can be a specific number or a percentage of Total Records.

Attachment Field in Access2007

a.p.r. pillaiFebruary 28, 2011Attachment, External Links, Field Type 1 comment

Attachment Field in Access 2007.

Working with images or animations in applications like Microsoft Access has always been enjoyable. I have used the Office Assistant for message boxes in all my Access applications, particularly when creating and deploying common VBA library programs across the network.

Not everyone may know that it is possible to display custom images in the Office Assistant control. I have leveraged this feature to display custom greetings to Access users on special occasions such as Christmas, Eid, and Onam simply by replacing the standard company logo image on the server. I was extremely disappointed when Microsoft removed this feature in Office 2007.

For those still using Microsoft Access 2003 or earlier, the following links provide tips and tricks for using the Office Assistant with MsgBox:

The Attachment field type in Access 2007 has significant flexibility, allowing multiple external documents or images to be stored in a single record without inflating the database size. This overcomes the limitations of the older Object Linking and Embedding (OLE) method, which was commonly used in earlier MS Access Versions for storing, editing, or displaying images. While a Hyperlink field can link to only one external file or a single internal object (such as a form or report), the Attachment field supports multiple items per record.

This feature is useful for storing and retrieving essential documents related to a record, such as project site plans, diagrams, contract agreements, engineering drawings, or employees’ family photos. Each attached document or image can be edited in its native application, preserving full functionality.

A Sample Demo.

Open Microsoft Access 2007.
If you have already created the Northwind 2007 sample database, open it; otherwise, select Local Templates from the Template Categories.
Click on the Northwind 2007 Template to select it.
Click the Folder icon on the right side of the File Name control, select the required folder, and save Northwind 2007.accdb Database.
Open Northwind 2007.accdb Database,
Close the Home Form.
Select Object Type from the drop-down list in the Navigation Pane and select Tables.
Right-click on the Employees table and select Design View from the Shortcut Menu.
Use the right scroll bar to move the field list up and bring the last field, Attachments (field type: Attachment), into view.
Now that we have seen the Attachment Field in the Employees Table (or you can create a new Table with the Attachment Field if you prefer), close the Design View.
Open the Employees Table in Datasheet View.
Move the horizontal scroll bar and bring the attachment field into view. See the sample image shown below:

Right-click to open Large Image in New Tab/Window.
The second column (highlighted) is the attachment field where a paper clip image and a number in brackets (zero) display how many attachments are in each record.
There are no attachments in any of those records so far, so we will add one; double-click the attachment field in the first record.
The Attachment control opens up. Click on the Add… Command Button to browse for files on the hard disk. You may select a Word Document, Excel File, PDF file, or Image.
Repeat this action to attach more files to the same field.
Click OK to close the dialog box. You will now see a number appearing in brackets, indicating how many attachments are in that field of the record.
Double-click the attachment field to open and show the attached files.
Click on one of the files to select it.
If you click on the Remove Command Button, you can remove the selected attachment, or click Open to open the document in its parent/preview Application.
If you right-click the attachment field, the Manage Attachment shortcut menu is displayed. Selecting this option will open the earlier dialog box we have seen for attaching /removing/opening external files.

Technorati Tags: Microsoft

PrimaryKey usage with Many Fields

a.p.r. pillaiFebruary 20, 2011Fields, Primary-Key, Process Controls No comments

PrimaryKey usage with Many Fields.

When designing a master table in Microsoft Access, ensure that unique values are in a column to make data retrieval easier. For example, in the Employees table of the Northwind.mdb sample database, each employee is identified by a unique employee code. But who ensures that the uniqueness of the values entered into the employee-code field?

It is possible to validate user input using VBA before saving the record. Database systems already have this functionality of a primary key index. The primary key prevents duplicate values and organizes the data automatically in ascending or descending order.

Usage of Indexes in Programs.

We use these field values in programs to retrieve the specific information very fast. Let us write a small VBA Routine to see how this is used in programs.

Public Function PrimaryKey_Example1(ByVal EmpCode As Integer)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "PrimaryKey" 'activate the Index on Employee Code

rst.Seek "=", EmpCode 'we are looking for the record of Employee Code provided
'Let us test whether the search for Employee Code was successfull or not
If Not rst.NoMatch Then
    MsgBox "EC: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "EC: " & EmpCode & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function

The statement rst.Index = "PrimaryKey" activates the index named PrimaryKey. A primary key index can include more than one field, and a table may contain several indexes within the Indexes collection, each defined with different field combinations. If a single field is not sufficient to ensure uniqueness, additional related fields can be included in the index. You can activate the appropriate index based on how you want the data organized before continuing with the processing steps.

The Primary Key Field contents don't always need to be numbers only; they can be any value such as FirstName or LastName fields, or both, or any combination of field types: text, date, number, etc., except the Memo field. You can give any suitable name to the Index in the Index Name column and use it to activate a specific Primary Index.

Right-click to open Large Image in New Tab/Window.

The Recordset's Seek() method is used for search operations on the Table. One of the Indexes should be active before the Seek() operation can be executed.

In the above example, the rst.Seek "=", the Empcode statement checks for the Employee Code passed to the Index_Example1() function. The statement looks for an exact match ("=" ) very quickly.

When the Index is defined with more than one field, then search keys must be separated with commas in the Seek() method. Let us modify the above program to use multiple values for the Index Keys in the Seek method, assuming that FirstName and LastName fields are the members of MyIndex.

Index having more than one Field Value.

Public Function PrimaryKey_Example2(ByVal strFirstName As String, ByVal strLastName As String)
Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "MyIndex" 'activate the Index

'Search for the Employee record
rst.Seek "=", strFirstName, strLastName

'Let us test whether the search for the Employee was successfull or not
If Not rst.NoMatch Then
    MsgBox "Employee: " & rst![ID] & " - " & rst![First Name] & " " & rst![last name]
Else
    MsgBox "Employee: " & strFirstName & " " & strLastName & " Not found!"
End If

Set rst = Nothing
Set db = Nothing

End Function

Percentage on Report Summary

a.p.r. pillaiFebruary 13, 2011Calculation, Report, Reports, Summary No comments

Percentage on Report Summary.

Earlier, we solved this problem using a Query in the blog post Percentage on Total Query. This time, let’s see how to achieve the same result on a Report. Our task is to display each detail line’s value as a percentage of the report’s summary total.

The approach is simple:

Create a Report that lists the detailed values and includes a report-level summary (such as the total).
Insert a Text Box in the Detail section.
In the Text Box’s Control Source, write an expression that divides the detail value by the Report Summary total.

This way, each record line will show its proportional contribution to the overall total directly on the Report.

Design a Sample Report to Try.

Import the Order Detail Table from Microsoft Access Sample Database: C:\Program Files\Microsoft Office\Office11\Sample\Northwind.mdb
Open a new Query in SQL View, without selecting a Table from the displayed list.

Copy and paste the following SQL String into the SQL editing window of the new Query:

SELECT [Order Details].[Order ID], Sum([Order Details].Quantity) AS TQuantity, Sum([Order Details].[Unit Price]) AS UnitPrice, Sum([Unit Price]*[Quantity]) AS TotalPrice
FROM [Order Details]
GROUP BY [Order Details].[Order ID];

Save the Query named OrderSummary.
Design a Report with the Detail Section and Report Footer summary controls using the OrderSummary as the Source.
Right-click to open Large Image in New Tab/Window.
Click the TextBox at the Report Footer to select it.
Display the Property Sheet (F4 or ALT+Enter) of the Text Box.
Change the Name Property Value to GTPrice (stands for Grand Total Price).
Write the expression =Sum([TotalPrice]) in the Control Source Property.
Select the Text Box at the right end of the Detail Section and display its Property Sheet.
Write the expression =[TotalPrice]/[GTPrice] in the Control Source Property.
In the Format Property, select the Percent format from the drop-down list.
Type 2 in the Decimal Places Property.
Save and Close the Report.
Open the OrderSummary in Print Preview and check the detail line percentage value calculated on the Report Footer Grand Total Price.

Creating Page-Level Totals.

Want to know how to calculate and display Page-wise control totals? You can learn it from here.

Embedded Macros in Access2007

a.p.r. pillaiJanuary 30, 2011Microsoft, Reports No comments

Embedded Macros in Access2007.

The only difference between stand-alone macros and embedded macros is that embedded macros do not appear in the Navigation Pane under the Macros group. Instead, they are written directly within a Form, Report, or a control’s event property. When you copy a Form or Report, its embedded macros go with it.

Let’s now explore how embedded macros are created.

Creating Embedded Macros.

Open Microsoft Access 2007.
Open a Report in Design View.
Press F4 or Alt+Enter to display the Property Sheet of the Report.
Click on the Event Tab of the Property Sheet.
Click on the On Load Event Property to select it.
Click on the Build (...) button on the right side of the property.
Right-click to open Large Image in New Tab/Window.
The Macro Builder option is already in selected state. Click the OK Command Button to accept it.
The Macro is open in Design View. The title bar of the macro indicates Catalog:Report: On Load (the Report Name: Object Type: Event Type), where the macro will be embedded.
Select MsgBox Action and type my Embedded Macro in the Message Action Argument.
Type the On Load Info in the Title Argument of MsgBox Action.
Click on the Close Toolbar button to save and close the Macro.
Save and Close the Report.
Right-click the Report in the navigation pane and select Open to open the Report in Report View. The embedded macro will run, and you will see the message.
You can modify the macro by following steps 2 to 6. You can add several actions in the Macro if needed.

Report Design in Access2007

a.p.r. pillaiJanuary 21, 2011Microsoft, Reports 1 comment

Report Design in Access2007.

In Microsoft Access 2007, two new design-time features are available: Report View (different from Print Preview) and Layout View, in addition to the traditional Design View and Print Preview. These new views make working with reports more interactive. In earlier versions of Access, only Design View and Print Preview were available.

Report View looks similar to Print Preview but offers additional functionality. You can search within the report, copy data to the clipboard, filter records, and even view summarized values of the filtered data. If you’d like to preserve the filtered records so that they appear each time the report is opened, simply set the Filter On Load property to Yes.

Right-click to open Large Image in New Tab/Window.

Layout View is somewhat similar to Design View, but with one important difference—you can make design adjustments while viewing the actual report contents. In this view, you can rearrange fields, add or remove fields, adjust field sizes, and modify data field properties, with the changes immediately reflected in the report output.

Right-click to open Large Image in New Tab/Window.

The Report View Feature.

Let us try out the above features; Open Microsoft Access 2007.
Open one of your Databases.
Import the Order Details Table from the Northwind.mdb sample database.
Click on the Table to select it.
Click the Report Option from the Create Menu to create a Report with the basic design and save it named Order Details.
Right-click the Order Details Report in the navigation pane, select Open to open the report in Report View.

Now, let us try search, filter, and copy operations in the Report View mode.

Click on the OrderID field in the first record on the Report to select it.
Click on the Find Toolbar button (the field-glass icon) under the Home menu.
Type 10251 in the Find What control and click the Find Next Command Button. The first record with OrderID number 10251 is highlighted. You can repeat the search operation by clicking on the Find Next Command Button.
Let us incorporate the filter action; click the Cancel Button to cancel the Find operation and close the dialog box.
Click on the Filter Toolbar Button. The following filter control will be displayed over the report.
Right-click to open Large Image in New Tab/Window.
Click the Select All option to remove all the check marks.
Put check marks on OrderID Numbers 10251 and 10255.
Click the OK Command Button to filter records with the selected OrderIDs on the Report.
You can click the Toggle Filter Toolbar button to display all the records or filter the records again.

To copy selected records from the Report to the Clipboard:

Click and hold the left mouse button at the left border of the topmost record in the report, and drag over a few records to highlight and select them.
Click the Copy toolbar button in the Home Menu to copy the highlighted records onto the Clipboard. These copied Report records can be pasted into Excel, Word, etc.

If you want to see the same set of filtered records every time you open the Report, then you must change the Report Property Value.

Select the Design View option from the View Menu.
Select the Property Sheet option to display the Report Property Sheet.
Click on the Data Tab of the Property Sheet.
Set the Filter on the Load property value to Yes.
You can add or remove OrderID numbers in the IN clause of the Filter condition in the Filter property value if needed.
Save the changes and close the Report.
Open the Report in Print Preview and check whether the filter action is in effect.
Close the Report.

The Layout View Feature.

Now, it is time to try out the Layout View options.

Right-click on the Order Details Report in the navigation pane.
Select Layout View from the displayed shortcut menu.
Click and hold on the OrderID heading, and drag and drop it after the Discount Column. You can do this with the column header, and also on any row of records.
Click any row in the Discount column to select the column.
Press the Delete key to remove the column from the Report.

Let us try to bring that column back into the Report from the Source Table Field List.
Click on the Add Existing Fields Toolbar Button from the Format Menu.

Right-click to open Large Image in New Tab/Window.
Drag the Discount Field and drop it between the Unit Price and OrderID fields.
While the Discount Field is still in selected state, click on the Arrange Menu to display its Toolbar.
Click on the Property Sheet Tool to display the Property Sheet of the Discount Field.
Click on the Format Tab of the Property Sheet.
Change the Decimal Places property value to 2 and change the Width Property value from 1 inch to 0.75 inches.

The interactivity feature is very powerful in the designing process of Microsoft Access Reports and makes the design task easier and more interesting, too.

Technorati Tags: Microsoft

Memo Field Text Formatting

a.p.r. pillaiJanuary 13, 2011Art Work, Formatting, Memo, Rich Text 2 comments

Memo Field Text Formatting.

We know that Memo fields in Microsoft Access tables provide the flexibility to store variable-length text data. However, in Access versions before 2007, these fields only supported plain text—much like working in Notepad.

For a long time, users felt the need for richer formatting options, such as creating numbered or bulleted lists, or highlighting key points to emphasize important information.

With the release of Microsoft Access 2007, this long-awaited feature finally arrived.

A Sample Quick Run.

Let us quickly find out how it works.

Open Microsoft Access 2007
If you have an earlier version of the Northwind.mdb sample database, open it. Otherwise, select Sample from the displayed list of templates and choose the Northwind 2007 template.
- Click the Folder Icon on the right side to select the folder to save the 'Northwind 2007.accdb' file and click on the Create Command Button to create and open the file.
- Click on the Login Command Button
  Right-click to open Large Image in New Tab/Window.
- Close the Home Form.
Open the Employees Table in Design View.
Click on the Notes Field to select it.
Click on the Text Format Property on the General Tab of the Field Properties Sheet. The current value of the Property is Plain Text.

Right-click to open Large Image in New Tab/Window.
Select Rich Text from the drop-down list to replace Plain Text.
You will see a warning message below; click Yes to apply the change.

Right-click to open Large Image in New Tab/Window.
Close the Design View and save the changes.
Open the Employees Table in Datasheet View.
Expand the Row Height of the records by dragging the intersection of the two records at the left border.
Bring the Notes Field values into view by moving the Scrollbar to the right and increasing the column width; see the image below.

Right-click to open Large Image in New Tab/Window.
You can now format the Memo Field Text as you would apply formatting in MS Word.
In the example image above, I have created a Numbered List, Bullet Text, applied Bold, Italics, highlighting, and alternate rows fill/backcolor.

Sub-Query in Query Column Expressions

a.p.r. pillaiJanuary 02, 2011Expression, Queries, Query 1 comment

Sub-Query in Query Column Expressions.

Queries are the primary data processing tools in database systems. They work behind the scenes, shaping raw data into meaningful outputs such as reports and summaries. Many Microsoft Access users, especially beginners, try to build a report’s output data by chaining multiple tables/queries together in a single query, expecting the final result in just one or two steps. This approach often leads to difficulties in producing the correct output.

A better method is to start by planning the report—define its layout, required contents, grouping, and summarizing needs. If multiple related tables are involved, break down the process into smaller, manageable steps. Begin by joining a few tables or queries to create a new query, then use that query as input for the next step. You can also create intermediate tables and build further queries on top of them to refine the data. In this process, make use of action queries such as Make-Table, Append, or Update to prepare and organize the data effectively.

When the Report Requirement is Complicated.

When a report’s contents are complex and cannot be built in a single step, my preferred approach is to create a Report Table. Data is brought into this table piece by piece from the source tables using queries or VBA routines, and then added or updated as needed before opening the report. Once the Report Table is populated, the report design becomes straightforward, since it is bound to a single, well-structured dataset.

These preparation steps can be fully automated with Macros or VBA.

To make the process flexible, necessary report parameters—such as date ranges or filter criteria—are stored in a Parameter Table. A Parameter Form is provided to capture or update these values. From this form, users can either:

Re-run the procedure to refresh the Report Table with new parameter values, or
directly open the report in Preview/Print mode if the data has already been prepared.

Using a Sub-Query in the Criteria Row.

Here, we will explore how to use Subqueries within Queries to filter records or incorporate data from other tables or queries.

Let us look at a simple Query that uses a sub-query in the criteria section to filter data from the Orders Table. In the Orders table, there are about 830 Orders, and the OrderIDs range from 10248 to 11077. We need to filter certain Groups of Orders (say Order Numbers 10248, 10254, 10260, 10263, 10267, 10272, 10283) for review.

The following is the sample SQL query that filters the above Orders without the use of a Sub-Query:

SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderID) In (10248,10254,10260,10263,10267,10272,10283)));

The above query works, but it has a limitation that whenever we want to filter a different set of Orders, we must manually modify the Criteria line by replacing the existing Order Numbers with the new ones. Clearly, we cannot expect end users to perform this task themselves.

A better approach is to provide users with a simple option to enter the required Order Numbers into a dedicated table (let’s call it ParamTable, with a single field: OrderNumber). This way, the query can automatically detect any changes to the table values at runtime. Users can type the desired Order Numbers directly into a Datasheet Form, and then click a Command Button to run the query with the updated values.

To achieve this, we need to use a subquery in the criteria row of the main query. The subquery compares the OrderNumber values in the ParamTable with those in the Orders table, ensuring that only matching records are returned.

We will modify the Query in the Criteria Row to insert a subquery to get the values from the ParamTable and use the OrderNumber field values as Criteria.

The modified SQL String of the Query is given below:

SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderID) In (SELECT OrderNumber FROM OrderParam)));

The Sub-Query string in the Criteria Row is in Bold in the SQL above.

The Sub_Query in a Query Column.

You may have already encountered the type of subquery shown above, but now we will explore a different approach: employing a subquery as an expression in a query column. This allows us to bring in values from another table related to the Query source table. Although this technique is not commonly used, it can be extremely valuable in complex scenarios where conventional joins or query structures fall short.

When several tables are used in a Query with LEFT JOIN or RIGHT JOIN relationships, it becomes difficult to link all the related tables this way to incorporate summary values of one table. This is more so when one-to-many relationships are involved.

We will use the Orders and Order Details Tables from the Northwind.mdb sample database for our example. Import both these tables from the Northwind.mdb sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

The sample Query (in normal style) uses both Tables in the Query, linked to the OrderID Field of both tables to create an Order-wise Summary from the Order Details Table.

SELECT Orders.OrderID,
 Orders.CustomerID,
 Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderVal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID;

The same result can be achieved without placing the Order Details Table in the Query design. We will write an expression in a separate Column using a subquery to pull the Summary Order-wise Total Value directly from the Order Details Table. Here is the example SQL String:

SELECT Orders.*,
    (SELECT  Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue
     FROM [Order Details] AS ODetails WHERE ([ODetails].[OrderID] = [Orders].[OrderID])
GROUP BY [ODetails].OrderID) AS OrderVal, [OrderVal]*0.12 AS Tax
FROM Orders;

In the Orders table, each order can have multiple related records in the Order Details table. By using a subquery (a totals query), we can calculate the total sales value for each order. The result of this calculation is then displayed in the corresponding row of the output. In other words, the subquery functions as an independent expression in its own column, running separately for each row in the Orders table.

The new column name OrderValue, created, can be part of other expressions, and we have calculated the Tax value, 12% of Order Value, in a separate column.

Let us take a closer look at the Sub-Query.

The SELECT clause uses only one output column (Sum((1-[Discount])*[UnitPrice]*[Quantity]) AS OrderValue), and the expression is named OrderValue. You should not use more than one column in the SELECT clause.
In the FROM clause, the Order Details Table is given a new name (ODetails), and the name is used to qualify the OrderID field in the WHERE clause. The OrderID field appears in both the Orders and Order Details Tables.
The WHERE clause in the Subquery is necessary to match the OrderIDs of the tables and place the calculated value of the Order row matched Records.

Earlier Post Link References:

GetRows Function and Exporting Data

a.p.r. pillaiDecember 25, 2010Export, Records, Utilities, Utility 1 comment

GetRows Function and Exporting Data.

We will create a useful data export utility with the GetRows() method of the Recordset Object. The GetRows method loads the entire data set from the table into memory as a two-dimensional Variant Array in a single statement. Let us try it with an example to see how this is done. An image of a sample table is given below, and we will see what this will look like in memory when loaded with the help of the GetRows() method of the Recordset Object:

Table: Employees
ID	Name	Birthdate	Height	Weight
1	Nancy	12/10/1980	164	58
2	Peter	05/07/1975	180	80
3	Linda	17/11/1982	170	60

The VBA Code.

The following sample VBA Routine loads the above data into memory, and a listing is dumped in the Debug Window:

Public Function Test(ByVal tblName As String)
Dim db As Database, rst As Recordset, varData As Variant
Dim intFields As Integer, intRecords As Integer, j As Integer, k As Integer
Dim rec As String, fld_type As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset(tblName, dbOpenTable)
j = rst.RecordCount - 1
k = rst.Fields.Count - 1

varData = rst.GetRows(j + 1)

For intRecords = 0 To j
    rec = ""
    For intFields = 0 To k
    fld_type = rst.Fields(intFields).Type

    If fld_type = 11 Or fld_type = 12 Then
      GoTo nextField
    End If
      rec = rec & varData(intFields, intRecords) & ","
nextField:
    Next
    rec = Left(rec, Len(rec) - 1)
    Debug.Print rec
Next
rst.Close
Set rst = Nothing
Set db = Nothing
End Function

The arrangement of records in memory in a two-dimensional array looks like the following:

1	2	3
Nancy	Peter	Linda
12/10/1980	05/07/1975	17/11/1982
164	180	170
58	80	60

The Memory Image of the Data.

The records are not stored in the same row-wise order as they appear in Datasheet view; instead, they are loaded into memory in columns. Once the data is in memory, it is important to understand how to reference the two-dimensional array correctly to access and output each record in the proper order. In the Debug window, you can see that each field value is separated by a comma in the listing.

In a typical two-dimensional array, the first index represents the row number, and the second index represents the column number. However, in this case, the structure is reversed: the first index corresponds to the field order number, and the second index represents the record number. You can confirm this by examining the sample memory arrangement shown above.

Export Data in Text Format

We will write a small utility program to export any MS-Access Table into a comma-delimited Text/CSV File, so that the data can be easily transported through the internet or imported into other applications.

The VBA code of the program is given below:

Public Function CreateDelimited(ByVal xtableName As String, ByVal txtFilePath As String)
'-----------------------------------------------------
'Utility: CreateDelimited()
'Author : a.p.r.pillai
'Date   : Dec. 2010
'Purpose: Create Comma Delimited Text File from Table 
'Rights : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim db As Database, rst As Recordset
Dim varTable() As Variant, j As Long, k As Long
Dim rec As String, fld_type As Integer
Dim intRecords As Integer, intFields As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset(xtableName, dbOpenTable)
varTable = rst.GetRows(rst.RecordCount)
k = rst.Fields.Count - 1
j = rst.RecordCount - 1

Open txtFilePath For Output As #1
rec = ""
For intFields = 0 To k
   fld_type = rst.Fields(intFields).Type
        If fld_type = 11 Or fld_type = 12 Then GoTo nextField
   rec = rec & Chr$(34) & rst.Fields(intFields).Name & Chr$(34) & ","
nextField:
Next
rec = Left(rec, Len(rec) - 1)
Print #1, rec
For intRecords = 0 To j
    rec = ""
    For intFields = 0 To k
        fld_type = rst.Fields(intFields).Type
        If fld_type = 11 Or fld_type = 12 Then GoTo Next_Field
        rec = rec & IIf(fld_type = 10, Chr$(34) & varTable(intFields, intRecords) & Chr$(34), varTable(intFields, intRecords)) & ","
Next_Field:
        Next: rec = Left(rec, Len(rec) - 1)
        Print #1, rec
Next
Close #1
rst.Close

Set rst = Nothing
Set db = Nothing

End Function

The Utility can be called from a Command Button Click Event procedure after setting the Table name and the target file pathname in text boxes. You can test the utility by calling it from the Debug Window (Immediate Window) directly as given below:

CreateDelimited "Products", "C:\Temp\Products.txt"

NB: If MEMO or Photo Fields are present in the Table, they are excluded from the output file.

The target file extension can be either .TXT or .CSV.

Easy-Read Reports.

Light Shading of Alternate Report Data Lines

The Report Module VBA Code.

Top N Records in Query.

Review of Rules of Queries.

Create a Sample Query.

Eliminating Duplicate Records.

Attachment Field in Access 2007.

A Sample Demo.

PrimaryKey usage with Many Fields.

Index having more than one Field Value.

Percentage on Report Summary.

Design a Sample Report to Try.

Creating Page-Level Totals.

Embedded Macros in Access2007.

Report Design in Access2007.

The Report View Feature.

The Layout View Feature.

Memo Field Text Formatting.

A Sample Quick Run.

Sub-Query in Query Column Expressions.

When the Report Requirement is Complicated.

Using a Sub-Query in the Criteria Row.

The Sub_Query in a Query Column.

Earlier Post Link References:

GetRows Function and Exporting Data.

The VBA Code.

The Memory Image of the Data.

Earlier Post Link References:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter