Partition Issue

[Guest]

I have creaetd a query using a"partition" statement which splits data into
set ranges. I have several queries performing the same function to different
data. The idea is that I can then create a single query, which will show the
results for each sub-query.

The problem is that the results from the query ignore any ranges where there
is no values ( ie 10-20 2; 20-30 1;40-50 5 etc). This makes it difficult to
compare each of the partition queries.

Is there any way in which I can force Access to include a range , regardless
of whether there is any values with it?

I hope this makes sense

Phil

 

[John Vinson]

I have creaetd a query using a"partition" statement which splits data into
set ranges. I have several queries performing the same function to different
data. The idea is that I can then create a single query, which will show the
results for each sub-query.

The problem is that the results from the query ignore any ranges where there
is no values ( ie 10-20 2; 20-30 1;40-50 5 etc). This makes it difficult to
compare each of the partition queries.

Is there any way in which I can force Access to include a range , regardless
of whether there is any values with it?

One way to do this, that handles missing values and allows variable
sized partitions, is to use a range table with a "non equi join".
Consider a table named Ranges, with three fields - Low, High, and
Range. You can join this to your table to determine which range the
field N falls in with a query like

SELECT yourtable.N, <other fields>, Ranges.Range
FROM yourtable
RIGHT JOIN Ranges
ON Ranges.Low <= yourtable.N
AND Ranges.High > yourtable.N;

You need to be sure that Ranges doesn't have any gaps or overlaps.

John W. Vinson[MVP]

 

[Guest]

Thanks John, Ill give it a try!

One way to do this, that handles missing values and allows variable
sized partitions, is to use a range table with a "non equi join".
Consider a table named Ranges, with three fields - Low, High, and
Range. You can join this to your table to determine which range the
field N falls in with a query like

SELECT yourtable.N, <other fields>, Ranges.Range
FROM yourtable
RIGHT JOIN Ranges
ON Ranges.Low <= yourtable.N
AND Ranges.High > yourtable.N;

You need to be sure that Ranges doesn't have any gaps or overlaps.

John W. Vinson[MVP]