How to give values to a range?

P

pemt

Dear all,

there are two files. Both are range files.
RangeFile1:
ITEM RangeStart1 RangeEnd1
ITEM1 1 100
ITEM2 20 200
ITEM3 2 12

RangeFile2:
ITEM RangeStart2 RangeEnd2
ITEM1 10 20
ITEM1 50 66
ITEM1 88 92
ITEM2 10 30
ITEM3 4 8
ITEM3 10 18

Results:
Range 1 2 3 4 5 6 7 8 9 10 11 12 ...20 21... 50 51...66 67...87 88...91 92
93....100
ITEM1 0 0 0 0 0 0 0 0 0 1 1 1 ... 1 0 ... 1 1... 1 0 ... 0
1... 1 1 0 .... 0

Range 20 21 22 ... 29 30 31 32 ... 200
ITEM2 1 1 1 ... 1 1 0 0 ... 0

Range 2 3 4 5 6 7 8 9 10 11 12
ITEM3 0 0 1 1 1 1 1 0 1 1 1

As you can see, every number from each item range in RangeFile1 will be
given a number of "1" or "0" based on RangeFile2.
How to do it?

Wish your great help again.
Thanks,

pemt
 
K

KARL DEWEY

Create a table named CountNumber with field CountNUM containing numbers 1
through 100.
Use these queries --
Pemt_1 ---
SELECT RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0) AS Item_Value
FROM CountNumber, RangeFile1 INNER JOIN RangeFile2 ON
RangeFile1.ITEM=RangeFile2.ITEM
WHERE (((CountNumber.CountNUM) Between 1 And 100))
GROUP BY RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0)
ORDER BY RangeFile1.ITEM;

Pemt_2 ---
SELECT Pemt_1.ITEM, Pemt_1.CountNUM AS Range, Max(Pemt_1.Item_Value) AS
MaxOfItem_Value
FROM Pemt_1
GROUP BY Pemt_1.ITEM, Pemt_1.CountNUM
ORDER BY Pemt_1.ITEM;

TRANSFORM First(Pemt_2.[MaxOfItem_Value]) AS FirstOfMaxOfItem_Value
SELECT Pemt_2.[ITEM], First(Pemt_2.[MaxOfItem_Value]) AS [Total Of
MaxOfItem_Value]
FROM Pemt_2
GROUP BY Pemt_2.[ITEM]
PIVOT Pemt_2.[Range];
 
P

pemt

Karl,

Thanks a lot! It works beautifully.

pemt

KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing numbers 1
through 100.
Use these queries --
Pemt_1 ---
SELECT RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0) AS Item_Value
FROM CountNumber, RangeFile1 INNER JOIN RangeFile2 ON
RangeFile1.ITEM=RangeFile2.ITEM
WHERE (((CountNumber.CountNUM) Between 1 And 100))
GROUP BY RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0)
ORDER BY RangeFile1.ITEM;

Pemt_2 ---
SELECT Pemt_1.ITEM, Pemt_1.CountNUM AS Range, Max(Pemt_1.Item_Value) AS
MaxOfItem_Value
FROM Pemt_1
GROUP BY Pemt_1.ITEM, Pemt_1.CountNUM
ORDER BY Pemt_1.ITEM;

TRANSFORM First(Pemt_2.[MaxOfItem_Value]) AS FirstOfMaxOfItem_Value
SELECT Pemt_2.[ITEM], First(Pemt_2.[MaxOfItem_Value]) AS [Total Of
MaxOfItem_Value]
FROM Pemt_2
GROUP BY Pemt_2.[ITEM]
PIVOT Pemt_2.[Range];

--
Build a little, test a little.


pemt said:
Dear all,

there are two files. Both are range files.
RangeFile1:
ITEM RangeStart1 RangeEnd1
ITEM1 1 100
ITEM2 20 200
ITEM3 2 12

RangeFile2:
ITEM RangeStart2 RangeEnd2
ITEM1 10 20
ITEM1 50 66
ITEM1 88 92
ITEM2 10 30
ITEM3 4 8
ITEM3 10 18

Results:
Range 1 2 3 4 5 6 7 8 9 10 11 12 ...20 21... 50 51...66 67...87 88...91 92
93....100
ITEM1 0 0 0 0 0 0 0 0 0 1 1 1 ... 1 0 ... 1 1... 1 0 ... 0
1... 1 1 0 .... 0

Range 20 21 22 ... 29 30 31 32 ... 200
ITEM2 1 1 1 ... 1 1 0 0 ... 0

Range 2 3 4 5 6 7 8 9 10 11 12
ITEM3 0 0 1 1 1 1 1 0 1 1 1

As you can see, every number from each item range in RangeFile1 will be
given a number of "1" or "0" based on RangeFile2.
How to do it?

Wish your great help again.
Thanks,

pemt
Click to expand...
Click to expand...
 
P

pemt

Sorry Karl,

Too excited. For ITEM1, it works beautiully, but not for ITEM2 or others,
because each item has different ranges in RangeFile1.

So, is it possible to generate a table file of CountNumber including all
ranges from each item based on separate coding? And, eventually, the results
will show individual ranges for each item. Now it only showes the range of
1-100 for all items.

Really appreciate your great help.

pemt

KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing numbers 1
through 100.
Use these queries --
Pemt_1 ---
SELECT RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0) AS Item_Value
FROM CountNumber, RangeFile1 INNER JOIN RangeFile2 ON
RangeFile1.ITEM=RangeFile2.ITEM
WHERE (((CountNumber.CountNUM) Between 1 And 100))
GROUP BY RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0)
ORDER BY RangeFile1.ITEM;

