none
Query Assist please combining 3 queries for system installs RRS feed

  • Question

  • Thank you for your assistance!!!

    I have 3 Queries very similar

    SELECT TOP 5 ([Assets].[Asset ]) AS Printer, Assets.Barcode, Assets.[Serial #], Assets.User, Assets.[Asset Description], Assets.Location, Assets.Category, Assets.[Asset ]
    FROM Assets
    WHERE (((Assets.User)="new") AND ((Assets.Category)="Printer"))
    ORDER BY ([Assets].[Asset ]), Assets.[Asset ];
    
    
    SELECT TOP 5 ([Assets].[Asset ]) AS PC, Assets.Barcode, Assets.[Serial #], Assets.User, Assets.[Asset Description], Assets.Location, Assets.Category, Assets.[Asset ]
    FROM Assets
    WHERE (((Assets.User)="new") AND ((Assets.Category)="PC"))
    ORDER BY ([Assets].[Asset ]), Assets.[Asset ];
    
    
    SELECT TOP 5 (Assets.[Asset ]) AS Monitors, Assets.Barcode, Assets.[Serial #], Assets.User, Assets.[Asset Description], Assets.Location, Assets.Category, Assets.[Asset ]
    FROM Assets
    WHERE (((Assets.User)="new") AND ((Assets.Category)="monitor"))
    ORDER BY (Assets.[Asset ]), Assets.[Asset ];

    I need to combine these such that on a Form I can select 1 of each category (sometimes only 1 item is selected) to issue to a user. Any thoughts on how to do this?

    Thanks 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

    Monday, December 7, 2015 7:32 PM

Answers

  • Use subforms.

    Do you really have a space in field name Assets.[Asset ]?  

    Why does 'ORDER BY (Assets.[Asset ]), Assets.[Asset ];' have same field twice?

    Why did you select  Assets.[Asset ] twice?   


    Build a little, test a little

    • Marked as answer by KCDW Thursday, December 10, 2015 10:58 PM
    Monday, December 7, 2015 10:39 PM

All replies

  • Use subforms.

    Do you really have a space in field name Assets.[Asset ]?  

    Why does 'ORDER BY (Assets.[Asset ]), Assets.[Asset ];' have same field twice?

    Why did you select  Assets.[Asset ] twice?   


    Build a little, test a little

    • Marked as answer by KCDW Thursday, December 10, 2015 10:58 PM
    Monday, December 7, 2015 10:39 PM
  • 1. Use subforms.

    2. Do you really have a space in field name Assets.[Asset ]?  

    3. Why does 'ORDER BY (Assets.[Asset ]), Assets.[Asset ];' have same field twice?

    4. Why did you select  Assets.[Asset ] twice?   


    1) I think you are suggesting the use of a main Form with the user info on it, and then have three subforms where I select the equipment to assign to the user on the MainForm. Then I guess I would also need a subform to list all the equipment assigned to the user (datasheet view)?

    2) Yes, it is an inherited db that is one of the fixes coming up.

    3) Since there is an Alias calling the same Field that exists and each of them have an Ascending Sort...again changes are in the works.

    4) The first selection is an Alias while the second is not.

    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

    Monday, December 7, 2015 10:56 PM
  • 4) The first selection is an Alias while the second is not.

    The first selection HAS an alias, but the aliases are 'Printer,' 'PC,' and 'Monitor.'

    Build a little, test a little

    Tuesday, December 8, 2015 2:09 AM
  • Karl,

    • the first Query has the Alias ([Assets].[Asset ]) AS Printer
    • the second Query has the Alias ([Assets].[Asset ]) AS PC
    • the third Query has the Alias (Assets.[Asset ]) AS Monitors

    The first selection in each query is an Alias that identifies the type of equipment the asset # belongs to. This facilitates the user more than anything else. The second time each query calls for that Field it is not an Alias.

    Do I need to remove the Aliases to make this work the way I'm trying to make it work? I can do that if needed. I can just add a label on the Form to indicate the type of equipment. Is that what you would do?

    Do you have any comments about 1, 2 & 3?


    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 Tuesday, December 8, 2015 4:03 AM
    Tuesday, December 8, 2015 4:01 AM
  • This would do want you want --

    SELECT TOP 5 [Assets].[Asset ],"Printer" AS Type, Assets.Barcode, Assets.[Serial #], Assets.User, Assets.[Asset Description], Assets.Location, Assets.Category
    FROM Assets
    WHERE (((Assets.User)="new") AND ((Assets.Category)="Printer"))
    ORDER BY  Assets.[Asset ];


    Build a little, test a little

    Tuesday, December 8, 2015 3:40 PM
  • Good Morning Carl,

    Thanks for your response. Sorry I have been away for a while.

    I see what your SQL does however after working with it a bit I still cannot see how to combine the 3 Queries. Can you show how that would look please?

    Returned data might look like...

    Asset  Type Barcode Serial # User Asset Description Location Category
    1 Printer ## ## New ## Rm 1 Printer
    2 Printer ## ## New ## Rm 1 Printer
    3 Printer ## ## New ## Rm 1 Printer
    4 Printer ## ## New ## Rm 1 Printer
    5 Printer ## ## New ## Rm 1 Printer
    1 PC ## ## New ## Rm 14 PC
    2 PC ## ## New ## Rm 14 PC
    3 PC ## ## New ## Rm 14 PC
    4 PC ## ## New ## Rm 14 PC
    5 PC ## ## New ## Rm 14 PC
    1 Monitor ## ## New ## Rm 8 Monitor
    2 Monitor ## ## New ## Rm 8 Monitor
    3 Monitor ## ## New ## Rm 8 Monitor
    4 Monitor ## ## New ## Rm 8 Monitor
    5 Monitor ## ## New ## Rm 8 Monitor

    Although Since category is redundant to type...Lots to fix in this db

    Thanks again Karl!!!


    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, December 10, 2015 3:04 PM
  • The following only returns the top 5 of the three groups

    SELECT TOP 5 Assets.Category, Assets.[Asset ], Assets.Barcode, Assets.[Serial #], Assets.User, Assets.[Asset Description], Assets.Location
    FROM Assets
    WHERE (((Assets.Category)="Printer") AND ((Assets.User)="new")) OR (((Assets.Category)="PC") AND ((Assets.User)="new")) OR (((Assets.Category)="Monitor") AND ((Assets.User)="new"))
    ORDER BY Assets.[Asset ];

    I think the three have to be joined?


    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, December 10, 2015 3:24 PM
  • Think I got it. Does this look right?

    SELECT * FROM qryTop5NewPC;

    Union All

    SELECT * FROM qryTop5NewPrinters;

    Union All

    SELECT * FROM qryTop5NewMonitors;


    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, December 10, 2015 4:45 PM
  • I do believe that you must use the actual field names rather than an asterisk for all in a union query.

    Build a little, test a little

    Thursday, December 10, 2015 4:58 PM
  • Well it seems to be working in two Queries as follows without the specific Field Names.

    1. Laptops, Monitors & Docking Stations
    2. PC's & Monitors

    SELECT * FROM qryTop5NewLaptop;
    UNION ALL SELECT * FROM qryTop5NewMonitors;
    UNION ALL SELECT * FROM qryTop5NewDocSta;
    
    
    SELECT * FROM qryTop5NewPC;
    UNION ALL SELECT * FROM qryTop5NewMonitors;

    Thanks again Karl, you were very helpful!!!


    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, December 10, 2015 10:26 PM