کوئری جدول متقاطع با TRANSFORM و PIVOT در SQL اکسس

کوئری totals معمولی هر گروه را در یک سطر نشان می‌دهد، اما جدول متقاطع یک بُعد گروه‌بندی را به ستون تبدیل می‌کند و ماتریسی مانند دسته محصول بر اساس ماه، کارمند بر اساس وضعیت یا واحد بر اساس سال می‌سازد. این شکل برای گزارش فشرده مناسب است.

کوئری جدول متقاطع در SQL اکسس از TRANSFORM، SELECT، GROUP BY و PIVOT استفاده می‌کند. در این درس نقش هر بخش، تعریف پارامتر، ستون ثابت و پویا، سلول Null، مجموع سطر و خواندن فیلدهای متغیر از VBA بررسی می‌شود.

این تکنیک بر توابع تجمیعی بنا شده است، زیرا هر خانه ماتریس خلاصه چند رکورد جزئیات است. join و فیلتر نیز باید صحیح باشند؛ تکرار یک ردیف OrderDetails مقدار خانه مربوط به ماه و دسته را افزایش می‌دهد.

جایگاه این درس در مسیر آموزش SQL اکسس

جدول متقاطع در پایان این مسیر پنج‌درس قرار می‌گیرد، چون تجمیع، پارامتر، join و طراحی شکل خروجی را هم‌زمان به‌کار می‌برد. برای فرم قابل ویرایش مناسب نیست، اما در گزارش، export و تحلیل بسیار مؤثر است.

پیش از نوشتن SQL سه چیز را تعیین کنید: عنوان سطر، عنوان ستون و مقدار داخل سلول. سپس تصمیم بگیرید مصرف‌کننده ستون پویا را می‌پذیرد یا به فهرست ثابت نیاز دارد. گزارش و VBA معمولاً با ستون ثابت قابل نگهداری‌ترند.

پیش‌نیازها و مدل داده نمونه

در مثال‌ها از جدول‌های Customers، Orders، OrderDetails، Products و Categories استفاده می‌شود. CustomerID کلید اصلی مشتری، OrderID کلید اصلی سفارش و ProductID کلید اصلی محصول است. فیلدهای کلید خارجی باید با کلید اصلی متناظر نوع داده سازگار داشته باشند و در پایگاه‌های بزرگ برای join و فیلترهای پرتکرار ایندکس شوند.

پیش از اجرای مثال‌ها از فایل اکسس نسخه پشتیبان تهیه کنید و نام جدول‌ها و فیلدها را با پایگاه خود تطبیق دهید. اگر نامی فاصله دارد آن را داخل کروشه قرار دهید. ابتدا یک SELECT ساده با تعداد رکورد محدود بسازید و سپس clauseها، پارامترها یا عبارت‌های پیشرفته را مرحله‌به‌مرحله اضافه کنید.

ساختار اصلی و مدل ذهنی

کوئری جدول متقاطع با TRANSFORM مقدار تجمیعی، با SELECT و GROUP BY عنوان سطر و با PIVOT عنوان ستون را تعیین می‌کند. فهرست اختیاری IN پس از PIVOT می‌تواند ترتیب ستون‌ها را ثابت کند و برای گزارش و VBA مفید است.

TRANSFORM Sum(OD.Quantity * OD.UnitPrice) AS SalesValue
SELECT C.CategoryName
FROM (Categories AS C
INNER JOIN Products AS P
    ON C.CategoryID = P.CategoryID)
INNER JOIN (Orders AS O
INNER JOIN OrderDetails AS OD
    ON O.OrderID = OD.OrderID)
    ON P.ProductID = OD.ProductID
GROUP BY C.CategoryName
PIVOT Format(O.OrderDate, 'yyyy-mm');

