John,
I try to write another query (Query2) to use the one you wrote as Query1 as
following to convert collected ranges in each range of RangeFile1 into "1"
and out of range as "0". Because all range sizes are 100, I create a
CountNumber file with Field of CountNUM from 1 to 100.
Query2
SELECT [RangeFile1].[ITEM], [CountNumber].[CountNUM], IIf([CountNUM] Between
([RangeStart1]-([RangeEnd1]-100)) And ([RangeEnd1]-([RangeEnd1]-100)) And
[CountNUM] Between ([RngStart2]-([RangeEnd1]-100)) And
([RngEnd2]-([RangeEnd1]-100)),1,0) AS Item_Value
FROM CountNumber, RangeFile1 INNER JOIN Query1 ON
[RangeFile1].[ITEM]=[Query1].[Item]
WHERE ([CountNumber].[CountNUM]) Between 1 And 100
GROUP BY [RangeFile1].[ITEM], [CountNumber].[CountNUM], IIf([CountNUM]
Between ([RangeStart1]-([RangeEnd1]-100)) And ([RangeEnd1]-([RangeEnd1]-100))
And [CountNUM] Between ([RngStart2]-([RangeEnd1]-100)) And
([RngEnd2]-([RangeEnd1]-100)),1,0)
ORDER BY [RangeFile1].[ITEM];
I try to get results like the folowing, but it didn't work.
for the 1st range in RangeFile1
ITEM CountNUM Item_Value
ITEM1 1 0
ITEM1 2 0
...
ITEM1 20 1
...
ITEM1 30 1
ITEM1 31 0
...
ITEM1 56 1
...
ITEM1 88 1
ITEM1 89 0
...
ITEM1 100 0
By the way, if every range from RangeFile1 list in one table by next field,
how to do it?
like this;
1-100 200-300 800-900
...
ITEM CountNUM Item_Value1 Item_Value2 Item_Value3
ITEM1 1 0 0 1
ITEM1 2 0 0 1
...
ITEM1 20 1 0 0
...
ITEM1 30 1 0 0
ITEM1 31 0 0 0
...
ITEM1 56 1 0 0
...
ITEM1 88 1 0 0
ITEM1 89 0 0 0
...
ITEM1 100 0 0 1
Sorry for bothering you so much.
Thanks,
pemt
John Spencer said:
Well, I learned a lot through practice and a book called SQL for Mere
Mortals.
Despite the title I did find it tough going at times since the book does
not talk about Access SQL specifically.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
pemt wrote:
Sorry John,
one more question: what book do you recommend me to learn writing queries
for Access as a freshman? Thanks,
pemt
:
You can try the following. You might have to adjust the comparison
operators to include or exclude the = sign (> to >= or >= to >.
SELECT R1.Item
, RangeStart1 & "-" & RangeEnd1 as Range1
, IIF(RangeStart1<RangeStart2,RangeStart2,RangeStart1) as RngStart2
, IIF(RangeEnd1<RangeEnd2,RangeEnd1,RangeEnd2) as RngEnd2
FROM RangeFile1 as R1 INNER JOIN RangeFile2 as R2
ON R1.RangeStart1 <= R2.RangeEnd2
and R1.RangeEnd1 >= R2.RangeStart2
AND R1.Item = R2.Item
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
pemt wrote:
Dear all,
How to use queries to find a series of range files in another range?
There are two files:
RangeFile1:
ITEM RangeStart1 RangeEnd1
ITEM1 1 100
ITEM1 200 300
ITEM1 800 900
ITEM2 40 140
ITEM2 490 590
...
RangeFile2:
ITEM RangeStart2 RangeEnd2
ITEM1 20 30
ITEM1 56 88
ITEM1 105 122
ITEM1 298 330
ITEM1 208 234
ITEM1 750 806
ITEM2 41 55
ITEM2 127 141
ITEM2 500 501
...
Results:
ITEM RangeStart1-RangeEnd1 RangeStart2 RangeEnd2
ITEM1 1-100 20 30
ITEM1 1-100 56 88
ITEM1 200-300 298 300
ITEM1 200-300 208 234
ITEM1 800-900 800 806
ITEM2 40-140 41 55
ITEM2 40-140 127 140
ITEM2 490-590 500 501
...
As you can see, not all ranges in RangeFile2 will be covered by ranges in
RangeFile1. If it's partially covered,like 298-330 or 750-806 in RangeFile,
the results will show RangeEnd1 or RangeStart1 as their new ranges, becoming
298-300 or 800-806.
Thank you very much for your help.
pemt
