انواع کوئری در اکسس

یکی از علت‌های استفاده از بانک‌های اطلاعاتی یا پایگاه داده، بهره‌گیری از امکانات آن‌ها برای ایجاد گزارش است. این گزارش‌ها در پایگاه داده به «کوئری» (Query) یا «پرس‌وجو» معروف است. از آنجایی که کار با پایگاه داده اکسس (MS-ACCESS) ساده و کاربر پسند است، در این نوشتار می‌خواهیم با کوئری در اکسس و انواع آن آشنا شویم. هر چند پرس و جو ساختار مشخصی دارد ولی عملکرد آن‌ها روی جدول‌های پایگاه داده متفاوت است به همین علت در اکسس چند نوع کوئری وجود دارد که در ادامه به هر یک از آن‌ها خواهیم پرداخت.

به منظور آشنایی بیشتر با مفاهیم اولیه پایگاه داده و کار با اکسس بهتر است ابتدا نوشتارهای دیگر مجله فرادرس با عنوان‌های پایگاه داده و اصطلاحات کاربردی آن — به زبان ساده و آموزش اکسس (Access) رایگان | به زبان ساده و کامل را مطالعه کنید. همچنین خواندن مطالب رابطه در اکسس — راهنمای کاربردی و معرفی زبان SQL — راهنمای جامع نیز خالی از لطف نیست.

کوئری در اکسس و انواع آن

پرس و جو یا کوئری (Query) در واقع نوعی جستجوی پیشرفته است. به کمک کوئری‌ها، می‌توانیم به رکوردهایی از جدول (Table) دسترسی داشته باشیم که مطابق با نیاز یا شرط تعیین شده در اختیارمان قرار می‌گیرند. با کوئری کنترل بیشتری روی نتیجه جستجو داشته و اگر با جدول‌های مرتبط سر و کار داشته باشیم، با توجه به رابطه بین جدول‌ها، کوئری‌ها امکان تهیه گزارشات ترکیبی را هم فراهم می‌آورند.

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

نکته: برای سادگی و هماهنگی نحوه کار خوانندگان و مشاهده نتایج اجرای کوئری‌ها در این متن، یک پایگاه داده در اکسس تولید شده که در این نوشتار مورد استفاده قرار می‌گیرد. برای دریافت فایل پایگاه داده اکسس در قالب فشرده، اینجا را کلیک کنید. پس از خارج کردن فایل از حالت فشرده، می‌توانید فایل QRelation Types.accdb را در اکسس باز کرده و جدول‌ها و کوئری‌های ایجاد شده را مشاهده یا ویرایش کنید.

به منظور ایجاد یک پرس و جو یا کوئری در اکسس لازم است از برگه Create، یکی از گزینه‌های طراحی کوئری (Query Design) یا راهنمای ایجاد کوئری (Query Wizard) را انتخاب کنید. هر چند روش Query Wizard ساده‌تر بوده و با طی کردن یک مسیر، پرس و جوی مورد نظرتان را ایجاد می‌کند ولی بسیاری از ویژگی‌های مربوط به طراحی پرس و جو را در بر نمی‌گیرد. البته نحوه به کارگیری آن نیز در نوشتار دیگری از مجله فرادرس مرور شده است. به همین دلیل در این متن به نحوه ایجاد کوئری بوسیله محیط طراحی (Query Design) خواهیم پرداخت. کوئری‌هایی که به این ترتیب می‌توانیم ایجاد کنیم به صورت زیر فهرست و معرفی شده‌اند.

• پرس و جو یا کوئری انتخاب (Select Query): در این نوع کوئری، صرفا رکوردهایی از جدول‌های مورد نظر انتخاب و نمایش داده می‌شوند. البته به کمک این نوع کوئری، می‌توانیم فیلدهای محاسباتی نیز ایجاد کنیم.
• پرس و جو یا کوئری ایجاد جدول (Make Table Query): اگر بخواهیم نتیجه جستجو یا محاسبات صورت گرفته در یک جستجو را در قالب یک جدول ذخیره و ثبت کنیم از «کوئری ایجاد جدول» (Make Table) استفاده می‌کنیم.
• پرس و جوی یا کوئری اضافه کردن رکورد به جدول (Append Query): به کمک این نوع کوئری، رکوردهای مورد جستجو به یک جدول اضافه می‌شود. این جدول ممکن است قبلا توسط کوئری ایجاد جدول (Make Table) ساخته شده باشد.
• پرس و جو یا کوئری به روز رسانی (Update Query): اگر بخواهیم مقدار یک یا چند فیلد از رکوردهای یک جدول را به یکباره تغییر و به روز رسانی کنیم، «کوئری به روز رسانی» Update، گزینه مناسبی خواهد بود.
• پرس و جو یا کوئری جدول توافقی (Crosstab Query): اگر بخواهیم یک جدول محاسباتی براساس جمع‌بندی یا گروه‌بندی روی یک جدول تشکیل دهیم، کوئری جدول توافقی بهترین راه حل است. به این ترتیب می‌توانیم برای مثال تعداد خانه‌های محله‌های مختلف را با توجه به نام خریدار، مشخص کنیم. خروجی این کوئری‌ها بسیار شبیه جدول‌های محوری (Pivot Table) در اکسل است.
• پرس و جو یا کوئری حذف (Delete Query): گاهی لازم است رکوردهای جستجو شده توسط کوئری، از جدول حذف شوند. البته این کار باید با احتیاط صورت گیرد، زیرا رکوردهای حذف شده از جدول، قابل بازیابی نیستند. کوئری حذف برای انجام این کار در اکسس اختصاص یافته است.

