Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Query to compare columns and display results based on comparison

    Hi Guys,

    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

    Many thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    SELECT ...
    FROM ...
    WHERE SerialNumberSpare <> SerialNumber

    Or am I missing something here?

    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Hi Sinndho,

    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?

    Thanks

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I'm obviously missing something here

    Have a nice day!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Don't bet on it, I could just as easily be misinterpreting.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2009
    Posts
    3
    Hey guys,

    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.

    Best regards

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •