Task Query

[mba_110]

Hi everyone i am trying to build a task table where i can able to record the events like, birthday of employee, Life insurance due date, Medical insurance due date, vehicle insurance due date, Passport expiry date, ID expiry date, Payroll preparation date, so on many...

I have created tblTasks

ID (PK) Numeric
EmpID (FK) to tblEmployees (PK) Numeric (This task is related to Emp)
Taskdescription
DaysBeforeReminder
AssignTo numeric field (This will assign the task to EmpID)
DueDate


Now what i need is all that events, occur for employees data stored in different tables (Like insurance, passport, ID etc) should be recorded as occurrence in tblTasks on every due date or renewal time.

The Next step i will explain you after i accomplish above, otherwise it will complicate the things here.

I need your easy (to understand) explanation/help and guidance how i can do it by query or by anything else?

 

[JHB]

I think it is best to give all the information at once, (of course in a clear and understandable way), otherwise it turns out that a solution cannot be used anyway.
But to solve exactly the problem you are writing about right now you can use a Union Query.
If you are not familiar with union queries, Google it: then you'll get lots of examples.

 

[mba_110]

I have manage to do following SQL for my union query, but results are partially correct, it is still asking to enter parameter values for all tables.

SELECT tblEmployees, [EmpID], [Fullname],[DOB]
FROM tblEmployees
UNION
SELECT tblEmploymentContracts, [ContractID], [Status], [HireDate]
FROM tblEmploymentContracts
UNION
SELECT tblLifeInsurance, [LifeInsurance_ID],[InsuranceType], [EndDate]
FROM tblLifeInsurance
UNION
SELECT tblMedical, [MembershipNo],[Type],[CoverEnd]
FROM tblMedical
UNION
SELECT tblPassports, [PassportNumber],[ExpiryDate],[Nationality]
FROM tblPassports
UNION
SELECT tblPayroll, [EmpID],[PayMonth],[PayDate]
FROM tblPayroll
UNION
SELECT tblResidentID,[IqamaNo], [HijriExpiry], [ExpiryDate]
FROM tblResidentID
UNION
SELECT tblSocialInsurance,[GosiNumber], [ResidentID], [EndDate]
FROM tblSocialInsurance
UNION
SELECT tblVehicleInsurance, [PlateNo], [InsuranceType], [EndDate]
FROM tblVehicleInsurance
UNION
SELECT tblVisas, [EmpID],[Passport],[ExpiryDate]
FROM tblVisas;

May be i forgot to mention (table Name.) before all fields or (table name.ID) in beginning of new table fields.

Please correct the missing area.

thanks.

 

[isladogs]

Remove the table name and comma after each SELECT
You can if you wish use table name followed by . before each field name but it's unnecessary

Whether this is going to give the info you need is another matter.

 

[mba_110]

it is not giving me the required result, for sure and same time it is only showing the first 3 three fields of tblEmployees only.

I wonder that i make any mistake here in design, or procedure it self is not selected correctly.

 

[isladogs]

It's only showing the first three fields as that's all you've entered in your query.
Your design may indeed be flawed if you need to include all these tables in a union query

 

[mba_110]

Please i need solution to the issue, and its missing break points.

I am using union query, based on above recommendation since i dont know it will accomplish my task or not.

 

[MajP]

I am with JHB, the more you can give up front the better because I read this very different. I have no idea how that union query does this

Now what i need is all that events, occur for employees data stored in different tables (Like insurance, passport, ID etc) should be recorded as occurrence in tblTasks on every due date or renewal time

If you were going to use a union query to then build an insert query to create tasks I assume it would need the emp_ID, TaskDescription, and TaskDueDate

Something like

SELECT EmpID_Fk as EmpID, EndDate as DueDate, "Update Insurance" as TaskDescription
FROM tblLifeInsurance
UNION
SELECT EmpID_Fk as EmpID, CoverEnd as DueDate, "Update Medical" as TaskDescription
FROM tblMedical