همچنین برای اجرای هر یک از کوئری‌های ایجاد شده، کافی است از بخش Result در برگه طراحی یا Design کوئری‌ها، یکی از گزینه‌های نمایش (View) یا اجرا (Run) را کلیک کنید. البته در اکثر مواقع از دکمه Run برای اجرا استفاده می‌کنیم، زیرا ممکن است اجرای یک کوئری، همیشه نتیجه قابل نمایش در یک جدول، نداشته باشد.

تصویر ۱: دسترسی به فرمان اجرای کوئری

حتما به این موضوع توجه داشته باشید که کوئری یا پرس‌وجوهای اکسس قابل ذخیره شدن در پایگاه داده هستند. بنابراین هر گاه مقادیر جدول‌ها تغییر یابد، باز هم می‌توان از کوئری‌ها استفاده کرد و با اجرای آن‌ها همان سوالات یا درخواست‌ها را از جدول پرسید، با این تفاوت که با توجه به تغییر مقادیر جدول، پاسخ‌ها ممکن است تغییر یابند. به این ترتیب طرح سوال و پرسش ثابت بوده ولی با هر بار اجرای کوئری، نتیجه می‌تواند تغییر کند. این ویژگی مهمی است که یکی از جنبه‌های تفاوت بین کاربرگ‌های الکترونیکی (SpreadSheets) و پایگاه‌های داده (DataBases) را شامل می‌شود.

در ادامه متن به نحوه ایجاد این گونه کوئری‌ها با ویژگی‌های مختلف خواهیم پرداخت.

کوئری انتخاب (Select Query)

یکی از معروف‌ترین و البته کاربردی‌ترین پرس و جو‌ها در اکسس، «کوئری انتخاب» (Select Query) است. به کمک این نوع کوئری، یک یا ترکیبی از عبارت‌های شرطی تعیین می‌کنید. تمامی رکوردهایی از جدول که این شرط‌ها برایشان صدق می‌کند، به عنوان نتیجه کوئری ظاهر می‌شوند. البته توجه دارید که اطلاعات جدول تغییری نکرده ولی یک پرس و جو از جدول صورت گرفته که نتیجه آن به شکل یک جدول ارائه می‌شود. در چنین جدولی می‌توانید از نمایش فیلدهای دلخواه بهره ببرید. به این ترتیب هم رکوردها و هم فیلدها در نتیجه کوئری قابل تعیین هستند.

به این منظور از جدول insure موجود در فایل پایگاه داده QRelation Types.accdb کمک می‌گیرم. در تصویر ۲ رکوردهای این جدول را مشاهده می‌کنید. این جدول شامل مشخصات بیمه‌ای افراد است. فیلد نوع بیمه (insurancetype) فیلدی است که می‌خواهیم براساس آن جستجو یا کوئری بسازیم.

تصویر ۲: جدول بیمه (insure) در پایگاه داده Relation Types.accdb

فرض کنیم می‌خواهیم افرادی که از جدول insure دارای نوع بیمه برابر با tamin هستند را طی یک کاربرگ اطلاعاتی نمایش دهیم. همانطور که گفته شد این جستجو توسط کوئری قابل اجرا است. به منظور انجام این کار مراحل زیر را پی‌می‌گیریم:

• پایگاه داده QRelation Types.accdb را در اکسس باز می‌کنیم.
• از برگه Create گزینه Query Design را از بخش Queries انتخاب می‌کنیم.
• در پنجره Show Table، جدول insure را انتخاب و با فشردن دکمه Add، آن را به صفحه Query انتقال می‌دهیم. این پنجره در تصویر ۳ دیده می‌شود.
• پس از اجرای مراحل بالا، دکمه Close را کلیک کرده تا به پنجره ویرایش Query Design دسترسی پیدا کنیم.

