You are still not clear. Your table shows JobCode and HR Location, but
your final critera set the text refers to Department number
I think the rules are
If Job Code starts with EA and HR Location RSF or US Mail then sub group
is remote
If Job code strat with EA and has any other HR Location then subgroup is
Local
If Job Code is SF0217 to SF0224 or Sf0237 or Sf0238 then subgroup is
Community
If Department Number begins with 501 followed by three characters and
ADP Source File begins with K and subGroup is not Hybrid then code as
GradonSite
UPDATE [Success Factors Employee Preview]
LEFT JOIN [ADP Group Reference] ON
[Success Factors Employee Preview].JOBCODE =
[ADP Group Reference].[Job Code]
SET [Success Factors Employee Preview].CUSTOM01 = [Form Label]
, [Success Factors Employee Preview].CUSTOM03 = [Group]
, SubGroup =
Switch(
[ADP Group Reference].[Job Code] LIKE 'EA*'
AND [ADP GROUP Reference].[Location] In ('Rsf','US Mail'),'Remote'
, [ADP Group Reference].[Job Code] LIKE 'EA*'
, 'Local'
,[ADP Group Reference].[Job Code] LIKE 'SF023[78]' Or
[ADP Group Reference].[Job Code] Between "SF0217' and 'SF0224',
'Community'
, [ADP Group Reference].[Job Code] Like '501???' and
[ADP Group Reference].[Hr Location] Like 'K*' AND
Nz([Success Factors Employee Preview].[SubGroup],"xxx") <> "Hybrid"
, "GradOnSite")
WARNING: BACK UP your data before attempting this. If it does not work
as expected there is no recovery other than a backup.
WARNING: Back up your data before attempting this.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Sorry but as you can tell I'm not a programmer by any means.
We have 2 tables and I would like to update the Subgroup column. We need to
look at [Success Factors Employee Preview].JOBCODE and [Success Factors
Employee Preview].HR Location. And if the following then do the following
Eg. There is not a specific job code for a Local vs Remote. If job code
begins
with "EA" and the location field in the ADP source file = RSF or US Mail ,
subgroup=Remote, else Local.
JobeCode HR Location Sub Group[
EA0028 CHI Local
EA0029 RSF Remote
EA0022 US Mail Remote
There are specific job codes for this group. SF0217 to SF0224, SF0237 &
SF0238, then mark as Community for subgro
JobeCode HR Location Sub Group[
SF0217 Do look at location Community
There is not a specific job code for this group. If department number
begins with 501xxx AND location code(ADP source file) begins with a "K",
check if employee is coded as HYBRID if not code as GradOnsite.
JobeCode HR Location Sub Group[
501XXXX “K†If employee code is HYBRID leave alone if not Code as INTERIM
Hope this was better please call me if you have any questions.
Bryan
John Spencer said:
UPDATE [Success Factors Employee Preview] LEFT JOIN [ADP Group Reference] ON
[Success Factors Employee Preview].JOBCODE = [ADP Group Reference].[Job Code]
SET [Success Factors Employee Preview].CUSTOM01 = [Form Label]
, [Success Factors Employee Preview].CUSTOM03 = [Group]
, SubGroup = IIF([ADP Group Reference].[Job Code] LIKE 'EA*'
AND [ADP GROUP Reference].[Location] In ('Rsf','US Mail'),'Remote','Local' )
I started to write the SQL and then I reread your posting.
I'm sorry but I'm not sure what you are doing here. Are you trying to code
subGroup field as Remote, Local, Community, Hybrid, or GradOnSite. IF so,
take a look at the switch function. If not, perhaps you can explain exactly
which fields you are attempting to set with which values.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I'm trying to write an if then statement to the the following
Here is the SQL statment for the querry I need to build on this
UPDATE [Success Factors Employee Preview] LEFT JOIN [ADP Group Reference] ON
[Success Factors Employee Preview].JOBCODE = [ADP Group Reference].[Job Code]
SET [Success Factors Employee Preview].CUSTOM01 = [Form Label], [Success
Factors Employee Preview].CUSTOM03 = [Group];
Determine Local vs Remote
There is not a specific job code for a Local vs Remote. If job code begins
with "EA" and the location field in the ADP source file = RSF or US Mail ,
subgroup=Remote, else Local.
Determine Community (College)
There are specific job codes for this group. SF0217 to SF0224, SF0237 &
SF0238, then mark as Community for subgroup
Determine GradOnsite
There is not a specific job code for this group. If department number
begins with 501xxx AND location code(ADP source file) begins with a "K",
check if employee is coded as HYBRID if not code as GradOnsite.
