How to retreive specific records?

[meyou]

Hi,

I am new here. I'm having a problem with a query in Access 2003. Actually since I'm a beginner, I think I just don't know how to build the query after all!

I have a table named candidatesExperiences. For example, here's some data that appears in the table :

idExp idCandidates
5 12
12 12
45 34
45 12
23 23

The two fields (idExp, idCandidates) are the PK. That table is a link between table Experiences(PK = idExp) and Candidates(PK=idCandidates)

Now, I need to build a query to retrieve the candidates with experience 5 and 12 and 45. The problem is, if I'm using the AND operator, nothing will show up. If I'm using the OR operator, candidate 12 and 34 shows up but I need to eliminate candidate 34 because he don't have experience 5 and 12.

How should I built my query?

Sincerely,

 

[DCrake]

Try using the In() command

In(5,12,etc)

 

[Brianwarnock]

The In(5,12,45) will produce the same result as Or.

I think that you are going to need to Count the IdExp or IdCandidates and criteria =3

Brian

 

[meyou]

Ok,

I have tried with the IN operator, but yes, it's the same result as with the OR.

Now, for the count possibility, since that I'm a beginner, I don't really where to start.

Any suggestions?

Sincerely,

 

[Brianwarnock]

Hang on I'm a slow typist

Brian

 

[Brianwarnock]

Select IdExp, IdCandidatrs,Count(IdCandidates) as CountofIDs
From Tablename
Where IdExp In(5,12,45)
Group By IdCandidates
Having Count(IdCandidates) =3

I think that is the Sql code.

Brian

 

[meyou]

Ok,

I have changed some fields name to reflect the dabase but here's the error when trying to run the query :

"You tried to execute a query that does not include the specified expression 'idExp' as part of an aggregate function"

Here's the code :

Select idExp, idCidm,Count(idCidm) as CountofIDs
From [experience-cv]
Where idExp In(5,22)
Group By idCidm
Having Count(idCidm) =2

 

[Brianwarnock]

OOps! sorry remove the IdExp from the Select, unless you need it in the output, inwhich case include it in the Group By, but change the order in the Select.

Brian

 

[meyou]

Ok,

Now it runs but with no results. I only see the headers.

One thing is that if I remove the count part, the CountofIDs column is always showing 1 is it normal?

Would you prefer a printscreen?

 

[Brianwarnock]

Ah! that's the problem with untested air code silly mistakes are made.
You cannot Group on the IdExp, in the Design grid make count no show also.

In SQL only the IdCidm should appear, if you require other data it is got by joining this to the tables on IdCidm

Brian

 

[meyou]

Ok,

It's working!

Now, if I want the name of the experience that is in another table, how should I proceed?

 

[Brianwarnock]

Now that you have a list of the IdCidm s that you require you can join this query to other tables or queries on the IdCidm to get further data.
Brian

PS sorry I made heavy weather of what was a simple query.

 

[meyou]

Ok,

Perfect, thank you very much for your help.

Really appreciated.

Sincerely,

 

[Brianwarnock]

Glad we got there in the end, thanks for sticking with this old guy.

Brian