Merge Tables with different intervals (from & to's)in Access?

[Cel]

I have to 2 tables with from & to's, they are not the same. How can I merge
the 2 tables for example 0-3 is A & 0.5-6 is B it must be 0-0.5 A, 0.5-3 AB &
3-6 B.

 

[John W. Vinson]

I have to 2 tables with from & to's, they are not the same. How can I merge
the 2 tables for example 0-3 is A & 0.5-6 is B it must be 0-0.5 A, 0.5-3 AB &
3-6 B.

I'm sorry, this is making no sense. 0-3 is not a table.

What are the actual fieldnames and datatypes in your tables?
What are some actual examples of the data in those fields?
Do you have a TEXT STRING

0.5-6

in a text field in a table? If so, Access does not see that as a range or as
numbers; it's just a black box containing five alphanumeric characters (a 0, a
.., a 5 and so on).

 

[vanderghast]

Untested.

Assuming

Activity FromThis ToThis ' field names
001 0 3 in tableA
001 0.5 6 in tableB


then, I would make a first query which will pump all the limits:

qLimits:
--------------
SELECT Activity, fromThis As limit FROM tableA
UNION
SELECT Activity, toThis FROM tableA
UNION
SELECT Activity, fromThis FROM tableB
UNION
SELECT Activity, toThis FROM tableB
-----------------

That should return

Activity Limit
001 0
001 .5
001 3
001 6





Next, a query which generates the appropriate limits, per activity, per
source


qGen
-------
SELECT qLimits.Activity, q.limit, "A" AS source
FROM tableA INNER JOIN qlimits
ON tableA.Activity = qlimits.activity
AND (qlimits.limit BETWEEN tableA.fromThis AND tableA.toThis)

UNION

SELECT qLimits.Activity, q.limit, "B"
FROM tableB INNER JOIN qlimits
ON tableB.Activity = qlimits.activity
AND (qlimits.limit BETWEEN tableB.fromThis AND tableB.toThis)
-----------------

which should return the records (not necessary in that row order) :

Activity Limit Source
001 0 A
001 .5 A
001 3 A
001 .5 B
001 3 B
001 6 B




The next step is to rank per activity, per source:

qRank:
Activity Limit Source Rank
001 0 A 1
001 .5 A 2
001 3 A 3
001 .5 B 1
001 3 B 2
001 6 B 3



And finally, to rebuild the sequences:
-------------------------
SELECT a.Activity, a.Source, a.Limit As Low, b.Limit As High
FROM qRank AS a INNER JOIN qRank AS b
ON a.activity = b.activity
AND a.source =b.source
AND a.rank+1 = b.rank
----------------------------
which returns (not necessary in that row order):

Activity Source Low High
001 A 0 0.5
001 A 0.5 3
001 B 0.5 3
001 B 3 6


Which is read: Activity 001 occurs only in A from 0 to 0.5; in A and in B
from 0.5 to 3; only in B from 3 to 6.

Maybe a crosstab is preferable for consultation:
--------------
TRANSFORM iif( 0<>COUNT(*), "x", "- ")
SELECT Activity, Source
FROM previousQuery
GROUP BY activity, source
PIVOT Low & "-" & High
-------------

which should produce:

Activity Source 0-0.5 0.5-3 3-6
001 A x x -
001 B - x x


where x indicates a presence, and - an absence.



I did not supply the SQL statement for qRank... well, use your favorite, or
the following:


qRank:
----------
SELECT a.activity, a.Limit, a.source, COUNT(*) As rank
FROM qGen As a INNER JOIN qGen As b
ON a.activity = b.activity
AND a.source = b.source
AND a.limit >= b.limit
GROUP BY a.activity, a.limnit, a.source