CODEWITHSUNDEEP
databasems-access
PoPeio
PoPeio

Reputation: 152

How to Select a record in Access if it is not a duplicate value

Ok, from the title it seems to be impossible to understand, I'll try to be as clear as possible. Basically, I have a table, let's call it 'records'. In this table I have some products, of which I store 'id', 'codex' (which is a unique identifier for a certain product in the whole database), 'price' and 'situation'. This last one is a string which tells me wether the product has just entered the store (in that case it is set to 'IN'), or it has already been sold ('OUT' in this case). The database was not created by us, I HAVE to work with that although it is horribly structured... The guy who originally projected the database decided to register when a product's situation passes from 'IN' to 'OUT' in the following way: instead of UPDATEing the corresponding value in the table, he used to take the row of data with 'IN' as situation, and to DUPLICATE it setting, that time, 'OUT' as situation. Just to sum up: if a product has not been sold yet, it will have one row of dedicated data; otherwise those rows will be two, identical except for the 'situation' field. What I need to do is: select a product if (and ONLY if) there is no duplicate for it. Basically, I can (and should) look for a 'codex', and if I my Count(codex) ends up being >1, I do not select the row. I hope the explanation of the process is clear enough... I tryed many alternative (no, SELECT DISTINCT is not a solution): des anyone have an idea of how to do that? Because really, none of us three could come up with a good solution! Here is the schema for the table, I hope it is sufficiently clear, and if not do not hesitate asking for more details. Just as a reminder: the project is in (sigh...) VB.net, the database is in Microsoft Access (mdb). I could not find a solution on StackOverFlow, I hope this is not a duplicate question! Thanks in advance for the help.

id     codex    price    situation
 1         1     2.50           IN
 2         1     2.50          OUT
 3         2     3.45           IN
 4         3    21.50           IN
 5         2     3.45          OUT
 6         4     1.50           IN

Upvotes: 0

Views: 48

Answers (1)

Manuel Rocha
Manuel Rocha

Reputation: 62

To check if I understand what your problem is... In your example table you just want to get the lines with ID 4 a 6, right?

If is that what you want, and If you want only the not sold ones try this command

SELECT 
   *
FROM 
   records
WHERE 
   codex 
   not in 
   (
      SELECT
         codex 
      FROM 
         records 
      WHERE 
         situation ='OUT'
   )

Upvotes: 2

Related Questions