(Note: repeated aliases for clarity and not required)

I would likely lean to writing some code to be more flexible, because I guess there may be some rules and other data for each task.
I would assume in the tables for example you have Passport. That would have an employees passport info and a foreign key to that employee. I would assume when they get a new passport you update the information for that passport and update the Expiration date. So now you have to correct a new task. I would think you do this in code.
1) Check the task table for that emp and that task type (I assume that is your task description "Update Passport"). You need a task completed field.
2) For the existing task see if due date is prior to current expiration date.
3) If so mark that task as complete and create new task, If the date is the same then do nothing.
4) If the old task is marked as complete create a new task.
EmpID = EmpID from table passport
Description = "Update Passport"
DueDate = may be some specific formula based on expiration date and rules for each table
DaysBeforeReminder = some rule

So that is how you would do future updates. To load the task table based on existing information in the tables
1)Loop each table and each record in each table
2)Based on the table type create or update records as above.

The union query may be a start to preload existing tasks. But if you are going to do future updates I think you will need code to close out a task or create new task. I also assume you may have rules on reminders and due dates based on specific data.

If going the union query route, you are going to build the union query so that you can then do an insert query into table tasks. So you need the empID from those tables. I do not understand the rest of the fields you are using. I thought you want to use the union to create tasks. I would think it only needs something like

UNION
SELECT [PassportNumber],[ExpiryDate],[Nationality]
FROM tblPassports

 

[mba_110]

I have followed your code but it is not producing the result in EmpID for all tables only tblEmployees which is mentioned in SELECT first are showing the result, however taskdescription and task duedate and TaskCategory are working fine for all.

SELECT EmpID, DOB as DueDate, "Employee Birthdays" as TaskDescription, "Birthdays" as TaskCatagory
FROM tblEmployees
UNION
SELECT EmpID_FK as EmpID, HireDate as DueDate, "Work Anniversary" as TaskDescription,"Employment Contract" as TaskCatagory
FROM tblEmploymentContracts
UNION
SELECT EmpID_Fk as EmpID, EndDate as DueDate, "Update Life Insurance" as TaskDescription,"Life Insurance" as TaskCatagory
FROM tblLifeInsurance
UNION
SELECT EmpID_Fk as EmpID, CoverEnd as DueDate, "Update Medical Insurance" as TaskDescription,"Medical Insurance" as TaskCatagory
FROM tblMedical
UNION
SELECT EmpID_Fk as EmpID, ExpiryDate as DueDate, "Update Passport Details" as TaskDescription,"Passport" as TaskCatagory
FROM tblPassports
UNION
SELECT EmpID_Fk as EmpID, PayEndDate as DueDate, "Prepare Payroll" as TaskDescription,"Payroll" as TaskCatagory
FROM tblPayroll
UNION
SELECT EmpID_Fk as EmpID, ExpiryDate as DueDate, "Update Resident ID" as TaskDescription,"Resident ID" as TaskCatagory
FROM tblResidentID
UNION
SELECT EmpID_Fk as EmpID, EndDate as DueDate, "Update GOSI" as TaskDescription,"GOSI" as TaskCatagory
FROM tblSocialInsurance
UNION
SELECT EmpID_Fk as EmpID, EndDate as DueDate, "Update Leave Return form" as TaskDescription,"Staff Leave" as TaskCatagory
FROM tblStaffLeaves
UNION
SELECT EmpID_Fk as EmpID, NextDueDate as DueDate, "Prepare Return form" as TaskDescription,"Tax" as TaskCatagory
FROM tblTax
UNION
SELECT EmpID_Fk as EmpID, CoverageEnd as DueDate, "Update Travel Insurance" as TaskDescription,"Travel Insurance" as TaskCatagory
FROM tblTravelInsurance
UNION
SELECT EmpID_Fk as EmpID, EndDate as DueDate, "Update Vehicle Insurance" as TaskDescription,"Vehicle Insurance" as TaskCatagory
FROM tblVehicleInsurance
UNION
SELECT EmpID_Fk as EmpID, ExpiryDate as DueDate, "Update Visa" as TaskDescription,"Visa" as TaskCatagory
FROM tblVisas