تصویر ۳: پنجره انتخاب جدول برای اجرای کوئری

• در هر ستون از پنجره حاصل با توجه به صورت گزارش یا پرس و جو، فیلدی که می‌خواهیم نسبت به آن اقدام کنیم را در قسمت Field قرار می‌دهیم. این کار را به کمک کشیدن و رها کردن یا انتخاب در قسمت مورد نظر، انجام می‌دهیم.
• در سطر Criteria مقدار مورد نظر برای پرس و جو تحت فیلد insurancetype را وارد می‌کنیم که در اینجا مقدار متنی tamin است. توجه دارید که به علت متنی بودن این عبارت، به شکل “tamin” ظاهر خواهد شد.
• برای نمایش همه فیلدهای مربوط به رکوردهای حاصل از جستجو، نشانه “*” را هم به سطر Field در یک ستون جداگانه قرار دهید.

تنظیمات یاد شده در تصویر ۴، دیده می‌شود.

تصویر ۴: پنجره طراحی کوئری

• دکمه Run را از زیر برگه Design در برگه Query Tools کلیک کنید تا پرس و جو یا کوئری اجرا شود.

نتیجه اجرای این پرس و جو طی نمایش یک جدول و مطابق با تصویر ۵ ظاهر می‌شود. همانطور که می‌بینید، همه رکوردهایی که مقدار فیلد insurancetype آن‌ها برابر با tamin بوده است، از جدول insure انتخاب شده و در یک ساختار جدول دیگر، نمایش داده شده‌اند. متاسفانه به نظر می‌رسد فیلد insurancetype دو بار در این خروجی (در فیلد Field0) دیده می‌شود. در ادامه متن این مشکل را رفع خواهیم کرد.

تصویر ۵: نتیجه اجرای کوئری به همراه تکرار فیلد مورد جستجو

به منظور درک بیشتر گزینه‌هایی که برای تعریف کوئری در این پنجره وجود دارد، آن‌ها را معرفی می‌کنیم.

• Field: این قسمت مربوط به مشخص کردن اسامی فیلدهایی از جدول یا جدول‌ها است که باید در پرس و جو نقش داشته باشند. معمولا در این قسمت فیلدی یا فیلدهایی که براساس آن باید جستجو صورت گیرد قرار داده می‌شود. هر فیلدی که در قسمت Field قرار گیرد، در نتیجه گزارش نیز ظاهر خواهد شد. گزینه‌ای به صورت نام جدول به همراه علامت * نیز در این قسمت دیده می‌شود. این گزینه به معنی همه فیلدهای جدول مورد نظر است. برای مثال *.insure به معنی نمایش همه فیلدهای جدول insure در نتیجه پرس و جو است.
• Table: ممکن است فیلدهایی که انتخاب کردید از چند جدول گرفته شده باشند. برای آنکه مشخص شود هر فیلد مربوط به کدام جدول است، قسمت Table، نام جدولی که فیلد انتخابی از آن آورده شده را مشخص می‌کند. به یاد دارید که می‌توان در جدول‌های مختلف اسامی فیلدها یکسان در نظر گرفته شود.
• Sort: این گزینه مشخص می‌کند اطلاعات جهت نمایش براساس کدام فیلد مرتب شود. همچنین انتخاب نوع مرتب‌سازی به صورت صعودی (Ascending) یا نزولی (Descending) نیز در این گزینه قابل تعیین است.
• Show: اگر برای هر فیلدی این گزینه فعال باشد، به این معنی است که فیلد مورد نظر باید در خروجی نشان داده شود. ممکن است فیلدی در جستجو نقش داشته باشد ولی نخواهیم که در نتیجه جستجو ظاهر شود. به این ترتیب با انتخاب show از نمایش آن صرفه نظر می‌کنیم. همانطور که دیدید، فیلد insurancetype دوبار در خروجی ظاهر شده. با برداشتن تیک برای این فیلد (با توجه به انتخاب گزینه *.insure برای نمایش همه فیلدها) از مشاهده مقادیر تکراری این فیلد در خروجی جلوگیری می‌کنیم.
• Criteria: در این بخش، شرط مورد نظر خود را جهت رکوردهای دلخواه، اعمال می‌کنیم. برای این کار می‌توانیم از دستورات و توابع مختلف برای اعمال شرط استفاده کنیم. عملگرهایی که در این قسمت قابل استفاده‌اند، در ادامه متن معرفی خواهند شد.
• Or: اگر بخواهیم ترکیب منطقی فصلی (یا) را براساس چندین شرط روی یک فیلد اعمال کنیم، در قسمت Or عبارت‌های منطقی را وارد می‌کنیم. بدیهی است اگر رکوردی در هر یک از این شرط‌ها صدق کند در نتیجه پرس و جو ظاهر خواهد شد.

