Please help with query

[bluenose76]

I have a db that holds many thousands of records. Predominantly it is showing me an individuals name and what courses they have attended. (It is basically an excel spreadsheet which I have imported into Access in order to utilise the Queries to give me the results I need)

If for arguments sake I would like to see all users who are holding an “ECDL” qualification then I would simply run a Query to look for “ECDL” and ti would give me the desired results.

My question is, How could I do the reverse? If my table holds ten thousand users and only 100 are holding “ECDL” how can I set up a query to show me all users who do not hold “ECDL”?

I hope that I have explained the above well enough for you all to understand what I am trying to do?

Thank you all in advance for your help.

Regards
Bev

 

[DCrake]

In your query on the condition line you would enter <> "ECDL"

However in a more refined search you would have a form that contains an option group control with three options

a) Equals to
b) Not equal to
c) Either (All)


Then on the on click event of the option group you would have a select case statement

Select Case Me.Options
   Case 1: SQL = "Select * From Table Where Field ='" & AnyString & "'"
   Case 2: SQL = "Select * From Table Where Field <> '" & AnyString & "'"
   Case 3: SQL = "Select * From Table Where Field Is Not Null"
End Select

Obviously there is a lot of code surounding this but the logic and concept is correct.

David

 

[bluenose76]

In your query on the condition line you would enter <> "ECDL"

However in a more refined search you would have a form that contains an option group control with three options

a) Equals to
b) Not equal to
c) Either (All)


Then on the on click event of the option group you would have a select case statement

Select Case Me.Options
   Case 1: SQL = "Select * From Table Where Field ='" & AnyString & "'"
   Case 2: SQL = "Select * From Table Where Field <> '" & AnyString & "'"
   Case 3: SQL = "Select * From Table Where Field Is Not Null"
End Select

Obviously there is a lot of code surounding this but the logic and concept is correct.

David

Thank you for your reply,

I have tried your segestion however it is not working for me?

Although i gave the example of ECDL, i do have many may qualifications, some of which are very similar. When searching for someone who holds a qualification i am using a Like criteria in my query. I need to replicate this in reverse. i.e. i need to run a query that will show me a lst of people who do not hold
Like "*" & "EUROPEAN COMPUTER DRIVING LICENCE" & "*" Or Like "*" & "ECDL" & "*" Or Like "*" & "BCS" & "*"

I have tried using <> as you have sugested but this does not run?

I appologise if i was not clear enough initially

thank you

 

[jdraw]

Thank you for your reply,

I have tried your segestion however it is not working for me?

Although i gave the example of ECDL, i do have many may qualifications, some of which are very similar. When searching for someone who holds a qualification i am using a Like criteria in my query. I need to replicate this in reverse. i.e. i need to run a query that will show me a lst of people who do not hold
Like "*" & "EUROPEAN COMPUTER DRIVING LICENCE" & "*" Or Like "*" & "ECDL" & "*" Or Like "*" & "BCS" & "*"

I have tried using <> as you have suggested but this does not run?

I appologise if i was not clear enough initially

thank you

Perhaps you could show us your table name, and the fields.
Also, do you have some codes (ECDL) for your qualifications?

Can you show us the query you are trying to run?

DCrake gave you the key concepts for solution, and told you there was a lot of coding required to make the solution work.
What did you actually try?

 

[Galaxiom]

For like comparisons use:
Not Like
rather than:
<> (not equal)

 

[gemma-the-husky]

well, you shouldnt be typing in these qualifications, as it allows problems of this sort to be CAUSED by having multiple versions of the same thing.

The best practice is to have a separate lookup table of the relevant qualifications that you want to manage - so that eg, qualification 1 is ECDL. - and now your table just record that certain users have qualification 1.

it also makes your querying easier - as you can select the qualifications you want to check from a combo box, or list box, but your program can then easily find the people with or without the relevant qualification. And the other important thing is that you can change the text description without causing any problems - you could even have a brief description (ECDL) and a full-text description - and choose to use either depending on the form or report layout.

The other thing is, going on from this, is that you shouldnt have a table with multiple columns for each user, one for each qualification. You should have a table for the users, and another table showing the user and his qualifications

ie - so you get

TABLE 1 - users
user1, fred smith, address details, phone no
user2, joe bloggs. address, phone
etc

TABLE 2 - qualifications
qual1 ECDL
qual2 GCSE
etc

TABLE 3 - user-qualifications
user1, qualification 1
user1, qualification 2
user1, qualification 6
user2, qualification 1
user2, qualification 3

etc


You may have this sort of set up already of course, but I suspect not - in view of your current problems.