My next step after we fix the above I need to make an UPDATE statement in this UNION query to update the tblTasks data, I have same fields in tbltasks and also 3 more fields for daysbeforeReminder, AssignTaskTo and Taskstatus while we implement the UPDATE statement I have to check the taskStatus is empty and not mentioned “Completed” and it is not duplicate the tasks. The Filter will be (EmpID,TaskCatagory,DueDate) for example if Employee renew his Medical insurance on so and so date last year and again he have renewal for this year it should be consider as correct (as DueDate filter will be change) otherwise if above filters not change and remain same it should not allow duplicates, this is what I understand any other option you can mention.


After update the above to tblTasks Admin will assign the task to preferred employees in TaskAssignTo (field mention on tblTasks) with DaysBeforeReminder and after that when employee login to database he will see the assign task on his list box on dashboard based on duedate and DaysbeforeReminder assign by admin may be 10 days before or a 15 from their he will mark it or type it whatever and tblTasks will be updated with “Completed” in TaskStatus field if assigned employee completed the task.


I really need your kind help and atleast a piece of code sample to achieve this, or any other easy way you can mention.

 

[JHB]

Post your database with some sample data, zip it.

 

[MajP]

I have followed your code but it is not producing the result in EmpID

The examples were notional. I assume in everyone of the tables, there is a foreign key back to the employee table. I might have gotten lucky but "EmpID_Fk" was a notional name for that key in those tables. I am guessing that is not what yours really is. Just like the different date fields you need to use the real name of the employee ID foreign key.

 

[mba_110]

I have managed to get my SQL for Union query, and its working like rocket.

Union Query details are same to my tbltask one field DOB is more but i limited for UNION query use only.

