none
Assist with Top 5 Report RRS feed

  • Question

  • Good Afternoon Everyone,

    How can I achieve the goal.

    I have two queries that return the top 5 PC's and top 5 Monitors from the "asset" Table.

    SELECT * FROM qryTop5NewPC;

    UNION ALL SELECT * FROM qryTop5NewMonitors;

    This returns

    PC's

    and

    Monitors

    Need at least to achieve

    PC / Monitors

    1 PC

    1 Monitor

    2 PC

    2 Monitor

    3 PC

    3 Monitor

    4 PC

    4 Monitor

    5 PC

    5 Monitor

    I also would like to pull from the User Table, the User Name and system installation date. So...

    1. User - Installation Date

         PC - Asset - Description - Etc

         Monitor - Asset - Description - Etc.

    2. User - Installation Date

         PC - Asset - Description - Etc

         Monitor - Asset - Description - Etc.

    3. User - Installation Date

         PC - Asset - Description - Etc

         Monitor - Asset - Description - Etc.

    4. User - Installation Date

         PC - Asset - Description - Etc

         Monitor - Asset - Description - Etc.

    5. User - Installation Date

         PC - Asset - Description - Etc

         Monitor - Asset - Description - Etc.

    Thank you!!!

    • Edited by KCDW Wednesday, May 25, 2016 9:04 PM
    Wednesday, May 25, 2016 8:56 PM

