none
Query in access 2007 RRS feed

  • Question

  • My table looks like this:

    <tfoot></tfoot>
    Assigned Assets
    ID Employee ID Asset ID Date Recieved Status
    1 22 250 23/07/2015 Inactive
    2 1 21 03/07/2015
    3 1 250 24/07/2015 Active

    And  I need a query to check if there are two of the same asset Id's like 250 and then check if they're both set to active. If they are then I need the oldest one to have it's status changed to inactive. It sounds simple enough but I'm very new to access and haven't gotten around to learning queries yet. Not sure how to write the code or the expressions for this.

    Thanks in advance!

    • Moved by Naomi N Tuesday, July 21, 2015 9:21 PM Better forum
    Tuesday, July 21, 2015 9:00 PM

Answers

  • Eduard14,

    You can use ID field to find roecords.

    First you need know double asset id with status active and max recived date:

    SELECT [asset id], 
           Max([date recived]) AS maxDate 
    FROM   tabname 
    WHERE  status = "active" 
    GROUP  BY [asset id] 
    HAVING Count([asset id]) = 2 

    Now you can use this query to get id's to change

    SELECT id 
    FROM   tabname 
           INNER JOIN (SELECT [asset id], 
                              Max([date recived]) AS maxDate 
                       FROM   tabname 
                       WHERE  status = "active" 
                       GROUP  BY [asset id] 
                       HAVING Count([asset id]) = 2) AS tmp 
                   ON tabname.[asset id] = tmp.[asset id] 
                      AND tmp.[maxdate] > tabname.[date recived] 

    See join condition (date comprasion)

    So you have ids you can change status field:

    UPDATE tabname 
    SET    status = "inactive" 
    WHERE  id IN (SELECT id 
                  FROM   tabname 
                         INNER JOIN (SELECT [asset id], 
                                            Max([date recived]) AS maxDate 
                                     FROM   tabname 
                                     WHERE  status = "active" 
                                     GROUP  BY [asset id] 
                                     HAVING Count([asset id]) = 2) AS tmp 
                                 ON tabname.[asset id] = tmp.[asset id] 
                                    AND tmp.[maxdate] > tabname.[date recived]) 


    Michał


    Wednesday, July 22, 2015 8:47 AM

All replies

  • Access query language is different than T-SQL. I'm going to move your question to the appropriate forum for the Help.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 21, 2015 9:20 PM
  • Shouldn't this be in an Access forum?  I am not sure if T-SQL translates directly to Access SQL.

    Anyway, I rather not spend time to write the actual query for this.  However, I can give you an idea of how to address it.

    Find Duplicates - You can find the duplicates by creating a Common Table Expression (CTE).  To make it simpler, filter for a Status of Active.  Group by AssetID and return AssetID and the number of rows per AssetID by using COUNT.  Ensure that you specify a HAVING clause where COUNT(*) > 1 (let's call this CTE Duplicates).

    SELECT
    AssetID
    ,COUNT(*) AS NumOfRows
    FROM TABLE AS T
    WHERE
    Status = 'Active'
    GROUP BY
    AssetID
    HAVING COUNT(*) > 1

    Find Oldest Row - Join your CTE in the previous step with the base table using the AssetID.  Use a windowing function with ROW_NUMBER to partition your data by AssetID and order by Date Received from the base table.  This will group the data by AssetID and order each group by Date Received in ascending order.  In addition, the ROW_NUMBER function will enumerate each group starting with 1.  Since the query specified ascending order by Date Received, the oldest row will be where the ROW_NUMBER value is 1.  This query will also be another Common Table Expression (let's call it OldestRow).

    SELECT
    ROW_NUMBER() OVER(PARTITION BY AssetID ORDER BY DateReceived) AS PartitionRowOrderId
    ,T.ID
    FROM Table AS T
    INNER JOIN Duplicates AS D
    ON
    D.AssetID = T.AssetID ...

    Result Set - Query your second CTE (OldestRow) where RowOrderId is 1.

    SELECT
    O.ID
    FROM OldestRow AS O
    WHERE
    O.PartitionRowOrderId = 1

    This should be more than enough to get you started.  If you need additional help, search for "SQL Server, find duplicate rows".  There are a ton of results.  Below is the first result that I found.

    http://stackoverflow.com/questions/2112618/finding-duplicate-rows-in-sql-server

    If you need help understanding Common Table Expressions, check out the link below.

    https://technet.microsoft.com/en-US/library/ms190766%28v=SQL.105%29.aspx?f=255&MSPPError=-2147217396

    UPDATE:

    Turns out that Microsoft went through the trouble to list out in detail how to remove duplicates from a SQL Server table.  You can use the link below as a reference.

    https://support.microsoft.com/en-us/kb/139444

    • Edited by J I M B O Tuesday, July 21, 2015 9:24 PM
    Tuesday, July 21, 2015 9:22 PM
  • I didn't reply to this question as the problem is a relational division problem of some sort and while it has relatively simple solutions in T-SQL I am not sure about Access solutions.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 21, 2015 9:34 PM
  • Eduard14,

    You can use ID field to find roecords.

    First you need know double asset id with status active and max recived date:

    SELECT [asset id], 
           Max([date recived]) AS maxDate 
    FROM   tabname 
    WHERE  status = "active" 
    GROUP  BY [asset id] 
    HAVING Count([asset id]) = 2 

    Now you can use this query to get id's to change

    SELECT id 
    FROM   tabname 
           INNER JOIN (SELECT [asset id], 
                              Max([date recived]) AS maxDate 
                       FROM   tabname 
                       WHERE  status = "active" 
                       GROUP  BY [asset id] 
                       HAVING Count([asset id]) = 2) AS tmp 
                   ON tabname.[asset id] = tmp.[asset id] 
                      AND tmp.[maxdate] > tabname.[date recived] 

    See join condition (date comprasion)

    So you have ids you can change status field:

    UPDATE tabname 
    SET    status = "inactive" 
    WHERE  id IN (SELECT id 
                  FROM   tabname 
                         INNER JOIN (SELECT [asset id], 
                                            Max([date recived]) AS maxDate 
                                     FROM   tabname 
                                     WHERE  status = "active" 
                                     GROUP  BY [asset id] 
                                     HAVING Count([asset id]) = 2) AS tmp 
                                 ON tabname.[asset id] = tmp.[asset id] 
                                    AND tmp.[maxdate] > tabname.[date recived]) 


    Michał


    Wednesday, July 22, 2015 8:47 AM