Solved Average Date Setting

[smtazulislam]

why can't you use examples that relate to your data - it might make more sense to those trying to help

you have 696 employees so the average is 58.

That is my question, How can giving result 58 -/+ With calculate from ExpireDateEn field (present date) to ADDED (3M OR 6M OR 9M OR 12M) equal all employees.

So are you saying you have 46 in Jan, so bring forward 12 employees from a later month and in Feb you have 63 so push forward 5 employees to a later month and in March push forward 142 etc If so, on what basis do you choose the employees to move. I really don't see how any of this can work without knowing the renewal period.

Employee 40 has a renewal date of 4/1/2024 so is presumably out of scope for 2023 a count of employees with renewals for Feb 2023 is 57, not 63 as you show above and Jan 2023 shows 14 not 46. So presumably 32 of those have already been assigned a later renewal date.

And what basis are you going to use to 'move' a renewal date - bring forward so there is an overlap, push back in which case the employee is presumably not able to work without a valid resident card? Or perhaps there is a period of grace where a there can be a delay, in which case how much

don't count any year overlap. because we have present date and renew from this date.

 

[smtazulislam]

Because what you're posting is not English. You're substituting English words in your native language and think it's fluent English and expect others to understand it.

English is not my tongue languages. I knew, I am not fluent English

We are on post 20 and nobody can understand what you want.
So if there's a problem, it's on your side. not experts' side.

You don't understood post 20 because you're not read previous posted. read Post 10 & 14.

If you need help, go to Google translate, type what you want in your native language (Arabic?), translate it to English, Copy the result and post it here.

Sorry, I can't write ARABIC. just read 55 % only..

 

[arnelgp]

you should probaly Add another field in your table to show the Validity (in months) of each Workers Iqama
so you can correctly calculate if the employee Iqama will expires withing 3 month, 6 month, 1 year or 2 years.

you enter When the Iqama was issued + Validity in months.

 

[smtazulislam]

you should probaly Add another field in your table to show the Validity (in months) of each Workers Iqama
so you can correctly calculate if the employee Iqama will expires withing 3 month, 6 month, 1 year or 2 years.

you enter When the Iqama was issued + Validity in months.

Okay. I added the field of MONTH. How to make average every month same quantity ?

 

[CJ_London]

Questions are just not being answered clearly. I now understand the principle required, just not what the rules are.

You have someone who's expiry date is January this year. We are now in February. So the implication is those in January are now working without a Iqama. You have not answered if this is OK or not. Based on what you are saying, the earliest they can now be renewed is in 3 months - i..e. April.

This does not make sense.

You might as well use Josef's suggestionin post #19

 

[arnelgp]

i added RenewalDate.
if you already Renenewed a persons Iqama, you should put the actual date of the renewal to this field.
right now, i only save the on this field, ExpiredDateEn + ExpiresMonths.

query3 shows the crosstab query.

 

[smtazulislam]

i added RenewalDate.
if you already Renenewed a persons Iqama, you should put the actual date of the renewal to this field.
right now, i only save the on this field, ExpiredDateEn + ExpiresMonths.

query3 shows the crosstab query.

This is missing month is MAR - MAY - JULY - SEPTEMBER - NOVEMBER.
and there not same quantity every months.

I need every months average same quantity divide by total Employees.

Note : if its 1 or 2 pcs quantity (- / +) no problem.

 

[ebs17]

You want a total average, here you get one. What sense this should bring in the following use, I don't know.

Due to the data situation months are missing:
2020-01 to 2022-12
2023-11
2024-03 to 2587-11

If you add them, for example by OUTER JOIN with a complete month table, the total average changes dramatically.

However, the periods used do not correspond at all to extensions of a few months.

SELECT
   E.AnyMonth,
   E.CountEmp,
   A.AVG_overAllMonth
