شمارش فیلدها بر اساس شرط

[anoor_h]

سلام در جدول پایین من میخوام شمارش بشه هر نفر نعداد استعلاجی وتعداد غیبیت و استحقاقی بر اساس کل ماه نمیدونم چطور کوئری بنویسم
count.PNG

[mazoolagh]

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

برای ذخبره دیتا باید به این شکل عمل کنین:
یک جدول اشخاص : کد شخص، نام، نام خانوادگی، ....
یک جدول وضعیت : کد وضعیت، وضعیت(استحقاقی/استعلاجی/غیبت)
یک جدول غیبت: تاریخ (یا در مثال شما روز ماه)، کد شخص، کد وضعیت
حالا کوئری گرفتن روی این خیلی راحته.

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

[mazoolagh]

در ضمن:

دیتاهایی از این دست رو به این شکل در جدول ذخیره نمیکنیم (به دلیل فضای خالی زیاد).
به اینها ماتریس اسپارس گفته میشه Sparse Matrix - چون دیتا در اونها پراکنده است.

در مهندسی خیلی از مسائل هست که دیتا اونها به همین شکل پراکنده است؛ مثل مدارهای الکترونیکی.
در اونجا هم برای جلوگیری از اتلاف فضا و محاسبات بیهوده (فضای خالی 0 هست و در محاسبات تاثیری نداره) فقط node list و component list میسازیم.
به این صورت که برای هر گره مشخص میکنیم چه گره هایی از چه المانی متصل هست.

در این مساله هم هر روز مشابه یک گره مدار هست و وضعیت هر روز هم مشابه لیست سرهای المانهایی هست که روی اون گره قرار دارن.

[anoor_h]

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

همونطوز که فرمودین برنامه رو پیوست می کنم برای کوئری

[mazoolagh]

البته قرار بود که جدول ها با دیتا باشه،
ولی مهم نیست چون من همیشه دیتا تست دم دست دارم یا میسازم.

من یک برنامه کامل براتون آماده کردم که پیوست میکنم.
و بعد اگر فرصت شد امروز وگرنه شاید فردا یک سری توضیحات در مورد این که چرا روش درست همین هست (در اکسس و کلا دیتابیس)،
و چرا طراحی جدول نباید به شکل تصویر پیوست پست شماره 1 باشه - مطرح میکنم.

البته تا جایی که یادم هست پرسش مشابه داشتیم.

[mazoolagh]

اول طراحی جدول ها:

کارمندان
1.png

وضعیت ها
2.png

وضعیت حضور کارمند بر اساس تاریخ
3.png

[mazoolagh]

جدول کارمندان با 100 اسم تستی پر شده:
10.png

جدول وضعیت ها:
11.png

[mazoolagh]

حالا با کد زیر جدول مرخصی ها رو پر میکنیم:

Option Compare Database

Option Explicit

Sub Fill_Leaves()

    Dim i, emp_id, stat_id, y, m, d As Long

    Dim whr As String

    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("Leaves")





    For i = 1 To 30000

        emp_id = RndX(1, 100)

        stat_id = RndX(1, 3)

        y = RndX(1400, 1402)

        m = RndX(1, 12)

        d = RndX(1, 30)

        whr = "EmployeeID=" & emp_id & " AND StatusID=" & stat_id & " AND YYYYMMDD= " & (y * 10000 + m * 100 + d)

        If DCount("ID", "Leaves", whr) = 0 Then

            With rs

                .AddNew

                !EmployeeID = emp_id

                !StatusID = stat_id

                !yyyy = y

                !mm = m

                !dd = d

                .Update

            End With

        End If

    Next





    rs.Close

    Set rs = Nothing





    MsgBox ("Leaves Table Filled!")

End Sub

Public Function RndX(MIN As Long, Max As Long) As Long

    Randomize Timer

    RndX = Int((Max - MIN + 1) * Rnd + MIN)

End Function

[mazoolagh]

بعد از اجرای Fill_Leaves ، دیتا جدول مرخصی (Leaves) شبیه زیر خواهد بود:
4.png

[mazoolagh]

حالا یک CrossTab Query مینویسیم که از روی این جدول مرخصی تعداد انواع وضعیت ها در هر روز و جمع اون ها رو محاسبه کنه:

TRANSFORM Nz(Count(Leaves.ID),0) AS CountOfID

SELECT Leaves.YYYYMMDD, Count(Leaves.ID) AS جمع

FROM Leaves INNER JOIN Status ON Leaves.StatusID = Status.StatusID

GROUP BY Leaves.YYYYMMDD

PIVOT Status.Status

6.png

[mazoolagh]

و نتیجه کار:
5.png

[mazoolagh]

برنامه پیوست:

[anoor_h]

ممنون مهندس نکات جدید قشنگی داشت فقط کاربر باید چطور دیتا جدید اضافه کنه

[amirzazadeh]

برنامه پیوست:

[mazoolagh]

شما باید یک فرم روی همین جدول leaves بسازین.

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

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

یا این که مستقیم در اکسل کدنویسی کنین.

[anoor_h]

و نتیجه کار:
5.png

تشکر دوباره من یه row دیگه اضافه کردم که نشون بده هر کارمند وضعیتش چیه درسته ؟
فقط هر ماه و هر چند ماه چطور میشه فهمید هر کس چقدر مرخصی رفته
abol.PNG

[mazoolagh]

1- شما باید فرم ثبت مرخصی ها رو روی جدول Leaves بسازین.

2- برای جستجو یا گزارشگیری هم از نمونه برنامه های تالار یا دیتابیس northwind کمک بگیرین.
و البته ویزاردهای خود اکسس هم هست.