Query on multiple wildcards

[NikToo73]

My lovely and foresightful organisation has reorganised itself in a way which is most likely entirely aimed at screwing up as much my job as possible. Hence I need to adjust some queries in Access.

We have three teams now where before there was one, all named ABC1, ABC2, etc., with sub-teams like ABC1-1, ABC1-2.

I need a query which would work somewhat like this:

IIF([SalesTeam].

 IN LIKE ('ABC1*','ABC2*,'ABC3*'), "Yes", "No").

I could do a nested IIF-statement, but this will in turn be part of a nested IIF-statement so it starts to get a bit complicated.

Any help much appreciated...

 

[plog]

First, why do you need to include the number? Wouldn't this work:

Like ('ABC*')

Second, if ABC1 is a distinct team from ABC2, then why are you querying them together? If they still need to be related, I would consider adding a new table to keep those relationships:

MasterTeams
MasterTeam_ID, MasterTeam_Name
1, ABC
2, DEF

Teams
Team_ID, ID_MasterTeam, TeamName
1, 1, ABC1
2, 1, ABC2
3, 1, ABC3
4, 2, DEF1
5, 2, DEF2

 

[NikToo73]

No, because I only want 2, 3, 4 and 5, and there's 1, 6, and 7 too... They do these things to complicate it...

It's also querying on several different columns, so it will be [SalesTeamPosition1].[TeamCode], the next one [SalesTeamPosition2].[Team Code], hence why I can't do different tables.

 

[plog]

so it will be [SalesTeamPosition1].[TeamCode], the next one [SalesTeamPosition2].[Team Code]

Numerated field names are always a sign of a poor structure. Why do you have multiple SalesTeamPosition tables? Are they the exact same structure too? I think your issue is larger than the one you posted.

 

[NikToo73]

No, it's just how the company is structured. It's a very large one, so the concerns of one lowly code-monkey isn't going to change their view... (as I'm the code-monkey...)

 

[plog]

I don't care how your company is structured, its your database that sounds improperly designed in its structure. How many SalesTeamPosition tables do you have and do they all the same structure?