sorting union query

Question

hi

I have a combo box from a union query of four tables the sql currently looks like this

how can I auto arrange the data alphabetically/number please

SELECT Labour.Item, Labour.[Purchase Price]

FROM Labour;

UNION ALL SELECT Materials.Item, Materials.[Current Value]

FROM Materials;

UNION ALL SELECT Prelims.Item, Prelims.[Current Value]

FROM Prelims;

UNION ALL SELECT AllInRates.Item, AllInRates.[Total]

FROM AllInRates;

thankyou

Answer 1

Union queries get their field names from the first subselect. In other words, the fields in your query will be [Item] and [Purchase Price]. You should be able to use either

SELECT Labour.Item, Labour.[Purchase Price]

FROM Labour

UNION ALL SELECT Materials.Item, Materials.[Current Value]

FROM Materials

UNION ALL SELECT Prelims.Item, Prelims.[Current Value]

FROM Prelims

UNION ALL SELECT AllInRates.Item, AllInRates.[Total]

FROM AllInRates

ORDER BY Item

or

SELECT Labour.Item, Labour.[Purchase Price]

FROM Labour

UNION ALL SELECT Materials.Item, Materials.[Current Value]

FROM Materials

UNION ALL SELECT Prelims.Item, Prelims.[Current Value]

FROM Prelims

UNION ALL SELECT AllInRates.Item, AllInRates.[Total]

FROM AllInRates

ORDER BY 1

Note, btw, that the original query you posted was invalid: you cannot have semi-colons inside the SQL.

Answer 2

is there now way to do it by the combined tableds

I do not understand the question.   

What I posted will sort the complete record set on the field(s) in the ORDER BY statement.

Answer 3

Try this --

SELECT Labour.Item, Labour.[Purchase Price]

FROM Labour

ORDER BY Labour.Item

UNION ALL SELECT Materials.Item, Materials.[Current Value] 

FROM Materials

UNION ALL SELECT Prelims.Item, Prelims.[Current Value] 

FROM Prelims

UNION ALL SELECT AllInRates.Item, AllInRates.[Total] 

FROM AllInRates;

It will only sort by what is in the first part of the union.

thanks Karl

is there now way to do it by the combined tableds

Answer 4

Try this --

SELECT Labour.Item, Labour.[Purchase Price]

FROM Labour

ORDER BY Labour.Item

UNION ALL SELECT Materials.Item, Materials.[Current Value] 

FROM Materials

UNION ALL SELECT Prelims.Item, Prelims.[Current Value] 

FROM Prelims

UNION ALL SELECT AllInRates.Item, AllInRates.[Total] 

FROM AllInRates;

It will only sort by what is in the first part of the union.