How To Sort a Union Query SQL Statement for a Report?!?

[sxschech]

OK, add a table, I called it PartSort, but you can change it if you have a different name in mind. I put in 10 digit increments, so if you want to add stuff between you have some room to do that without changing the other sort numbers.

PartType    PartSort
Battery       10
Air Filter    20
Fuel Filter   30
Oil Filter    40
Deck Belt     50
Hydro Belt    60
PTO Belt      70

Here is the modified qryPartsUsageUnion Query

SELECT PartSort.PartSort, USAGE.*
FROM (SELECT * FROM sbqryUseBattery
UNION
SELECT * FROM sbqryUseBeltsDeck
UNION
SELECT * FROM sbqryUseBeltsHydro
UNION
SELECT * FROM sbqryUseBeltsPTO
UNION
SELECT * FROM sbqryUseFiltersAir
UNION
SELECT * FROM sbqryUseFiltersFuel
UNION SELECT * FROM sbqryUseFiltersOil
WHERE PartType IN ('Belts', 'Battery', 'Filters')
)  AS [USAGE] LEFT JOIN PARTSORT ON USAGE.PartType = PARTSORT.PartType
ORDER BY PartSort.PartSort;

Then in your report, in the Sort, change the first group sort to PartSort.

 

[dgaletar]

THAT IS ONE OF THE MOST BEAUTIFUL THINGS I HAVE EVER SEEN!!! I, in no way, can believe that you stuck with this STUPID issue as long as you did.

THANK YOU!!!!!!!!!!

PROBLEM SOLVED!

DG

 

[dgaletar]

BTW, SXSCHECH, are you any good with password protecting a database??? I've never done anything like this before. But I want to put it on a shared drive. I want myself to be able to do anything (of course), my co-worker Paul to be able to ONLY enter information, and my boss to ONLY be able to view everything (form/report wise).

I found this post (http://www.mrexcel.com/forum/microsoft-access/248191-tutorial-access-menu-permissions-system.html), but I tried it and it didn't work at all! I just got a bunch of errors whenever I tried to do anything, so I deleted the database and re-used an earlier version.

 

[sxschech]

Good to know that the solution worked for you. As for passwords and permissions, I haven't done much on that as I mainly work on reports and the forms and data etc are for running reports rather than saying who can do what. Perhaps someone else will be better able to address that issue.

 

[dgaletar]

No problem. Thanks again for all the hard work. Talk to you later...