Unanswered: Query to compare columns and display results based on comparison
I am new to database world, therefore, please if possible, treat me as a new user. I come from networking background and have used as much logic as possible in the project i have been assigned.
Basically, I have amended Microsoft's Access template (Asset tracking database) to fit my needs, however, there are a few issues that I just cannot resolve. I have two rows (amongst many) in my table (Hardware Assets) called SerialNumberSpare and SerialNumber. As I purchase a new asset I create a record and use SerialNumberSpare and type in the serial number (I also assign it a SPARE status). Once I have deployed and used the asset I then create another record and use SerialNumber to update the database (the status in new record will be USED). SerialNumber is exactly the same as the SerialNumberSpare (obviously). The issue arrises when I run a query on Spare Assets. It will show all assets with SPARE status regardless of the fact that I have created another record whith the same serial number but tagged it as used (and I understand that should be the case). But I would like the query to compare the two columns - SerialNumberSpare and SerialNumber and if there are matching Serial Numbers I would like the query not to show them and only show the Serial Numbers that exist in SerialnumberSpare column but do not exist in SerialNumber column.
I hope this makes sense. If it doesn't, I will try and explain it somehow differently. Just a reminder, I am new to this so please, if possible, go easy on me
Thank you for your reply, however, that did not fix the problem. When I run that query I get only a row of two empty cells. Do you want me to try and explain the problem again as it does look very confusing?
I don't get why you have the two different fields. I also don't understand why you have two separate records for the one asset. It might be worth considering a review of your table design.
If your query extracts data which has a status of SPARE, of course you'll get duplicates since you are entering two records for the one asset (which also indicates something isn't right to me). To eliminate duplicates based on the serial number, you would normally just group on the serial number. Since you have two fields, you might have to concatenate them and then group on that. None of this would be a problem if you had one field for serial number and one record for an asset.
StarTrekker you are 100% spot on. I will only be changing status from spare to used. I was going to do that originally, but I had to have two records due to other fields. Anyway, I will be changing status and take it from there.
Thank you very much, and you Sinndho, you have both been helpful.