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
"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