Pemt_2 ---
SELECT Pemt_1.ITEM, Pemt_1.CountNUM AS Range, Max(Pemt_1.Item_Value) AS
MaxOfItem_Value
FROM Pemt_1
GROUP BY Pemt_1.ITEM, Pemt_1.CountNUM
ORDER BY Pemt_1.ITEM;

TRANSFORM First(Pemt_2.[MaxOfItem_Value]) AS FirstOfMaxOfItem_Value
SELECT Pemt_2.[ITEM], First(Pemt_2.[MaxOfItem_Value]) AS [Total Of
MaxOfItem_Value]
FROM Pemt_2
GROUP BY Pemt_2.[ITEM]
PIVOT Pemt_2.[Range];

--
Build a little, test a little.


pemt said:
Dear all,

there are two files. Both are range files.
RangeFile1:
ITEM RangeStart1 RangeEnd1
ITEM1 1 100
ITEM2 20 200
ITEM3 2 12

RangeFile2:
ITEM RangeStart2 RangeEnd2
ITEM1 10 20
ITEM1 50 66
ITEM1 88 92
ITEM2 10 30
ITEM3 4 8
ITEM3 10 18

Results:
Range 1 2 3 4 5 6 7 8 9 10 11 12 ...20 21... 50 51...66 67...87 88...91 92
93....100
ITEM1 0 0 0 0 0 0 0 0 0 1 1 1 ... 1 0 ... 1 1... 1 0 ... 0
1... 1 1 0 .... 0

Range 20 21 22 ... 29 30 31 32 ... 200
ITEM2 1 1 1 ... 1 1 0 0 ... 0

Range 2 3 4 5 6 7 8 9 10 11 12
ITEM3 0 0 1 1 1 1 1 0 1 1 1

As you can see, every number from each item range in RangeFile1 will be
given a number of "1" or "0" based on RangeFile2.
How to do it?

Wish your great help again.
Thanks,

pemt
Click to expand...
Click to expand...
 
P

pemt

I change "WHERE (((CountNumber.CountNUM) Between 1 And 100))" in Pemt_1 to
"WHERE ((([CountNumber].[CountNUM]) Between ([RangeFile1].[RangeStart1]) And
([RangeFile1].[RangeEnd1])))".
The results look better. For example, ITEM3 only shows its own range of
2-12. But ITEM2 doesn't, because it still relies on the CountNumber file.
If I have ITEM4 ranges from 200-400, ITEM4 won't show in the result at all.
In the reslut, now every item are under the same range. Could each item show
its own range as I described in my 1st post?
Results:
Range 1 2 3 4 5 6 7 8 9 10 11 12 ...20 21... 50 51...66 67...87 88...91 92
93....100
ITEM1 0 0 0 0 0 0 0 0 0 1 1 1 ... 1 0 ... 1 1... 1 0 ... 0
1... 1 1 0 .... 0

Range 20 21 22 ... 29 30 31 32 ... 200
ITEM2 1 1 1 ... 1 1 0 0 ... 0

Range 2 3 4 5 6 7 8 9 10 11 12
ITEM3 0 0 1 1 1 1 1 0 1 1 1

pemt

KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing numbers 1
through 100.
Use these queries --
Pemt_1 ---
SELECT RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0) AS Item_Value
FROM CountNumber, RangeFile1 INNER JOIN RangeFile2 ON
RangeFile1.ITEM=RangeFile2.ITEM
WHERE (((CountNumber.CountNUM) Between 1 And 100))
GROUP BY RangeFile1.ITEM, CountNumber.CountNUM, IIf([CountNUM] Between
[RangeStart1] And [RangeEnd1] And [CountNUM] Between [RangeStart2] And
[RangeEnd2],1,0)
ORDER BY RangeFile1.ITEM;

Pemt_2 ---
SELECT Pemt_1.ITEM, Pemt_1.CountNUM AS Range, Max(Pemt_1.Item_Value) AS
MaxOfItem_Value
FROM Pemt_1
GROUP BY Pemt_1.ITEM, Pemt_1.CountNUM
ORDER BY Pemt_1.ITEM;

TRANSFORM First(Pemt_2.[MaxOfItem_Value]) AS FirstOfMaxOfItem_Value
SELECT Pemt_2.[ITEM], First(Pemt_2.[MaxOfItem_Value]) AS [Total Of
MaxOfItem_Value]
FROM Pemt_2
GROUP BY Pemt_2.[ITEM]
PIVOT Pemt_2.[Range];

--
Build a little, test a little.


pemt said:
Dear all,

there are two files. Both are range files.
RangeFile1:
ITEM RangeStart1 RangeEnd1
ITEM1 1 100
ITEM2 20 200
ITEM3 2 12

RangeFile2:
ITEM RangeStart2 RangeEnd2
ITEM1 10 20
ITEM1 50 66
ITEM1 88 92
ITEM2 10 30
ITEM3 4 8
ITEM3 10 18

Results:
Range 1 2 3 4 5 6 7 8 9 10 11 12 ...20 21... 50 51...66 67...87 88...91 92
93....100
ITEM1 0 0 0 0 0 0 0 0 0 1 1 1 ... 1 0 ... 1 1... 1 0 ... 0
1... 1 1 0 .... 0

Range 20 21 22 ... 29 30 31 32 ... 200
ITEM2 1 1 1 ... 1 1 0 0 ... 0

Range 2 3 4 5 6 7 8 9 10 11 12
ITEM3 0 0 1 1 1 1 1 0 1 1 1

As you can see, every number from each item range in RangeFile1 will be
given a number of "1" or "0" based on RangeFile2.
How to do it?

Wish your great help again.
Thanks,

pemt
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to give a range with values? 1
How to use queries to find a series of range files in another rang 10
How to give values to a range? 0
How to give a range with values? 2
How to give a value to a range of data? 0
how to combine each record into a range? 3
Copy/Paste? 8
Union query 8

Top