union queries

[olive]

Hi

I am generatin a union query based on wheel diameters previously in seperate fields. However when I create the union query the data is automatically sorted in ascending order and deleting records that contain repeated numbers.

For example, I want the data from the different fields to be entered in this order:
6
6
4
3
3
7
19
28

but the datasheet output automatically rearrages the data as:
3
4
6
7
19
28


How do I turn off both these properties?
Many thanks

 

[boblarson]

You really can't deal with the sorting unless you put in a sort field in your tables.

As for the duplicates, just change the word UNION between the select statements to UNION ALL

 

[olive]

Hey thanks,

The source isn't actaully a table, it's a query itself. The SQL code is:

SELECT (SUM([3CDiaLess530])) AS TableDia3car FROM qryDiameterAvg3car
UNION ALL SELECT (SUM([3CDia530to540])) FROM qryDiameterAvg3car
UNION ALL SELECT (SUM([3CDia540to550])) FROM qryDiameterAvg3car
UNION ALL SELECT (SUM([3CDia550to560])) FROM qryDiameterAvg3car
UNION ALL SELECT (SUM([3CDia560to570])) FROM qryDiameterAvg3car
UNION ALL SELECT (SUM([3CDia570to580])) FROM qryDiameterAvg3car
UNION ALL SELECT (SUM([3CDia580to590])) FROM qryDiameterAvg3car
UNION ALL SELECT (SUM([3CDia590to600])) FROM qryDiameterAvg3car;

I don't think I can place a sorting filed on a sum. Is there any way to specify which row each entry should fall into, like the "INSERT INTO" function, but used in a query?

Cheers

 

[WayneRyan]

Olive,

Your Union query, is just that a query. Even though it is a collection of Selects, it really is
just the Union of a bunch of similar data. You can control it (to some extent) when you're assembling
it by using the ALL predicate, but it really is just a "random" collection of information.

As it is, you can use IT as a source and query it for sorting, grouping, Distinct operations and so on.

If you REALLY had to preserve the initial "chronological" order, you'd have to do the initial collection
into a table with an Identity column (Autonumber) to track their order. Even a Date/Time stamp wouldn't
do. Even then, you'd still have to process it with the logic to group, sort it, or whatever.

Wayne

 

[namliam]

Why split off your question from here: http://www.access-programmers.co.uk/forums/showthread.php?p=691964#post691964

???

You are all over the place

 

[boblarson]

Why split off your question from here: http://www.access-programmers.co.uk/forums/showthread.php?p=691964#post691964

???

You are all over the place

That's for sure Nam... I think I saw a total of 3 threads started about this same issue. Maybe I should merge them all but then the conversations might get confusing.

 

[namliam]

Hi Bob,

Perhaps make clear to Olive that Popeye and Brutus dont like multiple threads on the same question??

I agree it is going to be hell to merge the threads tho...