Hi Folks,
I'm having a couple of issues with a query I'm running through MSAccess. It has in it a couple of "calculated" fields, for one of them, it all works fine, the second has some #Error data entries being returned it it.
The Query is Below:
==============
SELECT tblContractType.Type AS Contract,
tblSim.[Phone number] AS [Service Ref],
tblBlackberry.BBerry_Mobile AS [Cost Centre 1],
tblEmployeeList.[Home Job],
tblEmployeeList.[Home Task],
tbluser.AltBudget,
tbluser.AltTask,
tblEmployeeList.Name AS [Cost Centre 4],
tbluser.User AS [Cost Centre 5],
tblSite.Name AS [Address Details],
IIf([Home Job]=[AltBudget],[Home Job],IIf([altbudget]=0,[Home Job],IIf([Home Job]<>[AltBudget],[Altbudget],[Home Job]))) AS [Cost Centre 2],
IIf([Home Task]=[AltTask],[Home Task], IIf([alttask]=0,[Home Task],IIf([Home Task]<>[AltTask],[AltTask],[Home task]))) AS [Cost Centre 3]
FROM (tblSite RIGHT JOIN (tblBlackberry RIGHT JOIN (tblEmployeeList RIGHT JOIN tbluser ON tblEmployeeList.[No] = tbluser.User) ON tblBlackberry.[IMEI Number] = tbluser.Handset) ON tblSite.SiteID = tbluser.Site) RIGHT JOIN ((tblContractType RIGHT JOIN tblSim ON tblContractType.TypeID = tblSim.[Contract Type]) RIGHT JOIN tblUserSim ON tblSim.SimID = tblUserSim.SimID) ON tbluser.User = tblUserSim.UserID
WHERE (((tblContractType.Type)="BT O2"))
ORDER BY tbluser.User;
=============================
The theory behind it is that each user has a perpetual Home "Job" and "Task" Code pulled out of Payroll. If they wish to declare another then this is recorded in "AltBudget" and "AltTask" columns.
The Calculated Cost Centre 2 takes the user's perpetual one and uses it if it's the same as the Alternative, or uses the Alternative if it is different to the home, or uses the home if alternative is 0.
The Calculated Cost Centre 3 (should) work the same for the Task Codes.
I have attached a screenshot of what is happening - I am struggling to understand why it's working for the budget codes but not my task codes...
Many Thanks for any assistance!
Graham
I'm having a couple of issues with a query I'm running through MSAccess. It has in it a couple of "calculated" fields, for one of them, it all works fine, the second has some #Error data entries being returned it it.
The Query is Below:
==============
SELECT tblContractType.Type AS Contract,
tblSim.[Phone number] AS [Service Ref],
tblBlackberry.BBerry_Mobile AS [Cost Centre 1],
tblEmployeeList.[Home Job],
tblEmployeeList.[Home Task],
tbluser.AltBudget,
tbluser.AltTask,
tblEmployeeList.Name AS [Cost Centre 4],
tbluser.User AS [Cost Centre 5],
tblSite.Name AS [Address Details],
IIf([Home Job]=[AltBudget],[Home Job],IIf([altbudget]=0,[Home Job],IIf([Home Job]<>[AltBudget],[Altbudget],[Home Job]))) AS [Cost Centre 2],
IIf([Home Task]=[AltTask],[Home Task], IIf([alttask]=0,[Home Task],IIf([Home Task]<>[AltTask],[AltTask],[Home task]))) AS [Cost Centre 3]
FROM (tblSite RIGHT JOIN (tblBlackberry RIGHT JOIN (tblEmployeeList RIGHT JOIN tbluser ON tblEmployeeList.[No] = tbluser.User) ON tblBlackberry.[IMEI Number] = tbluser.Handset) ON tblSite.SiteID = tbluser.Site) RIGHT JOIN ((tblContractType RIGHT JOIN tblSim ON tblContractType.TypeID = tblSim.[Contract Type]) RIGHT JOIN tblUserSim ON tblSim.SimID = tblUserSim.SimID) ON tbluser.User = tblUserSim.UserID
WHERE (((tblContractType.Type)="BT O2"))
ORDER BY tbluser.User;
=============================
The theory behind it is that each user has a perpetual Home "Job" and "Task" Code pulled out of Payroll. If they wish to declare another then this is recorded in "AltBudget" and "AltTask" columns.
The Calculated Cost Centre 2 takes the user's perpetual one and uses it if it's the same as the Alternative, or uses the Alternative if it is different to the home, or uses the home if alternative is 0.
The Calculated Cost Centre 3 (should) work the same for the Task Codes.
I have attached a screenshot of what is happening - I am struggling to understand why it's working for the budget codes but not my task codes...
Many Thanks for any assistance!
Graham