FROM
   (
      SELECT
         Format(ExpiredDateEn, "yyyy-mm") AS AnyMonth,
         COUNT(*) AS CountEmp
      FROM
         tblEmployee
      GROUP BY
         Format(ExpiredDateEn, "yyyy-mm")
   ) AS E,
   (
      SELECT
         E.CountAllEmp \ M.CountMonth AS AVG_overAllMonth
      FROM
         (
            SELECT
               COUNT(*) AS CountAllEmp
            FROM
               tblEmployee
         ) AS E,
         (
            SELECT
               COUNT(*) AS CountMonth
            FROM
               (
                  SELECT DISTINCT
                     Format(ExpiredDateEn, "yyyy-mm")
                  FROM
                     tblEmployee
               ) AS MX) AS M) AS A

 

[arnelgp]

Query2 is Count not Average.

 

[smtazulislam]

You want a total average, here you get one. What sense this should bring in the following use, I don't know.

Due to the data situation months are missing:
2020-01 to 2022-12
2023-11
2024-03 to 2587-11

If you add them, for example by OUTER JOIN with a complete month table, the total average changes dramatically.

However, the periods used do not correspond at all to extensions of a few months.

SELECT
   E.AnyMonth,
   E.CountEmp,
   A.AVG_overAllMonth
FROM
   (
      SELECT
         Format(ExpiredDateEn, "yyyy-mm") AS AnyMonth,
         COUNT(*) AS CountEmp
      FROM
         tblEmployee
      GROUP BY
         Format(ExpiredDateEn, "yyyy-mm")
   ) AS E,
   (
      SELECT
         E.CountAllEmp \ M.CountMonth AS AVG_overAllMonth
      FROM
         (
            SELECT
               COUNT(*) AS CountAllEmp
            FROM
               tblEmployee
         ) AS E,
         (
            SELECT
               COUNT(*) AS CountMonth
            FROM
               (
                  SELECT DISTINCT
                     Format(ExpiredDateEn, "yyyy-mm")
                  FROM
                     tblEmployee
               ) AS MX) AS M) AS A

Sorry not like that,

You make it a column result with Average Quantity. But you see the Employee Quantity is same as before.

now your calculation average 53 Quantity each month.

Our conditions is PresentDate (any ExpiredDateEn) to Add anyone (3 or 6 or 9 or 12) months for the "NewRenewDate"
Example:
You have to take anyone-
ExpiredDateEn + 3 months Or
ExpiredDateEn + 6 months Or
ExpiredDateEn + 9 months Or
ExpiredDateEn + 12 months
Result is "NewRenewDate"

this NewRenewDate should have average every months same quantity (as you found it 53 right now). our total Employee divide 12 Months



Now tell me, did you understand what I required?

 

[smtazulislam]

Hello, it solve by ChatGPT .
Now average quantity 40 - 77 pc's

 

[CJ_London]

Congrats! first time I've seen chatCPT solve a real world problem - what sql did it come up with?

 

[smtazulislam]

Congrats! first time I've seen chatCPT solve a real world problem - what sql did it come up with?

I am sorry for delay response. We are off-day FRI & SAT day.

Private Sub btnAverageMonths_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim dtExpired As Date
Dim dtNewExpired As Date
Dim intMonth As Integer
Dim intCount As Integer
Dim intAvg As Integer

Set db = CurrentDb()
strSQL = "SELECT EmployeeID, ExpiredDateEn, newExpiredDateEn FROM tblEmployee"
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
Do Until rs.EOF
    dtExpired = rs!ExpiredDateEn
    'Generate random number between 0 and 3 and multiply by 3 to get 0, 3, 6 or 9
    intMonth = Int(Rnd() * 4) * 3
    dtNewExpired = DateAdd("m", intMonth, dtExpired)
    rs.Edit
    rs!newExpiredDateEn = dtNewExpired
    rs.Update
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

'Calculate average count for each month
intCount = DCount("*", "tblEmployee", "newExpiredDateEn Is Not Null")
intAvg = Int(intCount / 12)

MsgBox "New dates added successfully." & vbCrLf & "Total count: " & intCount & vbCrLf & "Average count per month: " & intAvg, vbInformation

End Sub

 

[CJ_London]

interesting, I don't quite see the logic that results in a relatively even spread - if I get time later, I'll take a closer look. Thanks for posting the code