Querying Data with variable end string 1

[scottyjohn]

Hi all,
I have an ODBC data source (ROUTING), which I query to produce two correlated columns, PRODUCT and SKILL. I then use these results to query another ODBC datasource (SOFTPHONE), which only has reference to the PRODUCT column, so that I can do a count on SKILL. The trouble is that there is a routing application which now appends, _1, _2, _3, _4, or _5 to the end of the PRODUCT field in the SOFTPHONE datasource, so therefore I never get any results from the PRODUCT entries in ROUTING. Is there any way to get this data using some kind of wildcard statement? The first query is ......

SELECT distinct[ROUTING].[PRODUCT] AS PRODUCT, [ROUTING].[SKILL_1]
FROM ROUTING

And the second is

SELECT [qryPRODUCTTOSKILL].[SKILL_1] AS ['Skill'], [qryAGENTSKILLS].[LEVEL_] AS ['Skill-Level'], Count([SOFTPHONE].[PRODUCT]) AS ['Calls']
FROM (qryAGENTSKILLS INNER JOIN SOFTPHONE ON [qryAGENTSKILLS].[EMPLOYEE_ID]=[SOFTPHONE].[EMPID]) INNER JOIN qryPRODUCTTOSKILL ON ([SOFTPHONE].[PRODUCT]=[qryPRODUCTTOSKILL].[PRODUCT]) AND ([qryAGENTSKILLS].[NAME]=[qryPRODUCTTOSKILL].[SKILL_1])
GROUP BY [qryPRODUCTTOSKILL].[SKILL_1], [qryAGENTSKILLS].[LEVEL_], [SOFTPHONE].[STARTD]
HAVING (((SOFTPHONE.STARTD)=[Date (DD/MM/YYYY)])) and qryPRODUCTTOSKILL.SKILL_1 in ('PL_CALLBACK', 'PL_CRUISE', 'PL_INET', 'PL_INET_LATES', 'PL_LATES', 'PL_MAIN');

Hope this makes sense, and thanks in advance for any help! Oh and merry xmas! :-D

John
ski_69@hotmail.com

 

[ejm8]

I may be over simplifying this, but my understanding is your skill or product field has data values like "skill", "skill_1", "skill_2", etc and you just want to group by "product". If that's what it comes down to then here's an ugly way of doing that.

For this example, let's say you are just looking at one table named "test" and it has a field named "skill" which has data like described above.

SELECT IIf(Mid([skill],Len([skill])-1,1)="_",Left([skill],Len([skill])-2),[skill]) AS skillset, Count(test.skill) AS CountOfskill
FROM test
GROUP BY IIf(Mid([skill],Len([skill])-1,1)="_",Left([skill],Len([skill])-2),[skill]), IIf(Mid([skill],Len([skill])-1,1)="_",Left([skill],Len([skill])-2),[skill]);

 

[scottyjohn]

Thanks Ejm8 that did the trick!

John
ski_69@hotmail.com