How to give a range with values?

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 result, now every item are under the same range.

So, could each item show its own range as I described in my 1st post? Thanks,

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
 
A

Alex

pemt said:
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 result, now every item are under the same range.

So, could each item show its own range as I described in my 1st post?
Thanks,

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
 

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

Top