Creating a query to find differences (1 Viewer)

[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:

MS Access - Grouping Data

MS Access - Grouping Data - In this chapter, we will be covering how to calculate on a group of records in Access. We have created a field that calculates row by row or record by record to create a line total or subtotal field, but what if we wanted to calculate down by a group of records rather...

www.tutorialspoint.com

Specifically you want to use the MAX function:

SQL MIN() and MAX() Functions

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,