دستور را بر اساس جریان منطقی داده تحلیل کنید: ابتدا سطرهای منبع مشخص می‌شوند، سپس فیلتر سطح رکورد اجرا می‌شود، داده در صورت نیاز ترکیب یا گروه‌بندی می‌شود، ستون‌های خروجی محاسبه می‌شوند، شرط سطح نتیجه اعمال می‌شود و در پایان مرتب‌سازی صورت می‌گیرد. این مدل نشان می‌دهد چرا جابه‌جایی یک شرط می‌تواند نتیجه را تغییر دهد.

کاربردهای مناسب

• فروش ماهانه بر اساس دسته
• وضعیت حضور کارمند در هفته
• تعداد تیکت بر اساس تیم و اولویت
• بودجه واحد بر اساس فصل
• پاسخ نظرسنجی بر اساس سؤال و گزینه

نمونه کاربردی با قواعد واقعی

مثال پیشرفته فروش را بر اساس دسته محصول و ماه خلاصه می‌کند، سفارش‌ها را با پارامتر تاریخ محدود می‌سازد، RowTotal می‌افزاید و دوازده ستون ثابت برای سال 2026 ایجاد می‌کند.

PARAMETERS [pStartDate] DateTime, [pEndDate] DateTime;
TRANSFORM Sum(OD.Quantity * OD.UnitPrice) AS SalesValue
SELECT C.CategoryName,
       Sum(OD.Quantity * OD.UnitPrice) AS RowTotal
FROM (Categories AS C
INNER JOIN Products AS P
    ON C.CategoryID = P.CategoryID)
INNER JOIN (Orders AS O
INNER JOIN OrderDetails AS OD
    ON O.OrderID = OD.OrderID)
    ON P.ProductID = OD.ProductID
WHERE O.OrderDate Between [pStartDate] And [pEndDate]
GROUP BY C.CategoryName
PIVOT Format(O.OrderDate, 'yyyy-mm')
IN ('2026-01','2026-02','2026-03','2026-04','2026-05','2026-06','2026-07','2026-08','2026-09','2026-10','2026-11','2026-12');

بخش PARAMETERS مهم است، زیرا Access ممکن است پارامتر را در Crosstab تشخیص ندهد. فهرست IN ماه‌ها را حتی در نبود فروش ثابت نگه می‌دارد و Nz می‌تواند سلول خالی را فقط برای نمایش به صفر تبدیل کند.

اجرای کوئری از VBA

برای اتوماسیون قابل نگهداری، SQL را به‌صورت یک QueryDef نام‌دار ذخیره و از DAO اجرا کنید. پارامتر صریح از اتصال متن به رشته SQL بهتر است، زیرا Access نوع داده را پیش از اجرا می‌شناسد. روال زیر الگوی عمومی را نشان می‌دهد؛ نام QueryDef و پارامترها را با کوئری ذخیره‌شده همین درس هماهنگ کنید.

Option Explicit

Public Sub ReadMonthlySalesCrosstab()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field

    On Error GoTo CleanFail

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryMonthlySalesCrosstab")
    qdf.Parameters("pStartDate") = DateSerial(2026, 1, 1)
    qdf.Parameters("pEndDate") = DateSerial(2026, 12, 31)
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)

    Do While Not rs.EOF
        For Each fld In rs.Fields
            Debug.Print fld.Name & "=" & Nz(fld.Value, 0) & "; ";
        Next fld
        Debug.Print
        rs.MoveNext
    Loop

CleanExit:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

CleanFail:
    Debug.Print Err.Number, Err.Description
    Resume CleanExit
End Sub

در این نمونه Recordset از نوع snapshot برای خواندن نتیجه باز می‌شود. برای INSERT، UPDATE یا DELETE باید از روش اجرای action query استفاده و RecordsAffected را کنترل کنید. بخش پاک‌سازی نیز باید اشیا را در حالت موفق و خطا ببندد تا اتصال یا Recordset باز باقی نماند.

روش کنترل نتیجه

