Hello. I have a Union Query (that works perfectly fine) with the following code:
Code:
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;
I am using this information on a Report.
The problem is that the Report shows the data in random order. Is there a way to filter either the Union Query or the Report???
You can do filtering and sorting on the report. On the report design view, if the sorting and grouping window isn't visible, click on the Group & Sort button in the Grouping & Totals section of the ribbon (Access 2010).
If you want to sort the union query, before the semicolon, add a new line and type in
ORDER BY FIELDNAME
use the actual field name that you want to sort by, or the position number. For position number, say that you want to sort by City and City is the third field to display in your list, you would say ORDER BY 3.
Lastly, if you want to filter the union query itself, you can add a SELECT () as follows.
Code:
SELECT *
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 FIELDNAME = 'FILTERITEM';
Again, replace FIELDNAME and FILTERITEM with the actual name of the field and what you want filtered. Also, when you use this method, you can view the query in Design view as well as SQL view, so if you are more comfortable in design view that will be an option.
When I select "Group, Sort & Total", I want to show the belts first, so under "Group on PartType", I select "with title click to add" and add "Belts". Then, when I run the report, EVERY PART TYPE HAS "Belts" LISTED UNDER IT!
If you want to sort the union query, before the semicolon, add a new line and type in
ORDER BY FIELDNAME
use the actual field name that you want to sort by, or the position number. For position number, say that you want to sort by City and City is the third field to display in your list, you would say ORDER BY 3.
Now when I do this one, I get several errors... all probably meaning the same thing. I changed the SQL to read like this:
Code:
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
ORDER BY Belts;
...and I get the following error:
The ORDER BY expression (Belts) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.
I think that the problem here is that, in my sub query's I used a field titled "PartType". The "PartType" field tells us whether it is a belt, a filter, or a battery. So sorting that in the query would probably have to be done differently.
You know... maybe I'm using the wrong verbage here!!! I think that what I am trying to do is GROUP the PartTypes in the report. No, I guess that I want to filter them also.
Since we didn't know your fieldnames or values, the previous explanation was generic. What your are sorting on is the name of the field, not the contents. So if your field is called PartType, then you would ORDER BY PartType. Similarly, for the filter, if all you want is Belts, you would in the query say WHERE PartType = "Belts". Of course, if you use the WHERE and the only value is Belts, you wouldn't need the ORDER BY in the above example. I left it in the example so you can see how it would look. If there are subsorts under belts, then you could order by that instead. Below is an example with the WHERE clause as a Filter for Belts. If you wanted to filter on more items it would be something like WHERE PartType IN ('Belts', 'Battery'). Of course, the spelling is important, so if your part type is Batteries instead of Battery, then the above would need to change to that instead.
Code:
SELECT *
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 = "Belts"
ORDER BY PartType;
If you need further assistance/explanation, let me know.
OK, since my goal is to have my report display in some type of order, I used the following code:
Code:
SELECT *
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')
ORDER BY PartType;
And when I ran the query, I got "Syntax error in FROM clause.
Any suggestions??? :banghead:
UPDATE!!!
So I'm just trying to close the SQL so that I can move on to my next issue, but it won't let me. So I start messing around with the SQL statement just to get it to close, and WHAMO!... I end up with this:
Code:
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')
ORDER BY PartType;
I run the query and everything is now grouped together! And that's all I ever wanted! So... THANKS!!!
You may also need to add a sort in your report as explained in the first post. I have Access 2010, so that is what I'm basing on. If the Group, Sort and Total window is visible, you should see a button says Add a Sort. Click that and choose the field you want to sort by, I assume PartType is what you plan to sort on.
OK, so I finally figured out that I had to scroll down on the screen to see the button that says Add a Sort. But, when I added "PartType" to it, nothing happened.
Sorting the query isn't going to solve the problem. Reports completely rewrite your query and do not copy the order by clause. They use whatever is specified in the report's sorting and grouping options.
When you make a union query, the column names are derived from the first table in the union. So if sbqryUseBattery has columns ID, BatteryName, Cost - those will be the column names - period. The column name can't change mid-stream.
Sorting the query isn't going to solve the problem. Reports completely rewrite your query and do not copy the order by clause. They use whatever is specified in the report's sorting and grouping options.
When you make a union query, the column names are derived from the first table in the union. So if sbqryUseBattery has columns ID, BatteryName, Cost - those will be the column names - period. The column name can't change mid-stream.
Pat has a great point here, but you CAN avoid the conflict by not using the "*", and identifying each of the Fields in the Select Statement instead.
You could assign an Alias (I like to use Aliases that are meaningful to me) to any Field that needs one. This should also get rid of your ORDER BY issue, since you could use the Alias Names of the Fields instead of the Column Names from the Tables.
If you would like to send a sample of the database, I could take a look at it.
With regards to the Alias as mentioned by Rookie, in your sql statement it would be something like the following, again, not knowing your field names, these are made up:
Code:
SELECT XYZ AS PART_NUMBER, CITY AS CITY, RUN_DATE AS LAST_DATE
FROM ABC
UNION
SELECT STU AS PART_NUMBER, TOWN AS CITY, REFRESH_DATE AS LAST_DATE
FROM DEF
As Pat mentioned, only the first set of field names in the Union query are displayed, so technically, you don't need to Alias the rest of the unioned select statements, but is useful if you want to see what is defined as what. If you know what is in the rest of the queries (same number of cols and the position that the cols appear in, you could write the statement like:
Code:
SELECT XYZ AS PART_NUMBER, CITY AS CITY, RUN_DATE AS LAST_DATE
FROM ABC
UNION
SELECT *
FROM DEF
I opened the report and it appears to be sorted alphabetically by Part Type, then sorted alphabetically by Part Brand.
Without typing out all the values, only the first letter the report shows A, B, D, F, H, P. Then within that for example, under F you have Ka and then Ku.
So not sure what kind of sort you are looking to do or is there some other issue that I did not understand based on your explanation?
I also noticed that your SQL statement for the union query has more than one semicolon. You should only have on at the end and not after each select statement. That is why you had a syntax error when you tried my original suggestion about wrapping the entire Union with (). Perhaps that was a result of copy paste from several queries into one when you originally created the union query?
Until now, I don't recall you mentioning how you wanted the report sorted so the assumption was alphabetical. Since you want the report sorted in a specific way, you will need to add logic to define the sort order. If you only have those items, you can do if statements, otherwise you may want to set up a table to have the name and a sortnumber. I'll do a quick example in the query and post it for you to try.
