Query Assist please combining 3 queries for system installs

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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