SELECT tblEmployees.EmpID, tblEmployees.DOB, DateSerial(Year(Date()),Month([DOB]),Day([DOB])) AS DueDate, "Greet Employee Birthday" AS TaskDescription, "Employee Birthday" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-1) AS ReminderDate
FROM tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblEmployees.DOB)>0))
UNION ALL
SELECT tblEmploymentContracts.EmployeeID, tblEmploymentContracts.HireDate, DateSerial(Year(Date()),Month([HireDate]),Day([HireDate])) AS DueDate, "Congratulate Work Anniversary" AS TaskDescription, "Work Anniversary" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-1) AS ReminderDate
FROM tblEmploymentContracts
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblEmploymentContracts.HireDate)>0))
UNION ALL
SELECT tblLifeInsurance.EmpID, tblLifeInsurance.EndDate, DateSerial(Year(Date()),Month([EndDate]),Day([EndDate])) AS DueDate, "Life Insurance Renewal" AS TaskDescription, "Life Insurance" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-30) AS ReminderDate
FROM tblEmploymentContracts INNER JOIN tblLifeInsurance ON tblEmploymentContracts.EmployeeID = tblLifeInsurance.LifeInsurance_ID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblLifeInsurance.EndDate)>Date()))
UNION ALL
SELECT tblMedical.EmpID, tblMedical.CoverEnd, DateSerial(Year(Date()),Month([CoverEnd]),Day([CoverEnd])) AS DueDate, "Medical Insurance Renewal" AS TaskDescription, "Medical Insurance" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-15) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblMedical ON tblEmployees.EmpID = tblMedical.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblMedical.CoverEnd)>Date()))
UNION ALL 
SELECT tblPassports.EmpID, tblPassports.ExpiryDate, DateSerial(Year(Date()),Month([ExpiryDate]),Day([ExpiryDate])) AS DueDate, "Passport Renewal" AS TaskDescription, "Passport" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-90) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblPassports ON tblEmployees.EmpID = tblPassports.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblPassports.ExpiryDate)>Date()))
UNION ALL
SELECT tblPayroll.EmpID, tblPayroll.PayEndDate, DateSerial(Year(Date()),Month([PayEndDate]),Day([PayEndDate])) AS DueDate, "Payroll Preparation" AS TaskDescription, "Payroll" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-6) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblPayroll ON tblEmployees.EmpID = tblPayroll.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblPayroll.PayEndDate)>Date()))
UNION ALL
SELECT tblResidentID.EmpID, tblResidentID.ExpiryDate, DateSerial(Year(Date()),Month([ExpiryDate]),Day([ExpiryDate])) AS DueDate, "Resident ID Renewal" AS TaskDescription, "Resident ID" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-30) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblResidentID.ExpiryDate)>Date()))
UNION ALL SELECT tblSocialinsurance.EmpID, tblSocialinsurance.EndDate, DateSerial(Year(Date()),Month([EndDate]),Day([EndDate])) AS DueDate, "Social Insurance Renewal" AS TaskDescription, "Social Insurance" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-8) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblSocialinsurance ON tblEmployees.EmpID = tblSocialinsurance.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblSocialinsurance.EndDate)>Date()))
UNION ALL
SELECT tblTax.EmpID, tblTax.PeriodTo, DateSerial(Year(Date()),Month([PeriodTo]),Day([PeriodTo])) AS DueDate, "Tax Payment" AS TaskDescription, "Tax" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-30) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblTax ON tblEmployees.EmpID = tblTax.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblTax.PeriodTo)>Date()))
UNION ALL
SELECT tblTraining.EmpID, tblTraining.EndDate, DateSerial(Year(Date()),Month([EndDate]),Day([EndDate])) AS DueDate, "Training Status Update" AS TaskDescription, "Employee Training" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-3) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblTraining ON tblEmployees.EmpID = tblTraining.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblTraining.EndDate)>Date()))
UNION ALL
SELECT tblTravelInsurance.EmpID, tblTravelInsurance.CoverageEnd, DateSerial(Year(Date()),Month([CoverageEnd]),Day([CoverageEnd])) AS DueDate, "Travel Insurance Status Update" AS TaskDescription, "Travel Insurance" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-4) AS ReminderDate
FROM (tblEmployees INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblTravelInsurance ON tblEmployees.EmpID = tblTravelInsurance.EmpID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblTravelInsurance.CoverageEnd)>Date()))
UNION ALL SELECT tblVisas.EmpID, tblVisas.ExpiryDate, DateSerial(Year(Date()),Month([ExpiryDate]),Day([ExpiryDate])) AS DueDate, "Visa Status Update" AS TaskDescription, "Visa" AS TaskCatagory, IIf([DueDate]="",0,[DueDate]-5) AS ReminderDate
FROM (tblEmployees INNER JOIN tblVisas ON tblEmployees.EmpID = tblVisas.EmpID) INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID
WHERE (([tblEmploymentContracts]![ContractStatus]="Active") AND ((tblVisas.ExpiryDate)>Date()));

now the next hurdle is appending it to my tbltask, each time i append it recreate the new rows and duplicate the information, however i try doing it with update query but its giving me "unupdateable" error message.

I need all the option that i can update the task to my tbltask without duplicating the rows.

my tbltask fields are as follows, these to be updated whenever i run the query or add any information to any of above tables record.

ID
EmpID
Taskdescription
TaskCatagory
DueDate
ReminderDate

I am sorry but its take too long to take out all my database items to post as trim version, once i get a chance i will do it but let try this first.

 

[MajP]

You can create a composite Index your table on EmpID, TaskDescription, DueDate to ensure the three of them together are unique. Do you update a given Task for an employing when there is a new due date or do you create a new tasks and mark the old one closed?.

 

[mba_110]

All task is generated itself from entry of records in table, i have another columns for assignment and status update for the task, and their is option also to include General task by admin, these all task will be assign to employees by admin from his user access in database.


