کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم) - بهینگی

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس ۲۰۱۶ (درس دهم)

200
0
اشتراک گذاری :
اشیای Access : جدول، فرم، کوئری و گزارش - آموزش Access 2016 (قسمت دوم)

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

طراحی کوئری چند جدولی: می خواهم از چند جدول سوال بپرسم؟

درک و ساخت کوئری می تواند بسیار پیچیده باشد، اگر ایده روشنی درباره کاری که می خواهید انجام دهید و چگونگی پیداکردن پاسخ پرس و جو نداشته باشید. کوئری ساده ( از یک جدول) آنقدر ساده است که می توانید بدون اینکه آن را از قبل طراحی کنید، ایجاد کنید. اما وقتی کار به سوالات و پرس و جوهای پیچیده تر و از چند جدول می رسد بایستی در ابتدا کوئری را طراحی نمود.

طراحی کوئری

وقتی می خواهید کوئری بسازید که از چند جدول استفاده می کند، بایستی ۴ قدم زیر را فکر کرده و انجام دهید:

  1. دقیقا مشخص کنید دنبال چه چیزی هستید. اگر می توانستید سوال خود را از پایگاه داده به عنوان یک موجود زنده بپرسید، چه سوالی می پرسیدید؟ البته ساخت کوئری نسبت به پرسیدن یک سوال دشوارتر است، اما بهرحال اینکه بدانید دقیقا چه سوالی از پایگاه داده دارید، برای ساخت کوئری مناسب، ضروری است.
  2. چه نوع اطلاعاتی می خواهید کوئری نشان دهد. بعد ببینید که این اطلاعات در کدام فیلدها وجود دارند.
  3. از بین فیلدهای مشخص شده در گام ۲، دقیقا فیلدهایی که در کوئری موردنیاز است، را تعیین کنید. ضمنا تعیین کنید این فیلدها در کدام جداول هستند؟
  4. معیاری (Criteria) که اطلاعات در هر فیلد بایستی با آن مطابقت داده شود، را تعیین کنید. به سوالی که در گام یک مشخص کردید فکر کنید. کدام بخش از اطلاعات درون فیلد مورد نظر شما هستند؟

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

طرح ریزی کوئری

در ادامه می خواهیم فرایند فوق را در یک کوئری از پایگاه داده شیرینی فروشی دنبال کتیم. هر یک از مراحل فرایند طرح ریزی کوئری را با آنچه در ادامه انجام می شود، تطبیق دهید.

گام یک : تعیین دقیق سوالی که می خواهید بپرسید

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

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

به طور خلاصه، سوالی که می خواهیم کوئری به آن پاسخ دهد اینست : کدام مشتریان خارج از شهر اما در همین منطقه زندگی می کنند و قبلا از شیرینی فروشی ما سفارش داشته اند؟

گام ۲: تعیین اطلاعاتی که نیاز داریم

خب چه اطلاعاتی می خواهیم تا در لیست این گروه از مشتریان ببینیم؟ مشخصا، نام مشتریان و اطلاعات تماس (Contact information) آنها – شامال آدرس، شماره تلفن، و آدرس ایمیل – را می خواهیم. اما چگونه می خواهیم بفهمیم که آیا سفارش داشته اند یا نه؟ هر رکورد در جدول سفارش (Order) مشخص می کند که سفارش مربوط به کدام مشتری است.  بنابراین اگر ستون مربوط به شماره های کد سفارش (Order id numbers) را داشته باشیم، لیست محدود به مشتریانی خواهد شد که قبلا سفارش داشته اند.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

گام ۳: تعیین جداولی که اطلاعات موردنیاز ما را دارند.

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

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

گام ۴ : تعیین معیاری که کوئری به دنبال آن است.

وقتی برای یکی از فیلدها یک معیار (شرط) (Criteria) قرار می دهیم، در واقع فیلتری روی آن فیلد قرار می دهیم تا فقط اطلاعاتی که با معیار تعریف شده منطبق باشد، نمایش دهد. لیست فیلدهایی که در این کوئری وجود دارند، را مرور کنید. چگونه و کجا می توانیم معیاری قرار دهیم که به ما در یافتن سوال تعریف شده در گام یک کمک کند.

ما مشتریانی که در شهر ما -شهر Raleigh –  هستند را نمی خواهیم. بنابراین یک شرط می خواهیم که تمام رکوردهای جدول مشتریان را به جز آنهایی که شهرشان raleigh است نمایش دهد.