اگر بخواهید فیلدی که به پنجره کوئری اضافه کردید را حذف کنید، کافی است روی ستون مربوط به آن فیلد در محیط Design Query، کلیک کرده و از بخش Query Setup فهرست ظاهر شده گزینه Delete را بزنید.

ایجاد فیلد محاسباتی با کوئری انتخاب

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

برای ایجاد یک فیلد محاسباتی، جدول house از پایگاه داده QRelation Types.accdb استفاده خواهیم کرد. فیلد squarmeter در این جدول، مساحت هر خانه را مشخص کرده است. با ضرب کردن این مقدار در عدد ۱۰۰۰ تومان می‌توانیم ارزش هر خانه را محاسبه کنیم. برای انجام این کار روال یا گام‌های زیر را بر می‌داریم.

• به کمک برگه Create یک پرس و جو در محیط طراحی (Design Query) ایجاد می‌کنیم.
• روی یکی از ردیف‌های مربوط به ستون Field کلیک کرده و از برگه Query Tools و زیر برگه Design دستور Build را از قسمت Query Setup اجرا می‌کنیم.
• نوع محاسبه را در پنجره ظاهر شده برحسب فیلدهای جدول مورد نظر مشخص کرده و دکمه OK را می‌زنیم. نحوه انتخاب فیلد در محاسبه، مطابق با تصویر ۶ است.
• کوئری را به کمک فرمان Run، اجرا می‌کنیم.

تصویر ۶: تنظیمات پنجره ساخت فیلد محاسبات در کوئری انتخاب

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

نتیجه اجرای این عملیات مطابق با تصویر ۷ خواهد بود. مشخص است که فیلد محاسباتی به صورت Expr1 در قسمت Field، دیده می‌شود. اگر بخواهیم نام این فیلد در پنجره نمایش نتیجه به شکل دیگری باشد، کافی است از قسمت Show/Hide گزینه Property Sheet را انتخاب و در بخش Caption، نام دلخواه را برای فیلد محاسباتی وارد کنید.

تصویر ۷: تنظیمات مربوط به کوئری برای ایجاد فیلد محاسباتی

نکته: واضح است که فیلد *.house را هم به علت نمایش همه فیلدهای این جدول به قسمت فیلدهای کوئری اضافه کرده‌ایم تا بقیه مشخصات رکوردهای مربوط به خانه‌ها نیز در نتیجه محاسبات و کوئری انتخاب ظاهر شود.

نتیجه اجرای این کوئری در تصویر ۸ ظاهر شده است. همانطور که می‌بینید همه فیلدها از جدول house به همراه قسمت محاسبه شده برای هر خانه در خروجی مطابق با یک جدول، ظاهر شده است.

تصویر ۸: نتیجه اجرای کوئری انتخاب با فیلد محاسباتی

اگر بخواهیم نرخ مسکن را در هر محله با توجه به فیلد area، به طور جداگانه محاسبه کنیم، کوئری انتخاب کاربرد مناسبی پیدا می‌کند. به این ترتیب با انتخاب مقدار vanak برای فیلد area، مبلغ متر را به ۵۰۰۰ تومان افزایش می‌دهیم و محاسبات مربوط به Build را برای چنین رکوردهایی اجرا می‌کنیم. کافی است به صورتی که در تصویر ۹ مشاهده می‌کنید، عمل کنیم.

تصویر ۹: فیلد محاسباتی برای رکوردهای خاص از جدول

عملگرهای بخش Criteria

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

عملگرهای ریاضی

اگر نوع داده‌ی فیلد به گونه‌ای انتخاب شود که بتوان عملیات ریاضی روی آن انجام داد (برای مثال Number,Currency,Date/Time) از عملگرهای ریاضی برای اعمال شرط استفاده می‌کنیم. برای مثال برای مشخص کردن خانه‌هایی با متراژ بیشتر از ۱۰۰ متر در Criteria عبارت شرطی 100< را وارد می‌کنیم. سایر عملگرهای ریاضی طبق جدول زیر معرفی می‌شوند.

جدول 1 – عملگرهای ریاضی

عملگرهای منطقی

به منظور ترکیب گزاره‌های شرطی با یکدیگر از عملگرهای منطقی استفاده می‌شود. به منظور درک بهتر عملکرد آن‌ها نیز از جدول ۲ بهره گرفته‌ایم.

جدول ۲ – عملگرهای منطقی

عملگرهای متنی

هر چند در مقدمه تهیه کوئری در اکسس از عملگر Like استفاده کردیم، ولی اینجا نیز به منظور تاکید بیشتر، عملگرهای متنی مانند Like را مطابق با جدول ۳، معرفی خواهیم کرد.