Answers

  • Thank you Bruce for getting me on the right track. Ultimately we needed a specific output described before. Bruce you got us going in the right direction but not quite what wee needed. Here is the final resolution thanks to a co worker (Todd).

    SQL 1 - 3 to grab the info needed and alias it. SQL 4 to bring them all together.

    SELECT TOP 5 Assets.[asset] AS AssetID, Assets.Category, Assets.User, Assets.Location, Assets.[Serial #], Assets.[Asset Description]
    FROM Assets
    WHERE (((Assets.Category)="Monitor") AND ((Assets.User)="new"))
    ORDER BY Asset;
    
    SELECT TOP 5 Assets.[asset] AS AssetID, Assets.Category, Assets.User, Assets.Location, Assets.[Serial #], Assets.[Asset Description]
    FROM Assets
    WHERE (((Assets.Category)="PC") AND ((Assets.User)="new"))
    ORDER BY Asset;
    
    SELECT TOP 5 Assets.[asset] AS AssetID, Assets.Category, Assets.User, Assets.Location, Assets.[Serial #], Assets.[Asset Description]
    FROM Assets
    WHERE (((Assets.Category)="PC") AND ((Assets.R)=True))
    ORDER BY Asset;
    
    SELECT x.assetid, x.category, x.user, x.location, x.counter, x.Serial, x.[Asset Description], x.Row
    FROM (SELECT [qryTop5PCUsers W].AssetID, [qryTop5PCUsers W].Category, [qryTop5PCUsers W].User, [qryTop5PCUsers W].Location, (Select count (*)       From [qryTop5PCUsers W] As A      Where A.AssetID<=[qryTop5PCUsers W].AssetID) AS [Counter], [qryTop5PCUsers W].[Serial #] As Serial, Assets.[Asset Description], "1" As Row FROM [qryTop5PCUsers W]
    Union All
    SELECT [qryTop5NewPC W].AssetID, [qryTop5NewPC W].Category, [qryTop5NewPC W].User, [qryTop5NewPC W].Location, (Select count (*)       From [qryTop5NewPC W] As A      Where A.AssetID<=[qryTop5NewPC W].AssetID) AS [Counter], [qryTop5NewPC W].[Serial #] As Serial, Assets.[Asset Description],  "2" As Row FROM [qryTop5NewPC W]
    Union All
    SELECT [qryTop5NewMonitor W].AssetID, [qryTop5NewMonitor W].Category, [qryTop5NewMonitor W].User, [qryTop5NewMonitor W].Location, (Select count (*)       From [qryTop5NewMonitor W] As A      Where A.AssetID<=[qryTop5NewMonitor W].AssetID) AS [Counter], [qryTop5NewMonitor W].[Serial #] As Serial, Assets.[Asset Description], "3" As Row FROM [qryTop5NewMonitor W])  AS x
    ORDER BY x.counter, x.row, x.AssetID;
    

    and with a small amount of grouping, this is the outcome...


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    • Marked as answer by KCDW Wednesday, June 1, 2016 9:38 PM
    Wednesday, June 1, 2016 9:38 PM

All replies

  • Hi KCDW,

    According to you description, I have made a sample and reproduced this issue. So I suggest that you could modify like below:

    Select * from qryTop5NewMonitors;
    Union all select * from qryTop5NewPC
    Order by ID

    For more information, click here to refer about Combine the results of several select queries by using a union query

    In addition about your second issue, I suggest that you could post a new thread for this issue and there would be more community members to help you and we could focus on the specific issue.

    Thanks for your understanding.





    Thursday, May 26, 2016 3:08 AM
  • Good Morning David,

    Thank you for your response however that will not work. The ID's are issued when the items arrive so if sorted by ID all PC's would still be listed first followed by the Monitors and then any other equipment that might be added to this later.

    I'm thinking something like...

    Select * from qryTop1NewMonitors;
    Union all select * from qryTop1NewPC;

    Select * from qryTop1+1NewMonitors;
    Union all select * from qryTop1+1NewPC;

    Select * from qryTop1+2NewMonitors;
    Union all select * from qryTop1+2NewPC;

    Etc???


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, May 26, 2016 1:19 PM
  • Unless there is a way in the original qry to add an ID field.

    Like

    Assets.[Asset] As ID

    but force each pass as 1, 2, 3 Etc so the ID becomes 1, 2, 3 instead o the real asset number. This would allow me then to use David suggestion of Order By.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    • Edited by KCDW Thursday, May 26, 2016 2:54 PM
    Thursday, May 26, 2016 2:32 PM
  • So if I understand correctly, you are trying to create pairings between PC's and monitors.  And PC's and monitors do not have the same ID's as your first post indicated, so it's not a matter of linking on ID.  So you would want to add something to both of your "top5" queries to establish a one to one link.  If asset ID's are sequential autonumbers you could add a counter to both of your top5 queries and then join on the counter.  I don't know what your Assets table looks like but I threw together something like the following:

    qryTop5NewPC:
    SELECT TOP 5 tblAssets.AssetID,
        tblAssets.Type, (SELECT COUNT(*)
        FROM tblAssets AS A
        WHERE Type = "PC" AND
            A.AssetID<tblAssets.AssetID)
        AS Counter
    FROM tblAssets
    WHERE tblAssets.Type="PC"
    ORDER BY AssetID ASC

    qryTop5NewMonitor:
    SELECT TOP 5 tblAssets.AssetID,

        tblAssets.Type, (SELECT COUNT(*)
        FROM tblAssets AS A
        WHERE Type = "Monitor" AND
            A.AssetID<tblAssets.AssetID)
        AS Counter
    FROM tblAssets
    WHERE tblAssets.Type="Monitor"
    ORDER BY AssetID ASC

    Then join the two on Counter:

    SELECT qryTop5NewPC.AssetID,
        qryTop5NewPC.Type,
        qryTop5NewMonitor.AssetID,
        qryTop5NewMonitor.Type
    FROM qryTop5NewPC
    INNER JOIN qryTop5NewMonitor
    ON  qryTop5NewPC.Counter = qryTop5NewMonitor.Counter;

    -Bruce
    • Edited by Bruce Hulsey Thursday, May 26, 2016 10:28 PM
    • Marked as answer by KCDW Friday, May 27, 2016 10:11 PM
    • Unmarked as answer by KCDW Wednesday, June 1, 2016 9:39 PM
    Thursday, May 26, 2016 9:57 PM
  • Good Morning Bruce,

    First thank you for posting, that looks promising. after playing with the first query it looks like it could work. I have revised the code to match my fields (inherited db not my style) and it works for the info provided however since there are literally thousands of records it is taking the count where the item is in the entire db rather than the top 5. So here is the qry

    SELECT TOP 5 Assets.[Asset] AS AssetID, Assets.Category, (SELECT COUNT(*)
    FROM Assets AS A
    WHERE Category = "PC" AND A.Asset<Assets.[Asset]) AS [Counter], Assets.Barcode, Assets.[Serial #], Assets.[Asset Description], Assets.User, Assets.Location
    FROM Assets
    WHERE (((Assets.Category)="PC") AND ((Assets.User)="new"))
    ORDER BY Asset;

    and here is the result screenshot

    Again thank you Bruce for your input. I think this is really close and may work just fine but wondering if the result set could be 1 -5 rather than 131 - 135. Also without the extra fields added the result set was 0 - 4. CDan there be a +1 to make it 1 - 5?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    • Edited by KCDW Friday, May 27, 2016 6:27 PM
    Friday, May 27, 2016 4:52 PM
  • after completing the changes to the second qry and running a Union to compare to the Inner Join here are those results.

    Union

    While the Inner join returned no results...because the Field Asset ID will never have corresponding ID's. Here is the qry for that

    SELECT [QryTop5NewPC_Test_ID Field Add].[AssetID], [QryTop5NewPC_Test_ID Field Add].[Category], [QryTop5NewMonitor_Test_ID Field Add].[AssetID], [QryTop5NewMonitor_Test_ID Field Add].[Category]
    FROM [QryTop5NewPC_Test_ID Field Add] INNER JOIN [QryTop5NewMonitor_Test_ID Field Add] ON [QryTop5NewPC_Test_ID Field Add].[Counter] = [QryTop5NewMonitor_Test_ID Field Add].[Counter];

    It would work if the Top 5 of each PC and Monitor were returned as 1 - 5 and then joined or unioned.

    Still, I'm open for more suggestions

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, May 27, 2016 7:13 PM
  • Since you are specifying in your query that you want to filter on the user "New" you will also want to filter the subquery that provides your Counter by user, e.g. your top 5 PC query would look like this:

    SELECT TOP 5 Assets.[Asset] AS AssetID,
        Assets.Category, (SELECT COUNT(*)
        FROM Assets AS A
        WHERE Category = "PC" AND
            A.Asset<Assets.[Asset] and A.User=Assets.User)
        AS [Counter],
        Assets.Barcode,
        Assets.[Serial #],
        Assets.[Asset Description],
        Assets.User,
        Assets.Location
    FROM Assets
    WHERE Assets.Category="PC" AND
        Assets.User="new"
    ORDER BY Asset;


    • Edited by Bruce Hulsey Friday, May 27, 2016 7:52 PM
    • Marked as answer by KCDW Friday, May 27, 2016 10:11 PM
    • Unmarked as answer by KCDW Wednesday, June 1, 2016 9:39 PM
    Friday, May 27, 2016 7:39 PM
  • Yes Bruce that works perfectly!!!

    Thank you very much for that!!!

    Now about the other part...

    Is there a way to tie in the top five People due replacement systems?

    The date the equipment was last put out is in the same Table as above. The Field is [Installation Date]. Then the second Table is User with a Field [Name]. I would not have chosen that name myself but we are upgrading a step at a time right now.

    So Top 5 oldest [Assets].[Installation Date]

    and

    [User].[Name]

    User.name has a relationship to Assets.User as an indeterminate and (1) Join Type, both fields must be equal.

    Here is a screenshot of your queries used as the base of the Union Query

    and the three sql's

    SELECT TOP 5 Assets.[Asset] AS AssetID, Assets.Category, (SELECT COUNT(*)
         FROM Assets AS A
         WHERE Category = "Monitor" AND
             A.Asset<Assets.[Asset] and A.User=Assets.User) AS [Counter], Assets.Barcode, Assets.[Serial #], Assets.[Asset Description], Assets.User, Assets.Location
    FROM Assets
    WHERE (((Assets.Category)="Monitor") AND ((Assets.User)="new"))
    ORDER BY Asset;
    

    SELECT TOP 5 Assets.[Asset] AS AssetID, Assets.Category, (SELECT COUNT(*)
         FROM Assets AS A
         WHERE Category = "PC" AND
             A.Asset<Assets.[Asset] and A.User=Assets.User) AS [Counter], Assets.Barcode, Assets.[Serial #], Assets.[Asset Description], Assets.User, Assets.Location
    FROM Assets
    WHERE Assets.Category="PC" AND
         Assets.User="new"
    ORDER BY Asset;
    

    SELECT * FROM [QryTop5NewPC_Test_ID Field Add];
    
    UNION ALL SELECT * FROM [QryTop5NewMonitor_Test_ID Field Add]
    ORDER BY Counter;
    

    I'll mark your other posts as answer because they do answer the base question. We'll be closed here for the next three days so I won't get back to the User.Name add in until then.

    Thank you again!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, May 27, 2016 10:10 PM
  • So I hope everyone had a good Memorial Day holiday!!!

    Getting back to this question, having modified your code Bruce...Trying to incorporate to take the Top 5 oldest issued systems with the user name etc. I have the following SQL and it gives the names expected however the count isn't working.

    SELECT TOP 5 Assets.[asset] AS AssetID, Assets.category, (SELECT COUNT(*)
         FROM Assets AS A
         WHERE Category = "PC" AND
             A.Asset<Assets.[Asset] and A.User=Assets.User) AS [Counter], Assets.[Serial #], Assets.[Asset Description], Assets.User, Assets.Location
    FROM Assets
    WHERE (((Assets.R)=True) AND ((Assets.Category)="PC"))
    ORDER BY Assets.asset;
    

    In the following caption (names and serial# removed) you can see the counter is not returning the desired results. Please help me correct the error in the sql above.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, May 31, 2016 9:29 PM
  • Alright the above code is asking the wrong question.

    I need to have a list like the first 2 lists numbering 0-4 of the five PC replacements listed. What I am getting is for each person, which number of their PC is it. the first four on the list only have 1 PC therefore it is represented in the list as 0 where the last person has three PC's and that is why it has a 2.

    Somehow I need to get the count after the list of PC's is achieved so it puts the five in 0 - 5 order.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    • Edited by KCDW Wednesday, June 1, 2016 6:04 PM correcte text
    Wednesday, June 1, 2016 4:54 PM
  • Thank you Bruce for getting me on the right track. Ultimately we needed a specific output described before. Bruce you got us going in the right direction but not quite what wee needed. Here is the final resolution thanks to a co worker (Todd).

    SQL 1 - 3 to grab the info needed and alias it. SQL 4 to bring them all together.

    SELECT TOP 5 Assets.[asset] AS AssetID, Assets.Category, Assets.User, Assets.Location, Assets.[Serial #], Assets.[Asset Description]
    FROM Assets
    WHERE (((Assets.Category)="Monitor") AND ((Assets.User)="new"))
    ORDER BY Asset;
    
    SELECT TOP 5 Assets.[asset] AS AssetID, Assets.Category, Assets.User, Assets.Location, Assets.[Serial #], Assets.[Asset Description]
    FROM Assets
    WHERE (((Assets.Category)="PC") AND ((Assets.User)="new"))
    ORDER BY Asset;
    
    SELECT TOP 5 Assets.[asset] AS AssetID, Assets.Category, Assets.User, Assets.Location, Assets.[Serial #], Assets.[Asset Description]
    FROM Assets
    WHERE (((Assets.Category)="PC") AND ((Assets.R)=True))
    ORDER BY Asset;
    
    SELECT x.assetid, x.category, x.user, x.location, x.counter, x.Serial, x.[Asset Description], x.Row
    FROM (SELECT [qryTop5PCUsers W].AssetID, [qryTop5PCUsers W].Category, [qryTop5PCUsers W].User, [qryTop5PCUsers W].Location, (Select count (*)       From [qryTop5PCUsers W] As A      Where A.AssetID<=[qryTop5PCUsers W].AssetID) AS [Counter], [qryTop5PCUsers W].[Serial #] As Serial, Assets.[Asset Description], "1" As Row FROM [qryTop5PCUsers W]
    Union All
    SELECT [qryTop5NewPC W].AssetID, [qryTop5NewPC W].Category, [qryTop5NewPC W].User, [qryTop5NewPC W].Location, (Select count (*)       From [qryTop5NewPC W] As A      Where A.AssetID<=[qryTop5NewPC W].AssetID) AS [Counter], [qryTop5NewPC W].[Serial #] As Serial, Assets.[Asset Description],  "2" As Row FROM [qryTop5NewPC W]
    Union All
    SELECT [qryTop5NewMonitor W].AssetID, [qryTop5NewMonitor W].Category, [qryTop5NewMonitor W].User, [qryTop5NewMonitor W].Location, (Select count (*)       From [qryTop5NewMonitor W] As A      Where A.AssetID<=[qryTop5NewMonitor W].AssetID) AS [Counter], [qryTop5NewMonitor W].[Serial #] As Serial, Assets.[Asset Description], "3" As Row FROM [qryTop5NewMonitor W])  AS x
    ORDER BY x.counter, x.row, x.AssetID;
    

    and with a small amount of grouping, this is the outcome...


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    • Marked as answer by KCDW Wednesday, June 1, 2016 9:38 PM
    Wednesday, June 1, 2016 9:38 PM
  • Hi Chris,

    Sorry, I'm just now seeing all this.  I'm glad you got it sorted out!

    -Bruce

    Thursday, June 2, 2016 8:51 PM