Reference another table's data to use in a append query

[dvhornor]

I'm struggling with - what I think is a simple problem.
Below is a function to append data to another table. One line of the code - IIf(tblSkillData!Skill=1,'M&M','Other')AS SkillBucketDetail - is an
example of what needs to happen with over 100 skills. I am trying to avoid writing an if statement 100 times.

I have a table set up labeled "tblBuckets". In that table I have data that I
would like to reference to complete the SQL statement. The Table fields are the following: Skill / SkillBucket
Example: Skill / SkillBucket
1 M&M
2 CORE
3 HD
4 BILLING
etc.

I need help to write an array, case, or for-next statement (or something else) to help simply this code?

Public Function BuildSQL(dDate As Date) As String
Dim sSQL As String
'This function is used to build the appropriate append query

sSQL = "INSERT INTO tblSkillBucketDetail (SkillBucketDetail, Skill, SkillDate,"
sSQL = sSQL & "ASA, NCO, NCH, ATT, ACW, AHD, ABNcalls, ABNpcnt, ABNtime,"
sSQL = sSQL & "ExtOutCalls, CSL ) SELECT IIf(tblSkillData!Skill=1,'M&M','Other')AS SkillBucketDetail,"
sSQL = sSQL & "tblSkillData.Skill , tblSkillData.SkillDate, tblSkillData.ASA,"
sSQL = sSQL & "tblSkillData.NCO, tblSkillData.NCH, tblSkillData.ATT,"
sSQL = sSQL & "tblSkillData.ACW, tblSkillData.AHD, tblSkillData.ABNcalls,"
sSQL = sSQL & "tblSkillData.ABNpcnt, tblSkillData.ABNtime, tblSkillData.ExtOutCalls,"
sSQL = sSQL & "tblSkillData.CSL FROM tblSkillData WHERE (((tblSkillData.SkillDate)>#" & dDate & "#))"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

End Function

 

[bat17]

Now that you have a lookup table for your skillBuckets you can use a simple join to give you their proper names rather than type. If you create this in a query first, you may find it easier. Just drop in the fields that you want to show, add some criteria so that you can check that you are getting the results that you expect then go to SQL view and copy the code from there. I find it easier to do this and then modify the SQL to suit the VBA than try to build from scratch. Depending on what you are doing with the output you may be better off just using the type anyway as you can always use the lookup table to display the friendly name when you use the data. This will make updates easier as you can easily add categories by adding to the table or if some one decides that "M&M" should really be "M and M" you just have to tweak it one place and it will show up everywhere.

HTH

Peter

 

[dvhornor]

Peter -
Thank you! This was the direction I needed. I set it up and it works.

Dave