How to put extra field in query

[sven2]

Hello,

How can I put a count field in my query?

This is the query:

SELECT Personeels_evaluaties.Itemnummer, Personeels_evaluaties.Personeelsnummer
FROM (Item_stam INNER JOIN Personeels_evaluaties ON Item_stam.Itemnummer = Personeels_evaluaties.Itemnummer) INNER JOIN Functiewaardering_Stam ON Item_stam.Itemnummer = Functiewaardering_Stam.Itemnummer
WHERE (((Personeels_evaluaties.Personeelsnummer)=102))

And the result could be:


itemnummer ; personeelsnummer
1 ; 102
3 ; 102
3 ; 102
3 ; 102
6 ; 102
6 ; 102


I want to add an field that count the distinct itemnummer
So the result should be:

itemnummer ; personeelsnummer ; count

1 ; 102 ; 1
3 ; 102 ;3
6 ; 102 ;2


Is this possible?

Thanks in advance,
Sven.

 

[ArtMiles]

I am not sure what you are doing but this should get you close.

SELECT Personeels_evaluaties.Itemnummer, Personeels_evaluaties.Personeelsnummer, Count(Personeels_evaluaties.Itemnummer)as [Count]
FROM (Item_stam INNER JOIN Personeels_evaluaties ON Item_stam.Itemnummer = Personeels_evaluaties.Itemnummer) INNER JOIN Functiewaardering_Stam ON Item_stam.Itemnummer = Functiewaardering_Stam.Itemnummer
GroupBy Personeels_evaluaties.Itemnummer, Personeels_evaluaties.Personeelsnummer
Having (((Personeels_evaluaties.Personeelsnummer)=102))


Art Miles

 

[sven2]

Hello,

I have still some problemes to make my delete query ...

I have made an example to show what is the problem ...

This is the case:

there are employees that have functions (can be more then 1)
Functions have courses (itemnummers)

Now when I want to remove a function from a employee that have for example 2 functions with the same course this course should not be removed.

So i have made a listbox with 3 headers employeenumber, coursenumber and function. Now the thing is when I want to remove the function bobineur for employee 169 the result should be that course (itemnummer) 11 and 15 should be removed.

How can I do this?

Thanks in advance for the help.

Sven.

 

[sven2]

Hello,

again i am a little bit further ...

I have made 3 query's:
* a query that returns all the items (allitems)
* a query that returns items to remove (itemstoremove)

then I put the 2 query's together ... and I get the right result (query remove)


Now I have to change the last query into a delete query and this isn't working ...

How can I change the query (queryremove) to a delete query that is working ...

see example.

best regards,
Sven.

 

[Rabbie]

Before you can change a SELECT query into a DELETE query it must be an updatable query. See this link for more information.