Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










Recipe 1.4 Make Formatted Date Columns Sort Correctly in a Crosstab Query



1.4.1 Problem


If you have a crosstab query that
uses the built-in

Format function to convert
dates into text for column headings, Access sorts them alphabetically
(Apr, Aug, and so on) rather than chronologically. For example, open

01-04.MDB and run the qryAlbumTypeByMonth1
crosstab query (see Figure 1-7). This query shows
the cross-tabulation of the number of albums purchased by album type
and the month the albums were purchased. The month columns are sorted
alphabetically instead of chronologically.


Figure 1-7. The months in qryAlbumTypeByMonth1 sort alphabetically


When the purpose of using the month in a crosstab query is to examine
chronological variation by month, this makes the crosstab query all
but useless. Is there some way to tell Access to sort the columns by
date rather than alphabetically?


1.4.2 Solution


The query properties sheet
allows you to specify fixed column headings for a crosstab query.
This solution illustrates how to use the ColumnHeadings property to
specify column headings so that formatted dates sort chronologically.

Follow these steps to create a crosstab query with correctly sorted
formatted-date columns:

  1. Create a select query. Select Query Crosstab to convert
    the query into a crosstab query.

  2. Add the
    columns you want to the crosstab query. Use a calculation for the
    Column Heading field. This calculation should use the built-in

    Format function to convert a normal date into an
    alphabetic string for cross-tabulation purposes. This might be the
    day of week or the month of yearin the example shown in Figure 1-7, we took the date field, DateAcquired, and
    formatted it as a three-letter month string. Add the remaining fields
    to qryAlbumTypeByMonth2, as shown in Table 1-3.

    All crosstab queries must have at least three fields: Row Heading,
    Column Heading, and Value.


Table 1-3. Field settings for the qryAlbumTypeByMonth2 crosstab query

Field


Table


Total


Crosstab


AlbumType


tblAlbums


Group By


Row Heading


Month: Format([DateAcquired],
"mmm")


Group By


Column Heading


Album ID


tblAlbums


Count


Value

  1. Select View Properties if
    the properties sheet is not already visible. Click on any part of the
    background of the upper-half of the query screen. This will select
    the properties for the query itself (as opposed to the Field or
    FieldList properties). Enter the values of the formatted date, in the
    order in which you want them to appear, into the ColumnHeadings
    property. For the qryAlbumTypeByMonth2 query, add three-letter
    strings for each month of the year (see Figure 1-8). Separate each entry with a comma.



Figure 1-8. The query properties sheet for qryAlbumByMonth2


  1. Save and run the query. The date columns should be ordered
    chronologically.


Now run qryAlbumTypeByMonth2, which you'll also find
in

01-04.MDB . In this query, the months are
ordered chronologically (see Figure 1-9).


Figure 1-9. The months in qryAlbumTypeByMonth2 sort chronologically



1.4.3 Discussion


When you
convert a date/time field to a formatted date using the Format
function, Access converts the date into a string. This means that the
formatted date will sort alphabetically, like any other string.
Access includes a special query property, ColumnHeadings, to make it
easy to work around this unpleasant side effect of using the

Format function.

You aren't limited to using fixed column headings
with formatted date strings. This crosstab query property comes in
handy for several other situations. For example, you might use the
ColumnHeadings property to:

  • Force a crosstab to always contain a column heading, even if no
    values exist for that column. For example, you could use the
    ColumnHeadings property to include all employee names in a crosstab
    report, even if one of the employees has no sales for the reporting
    period.

  • Force a unique ordering for the columns of a crosstab query. For
    example, if your Column Heading field is made up of the names of
    regions, you can use the ColumnHeadings property to ensure that the
    home region always appears as the leftmost column.

  • Eliminate a column value. If the ColumnHeadings property contains any
    values, any column headings in the result set that are not listed in
    the property will be left out of the crosstab query. You can also
    accomplish this by using query criteria.



When you use the ColumnHeadings property, you must spell the column
heading values exactly as they appear in your data. If you misspell a
column heading value, that value will not appear in the crosstab
query. For example, if you use Format(datefield,
"ddd") as the Column Heading field and create
fixed column headings of Mon, Tue, Wed, Thr, Fri, Sat, and Sun, the
Thr column will be completely blank because the

Format function returns Thu (not Thr) for day of
week.

You cannot set the ColumnHeadings property programmatically. Setting
this property in the Access user interface causes an IN clause to be
added to the SQL of the query, and the only way to accomplish this in
code is to add or modify that IN clause yourself.


/ 232