Excluding Members from a query exclude for 1 or 2

[bearcub]

I have to create directory reports for members who hold leadership positions within our organization.

Some of our members hold multiple leadership roles in either Local, state or federal positions.

Say, for example, I wanted to create a query that would exclude all members that held federal positions. I would add a criteria to exclude all those members (e.g. Like Is "Null" or <> " "). The problem with this approach is that one of these members (let's say a secretary) should be included in my directory because they hold a position in one of the other two positions (i.e. local or state).

How would I tell the query to exclude everyone except the secretary position? Do I have list each of the positions in the query critieria to exclude them (e.g. Not like "President", etc) or is there an easier way to do this?

Thank you for your help,

Michael

 

[Joe4]

It really depends on how your data tables are structured and what your data looks like.
Can you post some examples of how this data appears in your data tables currently?

 

[bearcub]

How would I copy and paste the table structure. I can provide a small sample showing how the tables are constructed and the show the problem that I'm running into.

 

[Joe4]

If I go into a table, and select a range to copy, and paste it here, it looks like this:

1	A	A	400
2	B	B	300
3	C	C	100

<caption> TableA </caption><thead>
[TH="bgcolor: #c0c0c0"] ID [/TH]
[TH="bgcolor: #c0c0c0"] Product_Code [/TH]
[TH="bgcolor: #c0c0c0"] Product_Hier [/TH]
[TH="bgcolor: #c0c0c0"] Price_Fld [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

 

[bearcub]

Here goes, on my end it looks a mess but it does copy properly in Excel, sorry

ID FullName Local State SCC CTA NEA
111 Janic President Minority at Large
222 Helen Secretary President
333 John Treasurer Representative Bargain Chair Treasurer
444 Harry President
555 Wilbur Executive Director

The people that should exluded would be Harry and Wilbur. However, John should be included in the report because is is a treasure in the Local category even though he is included in CTA and NEA.

The directory is supposed to include on Local State and SCC leadership positions. As long as they hold one of these positions they should be in the directory even though they hold CTA or NEA positions.

I got the report to work but I had specific which NEA positions to include. if they were 20 roles and I only wanted one to be included I would have to to avoid having to write 19 "Not Like" statements. Is there a way to do this?

Thank you for your help in advance.

Michael

 

[Joe4]

I really cannot make heads or tails of your data, as it is all "jumbled" together. That is because extra spacing is removed automatically.

If you are unable to post like I showed in my previous post, here is another way.
Use CODE tags around your data, which will maintain the spacing. That still might not look exactly the way you expect it, but you can play around with it to get it to look "readable".
The trick is to use the Advanced Editor, put all your data between CODE tags, use the Preview Post button to see what it will look like, and keep adding/removing spaces where necessary to make it look readable (so keep using spaces and Print Preview - and then when it looks good, submit it then).

[TABLE="width: 308"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Address1[/TD]
[TD]Address2[/TD]
[TD]City[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]1 Main Street[/TD]
[TD][/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]3 Lake Ave[/TD]
[TD]Apt 14[/TD]
[TD]Boston[/TD]
[/TR]
[TR]
[TD]William Jones[/TD]
[TD]176 1st Avenue[/TD]
[TD][/TD]
[TD]Miami[/TD]
[/TR]
</tbody>[/TABLE]

 

[bearcub]

Code
ID FullName Local State SCC CTA NEA
111 Janic President Minority at Large
222 Helen Secretary President
333 John Treasurer Representative Bargain Chair Treasurer
444 Harry President
555 Wilbur Executive Director
Code

This is the way it looks. Hopefully it comes through

Any positions that contains Local, State or SCC should be in the directory. John is is the overlap - he should be in the report but Harry and Wilbur should not be.

In the query, in the NEA criteria field I have Not like "Executive" and Not Like "President.

Thank you for the tips on posting the data. Good to know.

Michael

 

[Joe4]

Looks like you are still having issues implementing the use of Code Tags in your post.
See here for help on that: https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html

 

[bearcub]

Is this better? Still learning how to use this tool, sorry

[RANGE=cls:xl2bb-100][XR][XH=cs:8]Book1[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][/XR][XR][XH]6[/XH][XD=h:c|c:333333|cls:fx][FORMULA=ID]ID[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=FullName]FullName[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Local]Local[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=State]State[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=SCC]SCC[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=CTA]CTA[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=NEA]NEA[/FORMULA][/XD][/XR][XR][XH]7[/XH][XD=h:c|c:333333|cls:fx][FORMULA=111]111[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Janic]Janic[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=President]President[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Minority at Large]Minority at Large[/FORMULA][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]8[/XH][XD=h:c|c:333333|cls:fx][FORMULA=222]222[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Helen]Helen[/FORMULA][/XD][XD=h:c][/XD][XD=h:c|cls:fx][FORMULA=Secretary]Secretary[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=President]President[/FORMULA][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]9[/XH][XD=h:c|c:333333|cls:fx][FORMULA=333]333[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=John]John[/FORMULA][/XD][XD=h:c][/XD][XD=h:c|cls:fx][FORMULA=Treasurer]Treasurer[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Representative]Representative[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Bargain Chair]Bargain Chair[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Treasurer]Treasurer[/FORMULA][/XD][/XR][XR][XH]10[/XH][XD=h:c|c:333333|cls:fx][FORMULA=444]444[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Harry]Harry[/FORMULA][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c|cls:fx][FORMULA=President]President[/FORMULA][/XD][/XR][XR][XH]11[/XH][XD=h:c|c:333333|cls:fx][FORMULA=555]555[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=Wilbu]Wilbu[/FORMULA][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c|cls:fx][FORMULA=Executive Director]Executive Director[/FORMULA][/XD][/XR][XR][XH=cs:8][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]A6[/XD][XD]ID[/XD][/XR][XR][XD]A7[/XD][XD]111[/XD][/XR][XR][XD]A8[/XD][XD]222[/XD][/XR][XR][XD]A9[/XD][XD]333[/XD][/XR][XR][XD]A10[/XD][XD]444[/XD][/XR][XR][XD]A11[/XD][XD]555[/XD][/XR][XR][XD]B6[/XD][XD]FullName[/XD][/XR][XR][XD]B7[/XD][XD]Janic[/XD][/XR][XR][XD]B8[/XD][XD]Helen[/XD][/XR][XR][XD]B9[/XD][XD]John[/XD][/XR][XR][XD]B10[/XD][XD]Harry[/XD][/XR][XR][XD]B11[/XD][XD]Wilbu[/XD][/XR][XR][XD]C6[/XD][XD]Local[/XD][/XR][XR][XD]C7[/XD][XD]President[/XD][/XR][XR][XD]D6[/XD][XD]State[/XD][/XR][XR][XD]D7[/XD][XD]Minority at Large[/XD][/XR][XR][XD]D8[/XD][XD]Secretary[/XD][/XR][XR][XD]D9[/XD][XD]Treasurer[/XD][/XR][XR][XD]E6[/XD][XD]SCC[/XD][/XR][XR][XD]E8[/XD][XD]President[/XD][/XR][XR][XD]E9[/XD][XD]Representative[/XD][/XR][XR][XD]F6[/XD][XD]CTA[/XD][/XR][XR][XD]F9[/XD][XD]Bargain Chair[/XD][/XR][XR][XD]G6[/XD][XD]NEA[/XD][/XR][XR][XD]G9[/XD][XD]Treasurer[/XD][/XR][XR][XD]G10[/XD][XD]President[/XD][/XR][XR][XD]G11[/XD][XD]Executive Director[/XD][/XR][/RANGE]

 

[Joe4]

Yes, that is much better.

If you had wanted to see any who was in Local, State, or SCC, the Criteria line would look like this:

WHERE (Not TableName.Local="") OR (Not TableName.State="") OR (Not TableName.SCC="");

In the query builder, you would just put

Not ""

under each of those fields, but on different rows (in Criteria, conditions placed on the same row are treated as AND, where conditions placed on different rows are treated as OR).