همچنین مشتریانی که خیلی دور از شهر هستند یعنی در منطقه ی غیر از منطقه ی ما هستند، را نمی خواهیم. از آنجا که کد تلفن منطقه ما با ۹۱۹ شروع می شود، بنابراین معیاری قرار می دهیم که فقط رکوردهایی را بازگرداند که فیلد شماره ی تماس آنها با ۹۱۹ شروع شود.

به این ترتیب مطمئن هستیم که برگه های تخفیف برای مشتریانی ارسال می شود که به اندازه کافی نزدیک هستند تا تشویق شوند که دوباره شیریتی سفارش دهند.

معیاری برای فیلد کد سفارش (Order ID) یا سایر فیلدها نیاز نیست، زیرا تمام سفارشاتی را می خواهیم که توسط مشتریانی صادر شده باشد که با ۲ معیار فوق الذکر منطبق باشند.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

توجه : در کوئری باید معیارها را به زبانی که اکسس متوجه شود، بنویسیم. همانطور که در تصویر فوق مشخص است، در معیاری که بایستی شماره تلفن ها با ۹۱۹ شروع شود به این صورت می نویسیم : like ( “919*”). برای آگاهی از نحوه نوشتن معیارهای کوئری به زودی در بهینگی مطلب راهنمای نوشتن معیار ( شرطهای) کوئری را منتشر خواهیم کرد. در این مطلب چندین معیار رایج در کوئری های اکسس را توضیح می دهیم. نسخه انگلیسی این سند را از اینجا دانلود کنید.

ادغام (join) جداول در کوئری

آخرین موضوعی که در طراحی کوئری بایستی بدانید، نحوه ارتباط یا اتصال یا  ادغام (join) جداول موردنیاز است.  وقتی ۲ جدول را به کوئری اکسس اضافه می کنیم، می توانیم آنها را در بخش ارتباط بین اشیا (Object Relationship pane) مشاهده کرد.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

خطی که دو جدول را به هم منصل می کند، خط اتصال یا ادغام (join line) نامیده می شود. این خطوط اتصال به صورت فلش جهت دار هستند. این فلش نشاندهنده اینست که کوئری جداول را به چه ترتیبی نگاه می کند. در جدول فوق، نوک فلش از سمت چپ به راست است، به این معنا که کوئری به داده های جدول سمت چپ نگاه کرده و سپس به اطلاعاتی در جدول سمت راست نگاه می کند که با رکوردهایی که تاکنون در جدول سمت چپ دیده است، ارتباط داشته باشد.

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

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

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

وقتی به لیست شکل فوق نگاه می کنید، متوجه چه چیزی می شوید ؟ اولا، هر سفارش در جدول سفارشات به یک مشتری در جدول مشتریان متصل است، همان مشتری که آن سفارش را داده است. البته، وقتی به جدول مشتریان نگاه می کنیم، می بینیم مشتریانی که بیش از یک سفارش دا اند به بیش از چند رکورد در جدول سفارشات متصل هستند و آن دسته از مشتریانی که سفارشی نداشته اند، به هیچ رکوردی از جدول مشتریان وصل نیستند. بنابراین، با اینکه دو جدول مشتریان و سفارشات با یکدیگر ارتباط داشته و به هم متصل هستند، اما ممکن است رکوردهایی در یک جدول داشته باشیم که با هیچ رکورد دیگری در جدول دیگر ارتباط نداشته باشد.

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

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

سپس هر رکورد از جدول سمت راست (یعنی جدول سفارشات) که دارای ارتباط با رکورد انتخاب شده از جدول سمت چپ باشد، را نیز فراخوانی می کند.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

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

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

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

سپس به جدول سمت چپ نگاه کرده و فقط مشتریانی را فراخوانی می کند که به سفارش انتخاب شده متصل هستند.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

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

خب حالا که دانستیم کدام جهت ارتباط صحیح است، آماده ساخت کوئری هستیم.

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

ایجاد کوئری بین چند جدول

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

مراحل ایجاد کوئری بین چند جدول

۱٫ دستور Query Design را از سربرگ Create در نوار ریبون اکسس انتخاب کنید.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

۲٫ در پنجره ای که باز می شود، هر جدولی که می خواهید در کوئری استفاده کنید را اضافه کنید. می تواند با نگه داشتن دکمه ctrl بیش از یک جدول را همزمان انتخاب کنید. وقتی کوئری را طراحی می کردیم، تصمیم گرفتیم که از دو جدول مشتریان و سفارشات استفاده کنیم، بنابراین این دو جدول را می افزاییم.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

 