1. کوئری منبع را بدون clause پیشرفته اجرا و تعداد سطرهای اولیه را ثبت کنید.
2. یک حالت کوچک را دستی محاسبه و با خروجی SQL مقایسه کنید.
3. پارامتری وارد کنید که نتیجه خالی بدهد و رفتار کد فراخوان را بررسی کنید.
4. یک مقدار Null یا رکورد تکراری آزمایشی اضافه و انطباق نتیجه با قاعده مستند را کنترل کنید.
5. تعداد رکوردهای QueryDef ذخیره‌شده را با Recordset بازشده در VBA مقایسه کنید.
6. نام و نوع ستون‌های نهایی مورد نیاز گزارش یا export را ثبت کنید.

خطاهای رایج و روش اصلاح

کارایی، ایمنی و نگهداری

برای اجرای مطمئن کوئری جدول متقاطع در SQL اکسس بهتر است ابتدا یک نسخه آزمایشی از پایگاه داده بسازید و چند رکورد با مقادیر قابل تشخیص در آن قرار دهید. کوئری را پیش از اتصال به فرم، گزارش یا کد VBA مستقیماً در محیط Query Design اجرا کنید. این روش خطای طراحی SQL را از خطای اتوماسیون جدا می‌کند و بررسی نتیجه را ساده‌تر می‌سازد.

زبان SQL اکسس به SQL استاندارد نزدیک است، اما در نشانه‌گذاری تاریخ، wildcardها، پارامترها و موتور عبارت‌ها تفاوت‌هایی دارد. هنگام کار با کوئری جدول متقاطع در SQL اکسس ابتدا دستور را در خود Access آزمایش کنید. پس از تأیید نتیجه، همان منطق را به QueryDef ذخیره‌شده یا رشته SQL در VBA منتقل کنید تا خطاهای نقل‌قول و نوع پارامتر زودتر آشکار شوند.

برای فیلدهای محاسباتی alias روشن انتخاب کنید. نام مستعار باید معنای خروجی را نشان دهد، نه اینکه فقط عبارت را تکرار کند. alias مناسب اتصال گزارش، خواندن Recordset در VBA و نگهداری پروژه را آسان می‌کند. اگر نام فیلد فاصله دارد یا با واژه رزروشده تداخل پیدا می‌کند، آن را داخل کروشه قرار دهید.

رفتار مقدار Null را از ابتدا مشخص کنید. توابع تجمیعی، شرط‌ها، joinها و عبارت‌های محاسباتی همگی Null را یکسان مدیریت نمی‌کنند. ابتدا تعیین کنید Null در منطق کسب‌وکار به معنی مقدار ناشناخته، نامرتبط یا صفر است. تابع Nz را فقط زمانی به‌کار ببرید که جایگزینی Null با مقدار پیش‌فرض واقعاً درست باشد.

اعتبارسنجی ورودی را از اجرای کوئری جدا کنید. تاریخ‌ها، شناسه‌ها و حدود عددی باید پیش از تخصیص به پارامتر بررسی شوند. متن واردشده توسط کاربر را در صورت وجود پارامتر به SQL نچسبانید. پارامترها علاوه بر کاهش خطاهای قالب تاریخ و جداکننده اعشاری، مشکل apostrophe در متن را نیز کنترل می‌کنند.

کارایی را با داده‌ای نزدیک به حجم واقعی اندازه بگیرید. کوئری‌ای که روی بیست رکورد سریع است ممکن است روی صدها هزار رکورد کند شود. فیلدهای پرتکرار در join و فیلتر انتخابی را ایندکس کنید، روی فیلد ایندکس‌شده تابع غیرضروری اعمال نکنید و فقط ستون‌هایی را برگردانید که مرحله بعد واقعاً لازم دارد.

برنامه آزمون باید داده معمولی، مقدار مرزی، Null، رکورد تکراری و نتیجه خالی را پوشش دهد. بررسی کنید اگر هیچ رکوردی پیدا نشد، اگر یک گروه فقط یک عضو داشت یا اگر دو سطر مقدار مرتب‌سازی یکسان داشتند، خروجی همچنان قابل پیش‌بینی باشد. نتیجه مورد انتظار را پیش از بازنویسی کوئری ثبت کنید.

