Question regarding eliminating a value for one column but keeping the value for a different column

[sam81]

Hello everyone, first post here. I have a problem that has been consuming me. I'm fairly new to Access so I have a grasp of running basic queries but this issue has me stuck.

I have column for a set of five values (let's say: A,B,C,D,E) and a column for months. Let's say the months span from January through June. Each of these six months can have any of these five values (A,B,C,D,E).

I'd like to run a query where I would like to include the value 'E' for the month of June only, but exclude the value 'E' for all other months. The query needs to have all other remaining months and values.

I did this initially by running two separate queries; one where I just ran a query for June and the value 'E' and then another query where I excluded the value 'E' and ran with all other months/values (I did this by using <> "E" next to criteria under the values column in query design). This works but the problem is that it isn't efficient as there are two queries that are different lengths (different number of rows).



Any ideas?

[CJ_London]

I think you need a Cartesian query - two tables (or queries) which are not linked. Something like

SELECT *
FROM tbl1, tbl2
WHERE (tbl1.month='june' and tbl2.value='E') OR (tbl1.month<>'june')

if it is one table then

SELECT *
FROM tbl1
WHERE (tbl1.month='june' and tbl1.value='E') OR (tbl1.month<>'june')

if neither of these are what you are looking for, please post some sample data of your months and values plus what you what as a result

[June7]

Maybe:

SELECT * FROM table WHERE ([value] = "E" AND [month] = "June") OR ([value] <> "E" AND [month]<>"June");

[sam81]

Maybe:

SELECT * FROM table WHERE ([value] = "E" AND [month] = "June") OR ([value] <> "E" AND [month]<>"June");

Where would this line go? Into the Criteria? The entire line? Sorry I'm still new to Access.

[June7]

That is an entire SQL statement. Can type into the SQL View window of query builder (use your table and field names) or use the query designer to build.