Running Sum in Union query

[Jonesy]

I have 12 tabs in Excel that are Linked in Access. I want to create a Union
query with a running sum to preserve the sort order.
SELECT *
FROM Period 1
WHERE Vin1>"1"

UNION SELECT *
FROM Period 2
WHERE Vin1>"1"

Should return...
Running
Sum Vin Table
1. B2345 Period 1
2. J7890 Period 2 etc..

Thanks in advance.

 

[Marshall Barton]

I have 12 tabs in Excel that are Linked in Access. I want to create a Union
query with a running sum to preserve the sort order.
SELECT *
FROM Period 1
WHERE Vin1>"1"

UNION SELECT *
FROM Period 2
WHERE Vin1>"1"

Should return...
Running
Sum Vin Table
1. B2345 Period 1
2. J7890 Period 2 etc..

One more example of the problems caused by unnormalized
tables.

Calculating a running value requires a unique sort order
across all the records. AFAIK, there is no way for one of
the Select queries in a union query to access the values in
another one of the Select queries. What that boils down to
is that you need to use a query that is based on the union
query to do the running value calculations. This kind of
arrangement is going to be about as fast as a turtle towing
a locomotive.

OTOH, since you must already have a unique sort order on
each Select and you know which select provided each record,
you can sort the entire union reslts with this approach:

SELECT 1 As Tno, *
FROM Period 1
WHERE Vin1>"1"

UNION SELECT 2, *
FROM Period 2
WHERE Vin1>"1"
. . .
ORDER BY Tno, sortingfield