Filtering

[BeataG]

Hello,

I have a query containig the following columns:

Id LastName FirstName Position Department

I'd like to filter these records for which the values of LastName, Position and Department are the same.

Could anybody help me?

I'll be grateful for your help. Thank you in advance.

 

[music_al]

Im not sure that makes sense.

Last Name, Position and Department are the same ? As in...

LastName = Department = Position

LastName = Smith
Position = Smith
Department = Smith

I obviously know you mean something else, but what do you really mean ?

Al

 

[boblarson]

Hello,

I have a query containig the following columns:

Id LastName FirstName Position Department

I'd like to filter these records for which the values of LastName, Position and Department are the same.

Could anybody help me?

I'll be grateful for your help. Thank you in advance.

If you have a table where there would be duplicate values for those fields, then your database structure and use is not normalized and should be redesigned.

I too am wondering what it is you are exactly after. What will this get you?

 

[BeataG]

Well, maybe I created a little confusion. Let me exemplify.


ID LastName FirstName Position Department

1 Smith John Programmer IT
2 Smith Andrew Programmer IT
3 Brown Michael Accountant AC


Having such data, the result has to be:
1 Smith John Programmer IT
2 Smith Andrew Programmer IT

I have to just get information from my database, that meet above criteria.

 

[music_al]

OK, you should really create the following tables...

tbl_Role_Position (for the position)
tbl_Department

...and then look up these tables from ComboBoxes on your form.

For a quick answer I havent created these tables for you, just the employee table and a simple query. This query basically looks for...

Dupliacte Last Name
OR
Duplicate Position
OR
Duplicate Department.

If however you wanted...

Dupliacte Last Name
AND
Duplicate Position
AND
Duplicate Department

Then you need to put your criteria all on the same line in the design view.

I think this MIGHT be what youre after. If not, please explain.

Al

 

[dcb]

LastName = LastName
And
Dept = Dept
And
Pos = Pos

Duplicate query would normally use the count method - the problem is trying to find it accross 3 colums. I would suggest that you use a composite of the above values

SELECT Count([Lastname] & [Position] & [Department]) AS CountComposite, [Lastname] & [Position] & [Department] AS NameComposite
FROM YourTable
GROUP BY [Lastname] & [Position] & [Department]
Having (((Count([Lastname] & [Position] & [Department]))>1));

 

[music_al]

just seen your next post. Just put your criteria on the same line. Remember to save your criteria before you run it again

 

[dcb]

Dupliacte Last Name
OR
Duplicate Position
OR
Duplicate Department.

If however you wanted...

Dupliacte Last Name
AND
Duplicate Position
AND
Duplicate Department

Al

Seems we were going the same way with different methods.....

 

[BeataG]

Yes, now it works perfectly.
Thank you all very much for your help.