Union Query

[Thomp001]

I have a union query that is trying to extract some jumbled information in a field. I have gotten the Query to extract what I want to a point but I need to take it a step further.
My problem is I need my Query to give me the wildcard of *AGR* but not anything that is "AGRI" because it needs to be a seperate catagory

The below is my union query



SELECT Mission.TRACKING_NBR, Mission.bts_reason, "AGR" AS Category
FROM Mission
WHERE (((Mission.bts_reason) Like "*AGR*" )) UNION Select Mission.TRACKING_NBR, Mission.bts_reason, "FDA" AS Category
FROM Mission
WHERE (((Mission.bts_reason) Like "*FDA*")) UNION SELECT Mission.TRACKING_NBR, Mission.bts_reason, "ATF" AS Category
FROM Mission
WHERE (((Mission.bts_reason) Like "*ATF*"));

 

[Fornatian]

try:


SELECT Mission.TRACKING_NBR, Mission.bts_reason, "AGR" AS Category
FROM Mission
WHERE (((Mission.bts_reason) Like "*AGR*"
And Not (Mission.bts_reason)="AGR1" )) UNION Select Mission.TRACKING_NBR, Mission.bts_reason, "FDA" AS Category
FROM Mission
WHERE (((Mission.bts_reason) Like "*FDA*")) UNION SELECT Mission.TRACKING_NBR, Mission.bts_reason, "ATF" AS Category
FROM Mission
WHERE (((Mission.bts_reason) Like "*ATF*"));

 

[Thomp001]

Thanks Ian,

Your soultion worked like a charm.

Is there a way on this same query to have a kind of all others query. I want the query to end with a if not found by the above Query specifics then put everything left under the catagory of "other"

thanks,
Bill

 

[Fornatian]

I'm not sure what you mean by:

I want the query to end with a if not found by the above Query specifics then put everything left under the catagory of "other"

Do you want an update query to change all records to 'other' when they don't fall into the stated categories.

A little confused.

 

[Thomp001]

Yes.. I want the union query that will show all records which do not fall into the stated categorys with the Category name of : "Other"

I figured it would be some kind of wildcard like the below but that gives me everything and not just what has not been identified already.

SELECT Mission.TRACKING_NBR, Mission.bts_reason, "Other" AS Category
FROM Mission
WHERE (((Mission.bts_reason) Like "*"


thanks,
Thomp

 

[Fornatian]

Why not use an umatched query to find all records which are not in the dataset of the current record. Then use a second union query to push the original data and 'other' data together into one dataset.

You could alternatively use a custom function to return the category without having to use a Union query at all. It would go something like :

Public Function GetCat(strMissionBtsReason as String) as String

If Instr(4, strMissionBtsReason , "AGR", 1) then
strMissionBtsReason = "AGR"

ElseIf Instr(4, strMissionBtsReason , "FDR", 1) then
strMissionBtsReason = "FDR"

ElseIf Instr(4, strMissionBtsReason , "ATF", 1) then
strMissionBtsReason = "ATF"

Else

strMissionBtsReason = "OTHER"

End if
End Function

Call this as a calculated column as follows:

Category: GetCat([Mission].[bts reason])

BTW, does your category (ATF,FDR etc) ALWAYS appear at the start, end or a certain position in the string? If so, it could be even easier.