This query and table is to gather the all information of task with due date and then admin will decide to whom he will through the tasks, i did not make it directly to users because i want admin should do some work to monitor until these completed.

Each task have status and statusdate in tbltask to mark it as done, whatever is not done will be as open status and what is done will be completed status and if anything do not have status than it means Admin have to assign it to employee this part will come next but i need now to clear the hurdle of appending it to table.

One more thing do you mean i should use append query and then create composite index well this is new for me but i will do it just guide me more about its procedure and if possible provide piece of example.

 

[MajP]

If you have a unique index on a table (example EmpID,TaskID) and you try to insert into that table a new record with the same EmpID, TaskID it will fail to insert. If you do this in code you do not get a message those duplicate records are just not added. You can also build an insert query that only inserts record not in the destination table. This may be a little more complicated because you have to ensure you have an updateable query.

 

[mba_110]

I have fields as i mentioned.

ID (This is primiry key with autonumber)
EmpID (is number field but it will be duplicate because one
employee have many tasks related to him)

Taskdescription (Task description could be duplicate as its repeat and all
related to employees)
TaskCatagory (Task Catagory is like catagory which will repeat with all
related employees tasks)
DueDate (DueDate can be unique but not sure that other records
will not have the same date)
ReminderDate (Same issue with Duedate can be duplicate with other
tasks)

I dont know how to fix it, do you have anything to mention i have not used indexes before.

thanks

 

[MajP]

Here is how to create a composite index
http://www.geeksengine.com/article/composite-unique-index-access.html

In your case EMPID, TaskDescription, and DueDate
A specific employee can not have the same task due on the same day.

 

[MajP]

I think a better solution is to build unique PKs for each task. Lets assume each of your tables in the union query has some PK field. Lets Assume that in every table it is called ID.

You can then make a unique ID for every task and insert that into your task table. Then you could get rid of the autonumber field. Something like

SELECT "EmploymentContract" & ID as TaskID, EmpID_FK as EmpID, HireDate as DueDate, "Work Anniversary" as TaskDescription,"Employment Contract" as TaskCatagory
FROM tblEmploymentContracts
UNION
SELECT "LifeInsurance" & ID as TaskID, EmpID_Fk as EmpID, EndDate as DueDate, "Update Life Insurance" as TaskDescription,"Life Insurance" as TaskCatagory
FROM tblLifeInsurance

Now you can index on TaskID.

 

[mba_110]

I think that's not possible, because when I use all tables PK as taskID in my tblTasks than it won't be unique anymore because all tables PK is having the number and autonumbers fields which will still be duplicates in TaskID however on Sunday I will try composite indexes to see how table behave once I append the duplicates and what data is stored after indexes.

But I want you to understand here the problem first.

I have like 12 tables in union query ready with all necessary fields and evaluation in SQL and it does produce the correct result or desired result i want so, that means we have data ready with us, but the problem I am facing is the result (record rows) generared by union query is being append to tbltaks with QryTaskAppend once i run it, firstly like 20 records appended to tbltask but listen carefully when I run second time without any changes it still append the same 20 records which is already exist in tbltask and it was appended by this query just a while ago.

In this process I need append should check the records exist in QryTaskCollection are exist in tbltask or no if any of record doesn't match with existing records in tbltask then it append that only, if records match with Qrytascollection with tbltask than it should not duplicate that and leave it as it is.

I know this can be done by code but it could be a intense code writing which I don't know.

 

[isladogs]

You need to create an unmatched append query similar to this:

To do so add the source table/query & the destination table to a new select query
Link using an outer join on suitable fields where the arrow is pointing at the destination table
Add the source fields and add one or more 'check' fields that can't be duplicated from the destination table. Add filter criteria 'Is Null'
Run the query to test how many new records exist.
Now change it to an append query leaving the Append to field name blank where you have Is Null
Run it - the records will be appended.
Run it again - no records are added as this would cause duplicates