کوئری ذخیره‌شده بخشی از مستندات پروژه است. برای QueryDef نام توصیفی انتخاب کنید، در ماژول VBA یک توضیح کوتاه درباره ورودی و خروجی بنویسید و هر کوئری را بر یک مسئولیت متمرکز نگه دارید. چنین کوئری‌ای مستقیماً قابل آزمایش است و می‌تواند میان فرم، گزارش و کد مشترک باشد.

اگر خروجی کوئری برای گزارش مهم یا عملیات تغییر داده استفاده می‌شود، پارامترها و تعداد رکوردهای برگردانده‌شده را ثبت کنید. لازم نیست داده حساس در log ذخیره شود. زمان اجرا، نام کوئری، خلاصه پارامترها و تعداد سطرها معمولاً برای عیب‌یابی کافی است.

کوئری جدول متقاطع در SQL اکسس را بخشی از یک زنجیره پردازش داده ببینید. ورودی، شکل خروجی و مصرف‌کننده نتیجه باید روشن باشد. اینکه خروجی برای نمودار، گزارش، export به Excel یا مرحله ویرایش داده استفاده می‌شود، روی aliasها، ترتیب، سیاست Null و میزان جزئیات اثر مستقیم دارد.

تمرین مرحله‌به‌مرحله

تمرین را در سه نسخه بسازید. در نسخه اول فقط سطرهای خام را با join و فیلتر ضروری برگردانید. در نسخه دوم تکنیک اصلی این درس را اضافه و نتیجه را با داده کم کنترل کنید. در نسخه سوم پارامتر، alias، مرتب‌سازی و روال VBA را بیفزایید. هر نسخه را موقتاً با نام جدا ذخیره کنید تا علت هر تغییر در خروجی مشخص باشد.

در تمرین دوم فقط یک قاعده کسب‌وکار را تغییر دهید؛ برای نمونه بازه گزارش، حذف سفارش‌های لغوشده، انتخاب دسته متفاوت یا تغییر خروجی از جزئیات به خلاصه. کوئری مناسب باید اجازه دهد این تغییر در یک پارامتر، شرط یا عبارت محاسباتی انجام شود و به بازنویسی چند بخش نامرتبط نیاز نداشته باشد.

در پایان کوئری را از دید توسعه‌دهنده دیگری بازبینی کنید. آیا بدون بازکردن فرم‌ها می‌توان جدول‌های ورودی، پارامترها، ستون‌های خروجی و سطح هر سطر را تشخیص داد؟ aliasهای مبهم را اصلاح، ستون‌های بلااستفاده را حذف و در ماژول VBA یک توضیح کوتاه درباره فرض‌ها ثبت کنید.

درس‌های مرتبط این مجموعه

این درس پس از مباحث SELECT، WHERE، JOIN و ویرایش داده قرار می‌گیرد. برای مرور عملیات تغییر رکورد می‌توانید مقاله ویرایش و حذف داده‌ها در SQL اکسس با VBA را بخوانید. پیشنهادهای لینک داخلی، درس‌های بعدی این مجموعه را نیز تا زمان انتشار با وضعیت planned نگه می‌دارند.

جمع‌بندی

پس از این درس می‌توانید ابعاد ماتریس را انتخاب کنید، TRANSFORM و PIVOT بنویسید، پارامتر تعریف کنید، ستون‌ها را ثابت سازید، Null را مدیریت و نتیجه را از VBA یا گزارش مصرف کنید.

روش قابل اعتماد، ساخت تدریجی است: SQL را با داده کنترل‌شده ثابت کنید، نوع پارامترها را صریح بنویسید، رفتار Null و داده تکراری را بیازمایید و سپس اتوماسیون را اضافه کنید. با این ترتیب، تغییر جدول‌ها، فرم‌ها یا نیاز گزارش‌گیری هزینه کمتری خواهد داشت.