۳٫ بعد از اینکه همه جداول مودرنیاز را اضافه کردید، دکمه Close را بزنید.

۴٫ جداول انتخاب شده در صفحه ارتباط اشیا (Object Relationship pane) ظاهر می شوند که توسط یک خط اتصال به هم وصل هستند. روی خط اتصال کلیک راست نمایید تا جهت اتصال را تغییر دهیم.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

۵٫ بعد از کلیک روی خط اتصال، پنجره مشخصات اتصال ( Join Properties) ظاهر می شود. گزینه ای که جهت صحیح اتصال را نشان دهد انتخاب کنید. در مثال ما، گزینه سوم را انتخاب می کنیم، چون اتصال راست به چپ می خواهیم.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

۶٫ در پنجره جدول، روی نام فیلدهایی کلیک کنید که می خواهید در کوئری باشند. این فیلدها به جدول طراحی (design grid) افزوده می شوند که در بخش پایین صفحه نمایش دیده می شوند. در مثال ما، اغلب فیلدهای جدول مشتریان را نیاز داریم : نام، نام خانوادگی، آدرس، شهر، ایالت، کد پستی و شماره تلفن. همچنین فیلد شماره کد (ID number) را از جدول سفارش هم می خواهیم.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

۷٫ اکنون باید معیارهای فیلتر هر فیلد را مشخص کنیم. برای اینکه سطر criteria (معیار) برای هر فیلد را انتخاب می کنیم. دو معیار برای فیلتر خروجی کوئری وجود دارند : در شهر raleigh نباشند و شماره تلفن با ۹۱۹ شروع شود. اولین معیار برای فیلد شهر و دومین معیار برای فیلد شماره تلفن تعریف خواهد شد. همانطور که قبلا گفتیم این دو معیار به این صورت بیان می شود :

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

۸٫ بعد از اینکه معیار را مشخص کردید، کوئری را با زدن دستور Run در سربرگ Design اجرا کنید.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

۹٫ نتیجه کوئری در نمای دیتاشیت (Datasheet view) نمایش داده می شود که مشابه یک حدول است. اگر بخواهید، می توانید کوئری را با کلیک بر دستور Save در نوار ابزار دسترسی سریع ذخیره کنید. در این صورت، نام کوئری از شما خواسته می شود، نام موردنظر خود را وارد کرده و بر ok کلیک کنید.

کوئری از چند جدول : آموزش فارسی و کاربردی اکسس 2016 (درس دهم)

اکنون شما روش ایجاد کوئری از چند جدول را آموختید. در درس بعدی، درباره سایر گزینه های طراحی کوئری صحبت می کنیم که باعث می شود کوئری شما بسیار قویتر شود.

چالش این درس : تمرین برای ساخت کوئری از چند جدول

  1. پایگاه داده تمرینی را باز کنید.
  2. کوئری جدید ایجاد کنید.
  3. جدول سفارشات و مشتریان را انتخاب کنید.
  4. جهت ارتباط را تغییر دهید که از راست به چپ باشد.
  5. نام، نام خانوادگی و کد پستی از جدول مشتریان را به کوئری اضافه کنید.
  6. فیلد پرداخت شده (Paid) را از جدول سفارشات به کوئری خود اضافه کنید.
  7. معیار زیر را به کوئری اضافه کنید :در فیلد کد پستی (Zip Code) ، عدد ۲۷۶۰۹ را تایپ کنید تا فقط مشتریانی که در کدپستی ۲۷۶۰۹ را برگرداند.در فیلد Paid نیز عبارت yes را تایپ کنید تا فقط مشتریانی که پرداخت کرده اند را فیلتر کند.
  8. کوئری را اجرا کنید. اگر کوئری را به طور صحیح وارد کرده باشید، بایستی نتایج شما شامل ۲۰ مشتری باشد که همه در کد پستی ۲۷۶۰۹ زندگی می کنند و هزینه سفارش را پرداخت کرده اند. در غیر اینصورت، بر دکمه آبشاری View در نوار ریبون کلیک کنید و نمای طراحی (Design view) برگردید و کوئری را بررسی کنید تا اشکال آن رفع شود.
  9. کوئری را با نام مناسب ذخیره کنید.
با گوشی اسکن کنید
1 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 5

میانگین امتیاز 5٫00 از 5 با 1 رای
Loading...

نویسندگان:

دیدگاه بگذارید

اولین نفری باشید که دیدگاه میگذارد

به من اطلاع بده
avatar
wpDiscuz