جدول ۳ – عملگرهای متنی

در ادامه به بررسی بعضی مثال‌ها در رابطه با این گونه عملگرها خواهیم پرداخت.

Like: اگر به دنبال افرادی در جدول Personal بودیم که نام خانوادگی آن‌ها با عبارت ahmadi شروع می‌شد، از عملگر Like و به صورت $$ \text{ Like “ahmadi*”}$$ استفاده می‌کردیم. همچنین اگر بخواهیم انتهای عبارت متنی به ahmadi ختم شود نیز از ترکیب عملگر Like و * به صورت $$\text{like “*ahmadi”}$$ کمک می‌گرفتیم. همچنین برای جستجو افرادی که در فامیلی آن‌ها عبارت ahmadi دیده می‌شود نیز از شرط $$\text{“*ahmadi*”}$$ بهره‌ می‌بردیم. همانطور که گفته شد علاوه بر * می‌توانیم از علامت ? نیز در عملگر Like استفاده کنیم. برای مثال اگر شرط “????” Like برای فیلد نام افراد استفاده شود، رکوردهایی که نام آنها چهار حرفی است، جستجو خواهد شد. یعنی از ? به جای یک کاراکتر استفاده می‌شود. در حالی که از * می‌توان بجای چند کاراکتر استفاده کرد.

Not: برای مثال اگر بخواهیم همه افراد بجز کسانی که نام خانوادگی آنها با احمدی شروع می‌شود را جستجو کنیم، باید از شکل$$\text{not like “ahmadi*”}$$  در Criteria برای فیلد fname استفاده کنیم. به عبارت دیگر با این دستور رکوردهایی را انتخاب می‌کنیم که شرط را دارا نیستند.

