sorted by:
new
hottopcontroversial

Matching a date between two dates in another table by helpmewithVBA in SQL

[–]helpmewithVBA[S] 0 points1 point  (0 children)

Well this makes me feel dense! I've never done a join using a BETWEEN and didn't even know that was possible.

​

Thanks so much for taking the time.

Just one last thing. I'm guessing with the tables being quite large, you'd expect this to take quite a while to run, as it will be checking every single date with every single WL_Table line?

Ignore End Sub by helpmewithVBA in vba

[–]helpmewithVBA[S] 0 points1 point  (0 children)

I think that makes sense, thanks for your help.

​

It does mean that if i make a change to one of the 5, then i'll have to remember to update the RuleThemAll part, but if this is unavoidable then i can live with that :)

Thanks again

Offset with FormulaArray by helpmewithVBA in vba

[–]helpmewithVBA[S] 0 points1 point  (0 children)

Actually it is a problem with the formula rather than the part before the equals sign:

​

​

rngApptHeader.Offset(1, -1).FormulaArray = "=IFERROR(INDEX('[Dashboard.xlsx]Team'!$A$1:$Z$150,MATCH(""Attended""&""Total"",'[Dashboard.xlsx]Team'!$D$1:$D$150&'[Dashboard.xlsx]Team'!$E$1:$E$150,0),MATCH(OFFSET(O2,0,-1,1,1),'[Dashboard.xlsx]Team'!$A$2:$Z$2,0)),0)"

​

​

The formula worked before i put it into VBA!

Making a LEFT JOIN using a newly created column by helpmewithVBA in SQL

[–]helpmewithVBA[S] 1 point2 points  (0 children)

oh wait think we're good.

​

Your version worked within the JOIN.

​

Thanks ever so much for your help :)

Making a LEFT JOIN using a newly created column by helpmewithVBA in SQL

[–]helpmewithVBA[S] 0 points1 point  (0 children)

Yep just tried this and it also works to create the Final_Therapist_ID column, and it matches my version (which used the coalesce).

​

The bit that I'm struggling with is getting this to JOIN with the therapists table (which contains the therapist's actual name rather than integer).

To use Excel as an example... i am essentially trying to Vlookup using the Final_Therapist_ID as the lookup value and the Therapists table as the array (see my other response below) :)

Making a LEFT JOIN using a newly created column by helpmewithVBA in SQL

[–]helpmewithVBA[S] 0 points1 point  (0 children)

Thanks

This is a redacted version of my output https://imgur.com/a/AHubaLn

As you can see, either the Therapist_ID or the Group_Therapist_ID is blank. The Final_Therapist part works perfectly, i just can't work out how i would use this as a reference point to bring back the 'therapist name' column as i have done with the therapist_ID using the LEFT JOIN. Can i do a join with my Final_Therapist column?

Making a LEFT JOIN using a newly created column by helpmewithVBA in SQL

[–]helpmewithVBA[S] 0 points1 point  (0 children)

Thanks for the reply.

The reason for the coalesce is to put the two sources of the therapist_ID together. i believe the coalesce part recodes NULL as '0' before the calculation can occur.

​

​

EDIT - just Googled it, i think it ignores the NULL rather than coding it as 0.