Can anyone pls help??

[tehchino]

How to extract correct data??

Hi there, assume this scenario

i have 4 table:
tblAdverseRecord
tblCompliment
tblMC
tblstaff

They are are linked up by staff_no
Each staff can hold a record for adverserecord, compliment or mc in diff table..
Now i need to do up one regconition report.
Meaning i have to award the staff by their compliment for the month, however the condition is that they shouldnt have any adverse record or Mc record in for that month. And the critieria here is that all staff belong in diff team and the award isgiven to team, not by staff_no. And in order to give the award. All the members in the team must quality the condition, which is no adverse record n mc record.

My qns is, how to fulfil the criteria to check by the team and se whether all the members are qualify? Pls help thankz.

 

[Fizzio]

Do all the staff members come from the same one team? If not, to extract the information you need, it would be wise to add a team table as it will make extraction by team less painful (dentist pun intended!)
Is this the scenario?

You have to award each staff member or only the top staff member? whose whole team (including themselves) have no adv and no mc?

If all staff members are in the same team this can be done with one query by simply adding all the tables to the query, counting the compliment ID, advID and MC ID then in the criteria for advID and MCID, set it to <1 or IsNull. I think this will work.

 

[tehchino]

hi Fizzio,
thanks for your willingness to help. I had solved my problem earlier on.But to answer to your qns, the scenario is that all staff come from different team and each team consist of 5-6 members.

All the members in the same team is of same ranking and thus everyone is awarded. But if it happened that one of the member have MC /Adverse record for that month, the whole team will not get credit.

If you are interested, here is the qry i put in my [Team] criteria to get it done >
Not In (select [Team] from QryCompliment where [staff_no] in (select [staff_no] from tblAdverseRecord where Month([Date])=[forms]![frmRpt]![mth])) And Not In (select[Team] from QryCompliment where [staff_no] in(select [staff_no] from tblMCdet where [mc type]="casual" and Month([Date_To])=[forms]![frmRpt![mth]))

I will be posting another topic to do my another requirement...hope that you are able to help!

 

[Fizzio]

Glad you sorted it - always willing to (try to) help