How to search for multiple criteria from data stored in one column?

[Hiestermonkey]

I am a novice Access user trying to manipulate a large database (approx. 20,000 rows of data) in order to analyze possible future employment gaps. Most of the data is duplicated, (such as name, id, position) with the exception of the persons work qualifications and the status of those qualifications i.e. qualified or trainee.

The employment qualifications are in one column and the status (trainee/qualifed) are in another column. The rest of the data is duplicated per employee.

Current example:

1. • Id Nme Age Pay Qual Qual.status
   • 1 Bob 35 9 FBDC Q
   • 1 Bob 35 9 GHTY T
   • 1 Bob 35 9 ERMC Q
   • 2 Jill 25 8 GJCV Q
   • 2 Jill 25 8 TYUJ Q
   • 2 Jill 25 8 WQDS T

I need to analyze the data based on a number of factors,(pay scale, age) as well as a number of qualifications.
Access can tell me who holds certain qualifications, but then the personal data is duplicated, and with so much data being returned by the query, I currently have to analyze it by visually inspecting each line for the right combination of qualifications.

With the qualification data in one column, I can't seem to query it based on more than one qualification. (when I ask a qualification parameter question with "and", no results are returned).
Here is an example from my query that return no records (note that it is the actual table and field names, not the examples provided before). The Emplid is the employees id, and I'm looking for the qualifications of "divs" and "rxb2".

SELECT [Gallatin Org/Emplid/Quals Merge].Emplid, [Gallatin Org/Emplid/Quals Merge].Qualification, [Gallatin Org/Emplid/Quals Merge].Qualification, [Gallatin Org/Emplid/Quals Merge].Status
FROM [Gallatin Org/Emplid/Quals Merge]
WHERE ((([Gallatin Org/Emplid/Quals Merge].Qualification) ="divs") AND (([Gallatin Org/Emplid/Quals Merge].Qualification) ="rxb2"));


Is there an easy way to do that, or to transpose the qualification and qualification status into a single row (tied to the personal data) to analyze it?

Thank you for your time-

[Rabbit]

The WHERE clause only looks at one row at a time. It will not look at all records nor will it look at similar records.

What you'll want to do is use a subquery to return the subset of employee IDs that match the qualifications and then use that subquery in the WHERE clause to filter by the employee ID.

[Hiestermonkey]

Thanks for the reply Rabbit. Where should I start with the subquery? Like I said I'm a novice, and have very little experience with SQL. Any advice is greatly appreciated.

[Rabbit]

Well, I would do something like this:

SELECT *
FROM Table1
WHERE
  UniqueID IN (
    SELECT DISTINCT UniqueID
    FROM Table1
    WHERE SomeField = 'Hello'
  )
  AND
  Unique IN (
    SELECT DISTINCT UniqueID
    FROM Table1
    WHERE SomeField = 'World'
  )

[Hiestermonkey]

Thanks for the reply Rabbit. I'll give it a shot. I'm not sure I know exactly where to put that subquery though...I'll try to place it somewhere within the initial WHERE query.

[Rabbit]

You put it in the same place I put it. I have 2 subqueries in my WHERE clause.

[Hiestermonkey]

I appreciate the input, but you are definitely dealing with a total novice. Please bear with me.

Here is what I have in my query (this and only this), and it is asking for 2 unique Id's (as is stated in the sql language)

SELECT *
FROM [Gallatin Org/Emplid/Quals Merge]
WHERE UniqueID IN (
SELECT DISTINCT UniqueID
FROM [Gallatin Org/Emplid/Quals Merge]
WHERE Qualification = 'DIVS'
)
AND
Unique IN (
SELECT DISTINCT UniqueID
FROM [Gallatin Org/Emplid/Quals Merge]
WHERE Qualification= 'RXB2'
);

the field is qualification and the quals I am looking for are DIVS and RXB2. when it asks for unique Id, is it asking for a single person's unique ID?

If so, I may need another way to approach it. I would like to be able to ask for the Qualifications, and have Access find who is qualified...

does that make sense? I know I may be missing your intent here....This has been a self taught process up to this point.

[Rabbit]

You need to replace the word UniqueID with your unique ID field. I don't know what you're using as your unique ID field so I put the word UniqueID as a placeholder.

From your earlier SQL, I think your unique ID is emplid but I have no idea if that's right or not.

[Hiestermonkey]

Thanks Rabbit. I appreciate it. I'll give it a shot and get back to you.