In: برای مثال برای جستجوی خانه‌هایی که در جدول house وجود دارند و محله آن‌ها یکی از مقادیر “vanak” یا “abas abad” است از عبارت (“In(“abas abad”,”vanak  استفاده کنیم.

Is Null: اگر بخواهیم رکوردهایی را انتخاب کنیم که مقدار یک فیلد آنها تهی یا خالی باشد از این عبارت استفاده می‌کنیم. برای مثال افرادی که در فیلد نام آن‌ها چیزی وارد نشده است را با استفاده از دستور Is Null در قسمت Criteria در Field نام (pname) جستجو می‌کنیم.

عملگر < یا >: درست مانند عملگرهای ریاضی می‌توان از عملگر مقایسه‌ای < (بزرگتر) یا > (کوچکتر) برای فیلدهای عددی استفاده کرد. برای مثال اگر بخواهیم خانه‌های با متراژ کمتر از ۱۰۰ متر را مشاهده کنیم، کافی است قید را در بخش Criteria برای فیلد squarmeters به صورت زیر بنویسیم.

$$ \large \text {< 100 }$$

عملگر <>: از این عملگر برای حالتی استفاده می‌شود که می‌خواهیم مخالفت با یک مقدار یا وضعیت را مشخص کنیم. به این ترتیب می‌توان گفت که این عملکرد حالت استثنا را نشان می‌دهد. مثلا برای عدم نمایش خانه‌هایی که مربوط به محله ونک هستند از رابطه زیر در قسمت Criteria برای فیلد area، کمک می‌گیریم.

$$ \large \text{ <>  “vanak” } $$

در ادامه به بررسی چند کوئری در اکسس خواهیم پرداخت که تعریف و ایجاد آن‌ها مشابه کوئری انتخاب بوده ولی عملکرد متفاوتی دارند.

کوئری ایجاد جدول (Make Table Query)

یکی از روش ثبت پاسخ‌ها یا نتایج حاصل از اجرای کوئری، ایجاد یک جدول است. در کوئری یا پرس و جوی ایجاد جدول (Make Table Query) این امر صورت می‌گیرد. هنگامی که این کوئری را از برگه Query Tools و زیربرگه Design انتخاب می‌کنید، اکسس نام جدولی که باید در پایگاه داده براساس این کوئری ایجاد شود را از شما دریافت می‌کند. در تصویر ۱۰ نمونه‌ای از پنجره گفتگو برای دریافت مشخصات جدول را مشاهده می‌کنید.

تصویر 10: پنجره گفتگو برای دریافت مشخصات جدول در کوئری ایجاد جدول

اگر گزینه Current Database را انتخاب کنید، جدول مورد نظر در پایگاه داده جاری، ایجاد خواهد شد. با فعال سازی گزینه Another Database، جدول در پایگاه دیگری ساخته و ذخیره می‌شود. پس از فشردن دکمه OK به پنجره کوئری برگشته و تنظیمات مربوط به نحوه انتخاب رکوردها تعیین می‌شوند. تنظیمات مربوط به این کوئری درست به مانند کوئری انتخاب بوده و مطابق با تصویر ۴ خواهد بود.

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

کوئری اضافه کردن رکورد (Append Query)

اگر لازم باشد که رکوردهای جدیدی از یک جدول به جدول دیگری اضافه شوند، کوئری دیگری مورد استفاده است که Append Query نامیده می‌شود. به محض انتخاب این نوع کوئری، اکسس از شما نام جدولی را می‌پرسد که باید رکوردهای انتخاب شده به آن اضافه شوند. پنجره ظاهر شده درست به مانند تصویر 10، خواهد بود.

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

کوئری به روز رسانی (Update Query)

فرض کنید در یک جدول، می‌خواهیم همه مقادیر فیلد محل تولد افراد (Birthplace) که به صورت «شهر شیراز»، مشخص شده به «شیراز» تغییر کند. این کار به صورت دستی بسیار مشکل و سخت است. ولی پرس و جوی به روز رسانی یا Update Query این کار را به سادگی انجام می‌دهد.

برای انجام این کار کافی است یک Update Query ایجاد کنید و جدول Personal را به آن اضافه کنید. در قسمت فیلد، Birthplace را قرار داده و شرط (Criteria) را “شهر شیراز” تعیین کنید. در قسمت Update To نیز عبارت “شیراز” را مشخص کرده و کوئری را اجرا کنید. نام محل تولد همه افراد از شهر شیراز به شیراز تغییر خواهد یافت. این نوع کوئری در اکسس بسیار به کار می‌رود.

کوئری متقاطع (Crosstab Query)

یکی از مزایای استفاده از پرس و جوی متقاطع یا Crosstab Query، بهره گیری از توابع محاسباتی است. پرس و جوی متقاطع، نوعی از کوئری در اکسس است که به واسطه متغیرهایی برای سطر یا ستون یک جدول متقاطع، می‌توان جمع‌بندی یا تابع محاسباتی را برای درون سلول‌های جدول ایجاد شده در نظر گرفت. فرض کنید می‌خواهیم مشخص کنیم که هر فرد در هر محله چند خانه دارد. به یاد دارید که جدول‌های personal, property و house با یکدیگر مرتبط هستند. به این ترتیب با ایجاد یک کوئری جدید از نوع Crosstab Query، جدول‌های مورد نظر را به پنجره کوئری در اکسس اضافه می‌کنیم.

برای تهیه گزارش گفته شده، تنظیمات مربوط به این کوئری را به شکل تصویر 11 در آورده و کوئری را اجرا می‌کنیم. به یاد داشته باشید که باید حتما فیلدی به عنوان سطر در چنین کوئری در سطر Crosstab معرفی شده باشد. فیلدهایی که در این قسمت به عنوان سطر (Row Heading) یا ستون (Column Heading) معرفی شده‌اند، جنبه دسته‌بندی یا طبقه‌بندی رکوردها را داشته و باید در سطر Total به عنوان Group By مشخص شوند.

فیلدی که باید محاسبات روی آن صورت گیرد در سطر Crosstab از نوع value بوده و به عنوان متغیر محاسباتی روی گروه‌ها به کار می‌رود. در ضمن برای تعیین نوع محاسبه نیز باید از سطر Total برای چنین فیلدی، نوع عملیات ریاضی مانند جمع (Sum)، میانگین (Avg)، شمارش (Count) و … را انتخاب کنید.

با توجه به درخواستی که از این کوئری در اکسس و پایگاه داده این متن داشتیم، تنظیمات باید به صورت تصویر 11 تعیین شود.

تصویر 11: نمایش تنظیمات پرس و جو یا کوئری متقاطع در اکسس

خروجی یا جدول حاصل از اجرای این کوئری نیز در تصویر ۱۲ قابل مشاهده است.

تصویر ۱۲: نمایش خروجی یک کوئری متقاطع در اکسس

همانطور که می‌بینید، فیلدی که در ستون (fname) قرار گرفته به همراه فیلد سطر (area) دسته یا گروه‌ها را تعیین کرده‌اند. به این ترتیب مشخص می‌شود که آقای ahmadi دارای دو خانه در vanak و یک خانه در ray است. همچنین kalami نیز یک خانه در abad dareh و یک خانه هم در abas abad دارد.

کوئری حذف (Delete Query)

به منظور حذف رکوردهای خاص از جدول یا جدول‌های مرتبط، از کوئری حذف (Delete Query) استفاده می‌شود. به این ترتیب رکوردهایی که با شرط معرفی شده در کوئری، مطابقت دارند، حذف شده و در صورت ارتباط بین جدول‌ها، ممکن است رکوردهای مرتبط در جدول‌های دیگر نیز حذف شوند. از آنجایی که امکان برگرداندن رکوردهای حذف شده در اکسس وجود ندارد، در نتیجه این کار باید با دقت زیاد صورت گیرد.

همانطور که در تصویر 13 می‌بینید، تنظیمات مربوط به کوئری حذف تفاوت زیادی با کوئری انتخاب ندارند. فقط سطر Delete به جای Sort قرار گرفته است. انتخاب‌هایی که در این بخش وجود دارد دو گزینه Where و From است.

گزینه Where: اگر بخواهید رکوردهایی را از جدول جاری به واسطه کوئری حذف، از بین ببرید از گزینه Where استفاده کنید. در این صورت فقط رکوردهای جدول جاری تحت تاثیر قرار می‌گیرند. واضح است که شرط برای انتخاب رکوردهای حذفی نیز باید در قسمت Criteria مشخص شود.

تصویر ۱۳: کوئری در اکسس از نوع حذف ساده

گزینه From: فرض کنید رکوردهایی را از جدول دیگری حذف کنید که با جدول جاری در ارتباط هستند. شرط انتخاب این رکوردها در جدول جاری است ولی نتیجه حذف باید روی جدول دیگری اعمال شود. در نتیجه با انتخاب جدول و فیلد مورد نظر از جدول ثانویه و همچنین تعیین گزینه From برای آن‌ها، به اکسس تذکر می‌دهید که شرط انتخاب رکوردها با مکانی که باید رکوردها از آن حذف شوند، تفاوت دارد.

تصویر 14: حذف رکوردهای مرتبط از یک جدول در جدول دیگر با گزینه From

کوئری در اکسس به صورت پارامتری

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

گاهی اوقات در زمان ایجاد یک پرس و جو نمی‌توانید شرط مورد نظر را دقیقا اعمال کنید و می‌خواهید در زمان اجرای کوئری، اطلاعات مورد نیاز از کاربر گرفته شود و براساس داده‌های وارد شده توسط کاربر، کوئری اجرا شود. برای این کار در بخش Criteria از علامتهای [   ] استفاده می‌کنیم. در داخل این علامت‌ها یک پیغام برای کاربر جهت ورود داده‌های مورد نظر می‌نویسیم. یعنی هنگام اجرای کوئری ابتدا یک «کادر گفتگو» (Dialog box) باز شده و داده‌ها را از کاربر سوال می‌کند و براساس مقادیر وارد شده، کوئری اجرا خواهد شد. برای ایجاد کوئری در اکسس به صورت پارامتری، روال زیر را طی می‌کنیم.

• ابتدا یک پرس و جو به کمک یکی از روش‌های گفته شده ایجاد می‌کنیم. این کار را می‌توان به کمک Query Design یا Query Wizard در برگه Create انجام داد. در اینجا فرض کرده‌ایم که از Query Design استفاده کرده‌ایم.
• جدول personal را به پنجره Query اضافه (Add) می‌کنیم.
• فیلد securitycode را به قسمت Field در کوئری می‌بریم.
• در قسمت Criteria عبارت [Enter Security Code] را می‌نویسیم.
• فیلد *.personal را در قسمت Field بعدی قرار می‌دهیم.
• گزینه Show را برای فیلد securitycode بر‌ می‌داریم.
• کوئری را با فرمان Run، اجرا می‌کنیم.

تنظیمات صورت گرفته در تصویر 15 دیده می‌شوند.

تصویر 15: ایجاد یک کوئری پارامتری در اکسس

پس از اجرای این کوئری یک پیغام توسط اکسس مانند تصویر نمایش داده می‌شود. با وارد کردن کد ملی فرد مورد نظر و فشردن دکمه Ok، سایر اطلاعاتش نمایش داده می‌شود.

تصویر 16: پنجره گفتگو دریافت پارامتر

نتیجه اجرای این کوئری با توجه به پارامتر وارد شده برای کد ملی طبق یک جدول ظاهر خواهد شد.

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

پرس و جو یا کوئری در اکسس از جدول‌های مرتبط

در نوشتار دیگری از مجله فرادرس با نحوه ایجاد رابطه (Relationship) بین جدول‌های اکسس آشنا شدید. در این بخش می‌خواهیم به کمک جدول property که جدول‌های personal و house را به یکدیگر متصل می‌کند، یک کوئری در اکسس ایجاد کنیم. به این ترتیب با مشخص کردن کد ملی یک فرد می‌توانیم مشخصات فردی و همچنین ملکی آن فرد را به همراه سهمی که در هر خانه دارد، دریافت کنیم. برای انجام این کار مراحل زیر را طی خواهیم کرد.

• در پایگاه داده QRelation Types.accdb، یک کوئری در اکسس با محیط طراحی ایجاد کنید.
• جدول‌های personal, house و property را به کوئری اضافه کنید.
• همه فیلدهای جدول person و همه فیلدهای جدول house را به کوئری اضافه کنید.
• کوئری را ذخیره و اجرا کنید.

با توجه به رابطه‌های ایجاد شده، این کوئری در اکسس باید به مانند تصویر 17 تنظیم شده باشد.

تصویر 17: نمایش کوئری در اکسس با ترکیب جدول‌ها

همانطور که مشخص است، همه افراد به همراه مشخصات خانه‌هایی که به اسمشان است، لیست شده و به این ترتیب یک گزارش ترکیبی از دو جدول تهیه می‌شود.

نکته: اگر شرایطی را داشته باشیم که بخواهیم اطلاعات را تنها از جدول‌های راست و چپ داشته باشیم (و نه از جدول دیگر)، باز هم باید جدول واسط (جدول Property) را در طراحی Query اضافه کنیم.

نتیجه اجرای این کوئری مطابق با تصویر 18 خواهد بود.

تصویر 18: نتیجه اجرای کوئری در اکسس با ترکیب چند جدول مرتبط

نمایش کدهای SQL برای کوئری در اکسس

همانطور که می‌دانید زبان پرس و جو استاندارد در همه پایگاه‌های داده یا بانک‌های اطلاعاتی، به نام SQL معروف است که از سر کلمه‌های عبارت Structured Query Language یعنی زبان ساخت یافته پرس و جو ساخته شده است.

در اکسس نیز برای نمایش کدهای استاندارد SQL برای هر کوئری می‌توانید در نمایش ویرایش کوئری (Design Query)، کدهای مرتبط با پرس و جوی ساخته شده را با کلیک روی گزینه SQL View، مشاهده کنید.

تصویر 19: نمایش SQL برای کوئری در اکسس

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

واضح است که در این کد، فرمان Select و بقیه دستورات SQL مانند FROM و WHERE، گویای عملی هستند که کوئری مورد نظر در اکسس اجرا می‌کرد. مشخص است که این کدها علاوه بر خوانایی، نشانگر عملیات ذکر شده هستند.

نکته: زبان SQL، به صورت استاندارد در بیشتر برنامه‌های مدیریت پایگاه داده (DBMS) حضور دارد. به همین علت گاهی نام‌گذاری چنین برنامه‌هایی با پسوند یا پیشوند SQL همراه است. برای مثال MySQL یا Transact-SQL از برنامه‌های مدیریت بانک اطلاعاتی هستند که از SQL برای ایجاد کوئری یا پرس و جو استفاده می‌کنند.

از طرفی برنامه‌هایی نیز وجود دارند که از ساختار SQL برای پرس‌وجو استفاده نمی‌کنند. به این گونه نرم‌افزارها NO-SQL می‌گویند. برای آشنایی بیشتر با این گونه نرم‌افزارها بهتر است نوشتار پایگاه های داده SQL و NoSQL و تفاوت آنها — به زبان ساده را مطالعه کنید.

خلاصه و جمع‌بندی

در این نوشتار با چند نوع پرس و جو یا کوئری در اکسس آشنا شدید. همانطور که خواندید، در بیشتر موارد ساختاری که برای تعریف یا ایجاد انواع پرس و جوها در اکسس وجود دارد شبیه به یکدیگر بوده ولی عملکرد متفاوتی دارند. برای مثال نحوه ایجاد یک کوئری انتخاب (Select Query) یا یک کوئری حذف (Delete Query) بسیار به یکدیگر شبیه هستند ولی اولی فقط عمل انتخاب و دومی علاوه بر انتخاب، عمل حذف رکوردهای یافته شده را هم انجام می‌دهد. هر چند نحوه تعریف کوئری در اکسس بسیار ساده است ولی قدرت زیادی در تهیه گزارشات و تغییر مقادیر جدول دارند.

اگر این مطلب برای شما مفید بوده است، آموزش‌ها و مطالب زیر نیز به شما پیشنهاد می‌شوند:

• مجموعه آموزش‌های دروس علوم و مهندسی کامپیوتر
• آموزش SQL Server – مقدماتی
• مجموعه آموزش‌‌های مهارت‌های کاربردی کامپیوتر (ICDL)
• آموزش اکسس (Access) به همراه کدنویسی VBA‎‎‎‎‎
• پایگاه داده و سیستم های مدیریت اطلاعات
• رابطه در اکسس — راهنمای کاربردی
• آموزش اکسس رایگان | به زبان ساده و کامل

آرمان ری بد (+)

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

بر اساس رای 8 نفر

آیا این مطلب برای شما مفید بود؟

منبع: blog.faradars.org