Creating a query to find differences

[biofaku]

Hi, I'm trying to create a query that allows me to find the differences between values inside a same table.
Let's suppose I have this table:

Client	Item
A	1
A	2
A	3
B	1
B	2
B	4

The query should return only these results:

Client	Item
A	3
B	4

I tried using WHERE NOT IN, but so far it did not work.
Any idea how to achieve this?

Thanks!

 

[plog]

I don't understand your words, but do understand your example. "Difference" implies subtraction, but that's not what you want based on your example. What you want is to return the highest value in item for each Client. that can be done with a simple aggregate query:

Grouping Data in MS Access

Discover techniques for grouping data in MS Access to improve your database reporting and analysis.

www.tutorialspoint.com

Specifically you want to use the MAX function:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

I would read through that entire tutorial.

 

[biofaku]

Hi plog, thanks for your input. I'm sorry if I have not expressed myself well, and I think the example was confusing because I used numbers on the column Item. What I'm trying to do is a query that gets the values on the column Item that are not in common with the Client column. The value on any of both columns could be a number, a date, a letter.
In this example, both the numbers 1 and 2 are found either on the client A or the client B, but that doesn't happen with the item 3 or the item 4.

I think if EXCEPT on SQL worked on access, the solution would be query using that statement

 

[bastanu]

Please have a look at the attached (query qryDifferencesInItems).
Cheers,

 

[plog]

Words aren't working. Please provide sample data to demonstrate what you want.

 

[biofaku]

Thanks everyone for the input!
bastanu that worked perfectly! Thanks a lot!

 

[bastanu]

You're very welcome, good luck with your project!
Cheers,