The Doc Man
Thanks for your time.
My reports are to be generated from my query so I do not have to update the table as such. I wanted to bring out the general picture of what I want to achieve.
This is what I have now:
MyTable
ID Fa Fb Fc Fd Fe Low
1 4 7 1 2 6
2 3 4 7 8 5
MyTableQ
ID Fa Fb Fc Fd Fe Low
1 4 7 1 2 6
2 3 4 7 8 5
So with the query MyTableQ, the code becomes
SELECT MyTable.ID, MyTable.Fa, MyTable.Fb, MyTable.Fc, MyTable.Fd, MyTable.Fe, MyTable.Low
FROM MyTable;
MyUnion
ID Fx
1 1
1 2
1 4
1 6
1 7
2 3
2 4
2 5
2 7
2 8
Code for MyUnion becomes;(and gives the table above)
SELECT ID, Fa As Fx From MyTableQ
UNION
SELECT ID, Fb As Fx From MyTableQ
UNION
SELECT ID,Fc As Fx From MyTableQ
UNION
SELECT ID, Fd As Fx From MyTableQ
UNION SELECT ID,Fe As Fx From MyTableQ;
MyBottomTwo
ID Fx
1 1
1 2
Code for MyBottomTwo is; (and gives the table above .Still without results for ID=2)
SELECT TOP 2 MyUnion.ID, MyUnion.Fx
FROM MyUnion
ORDER BY MyUnion.ID, MyUnion.Fx;
MySum
ID SumFx
1 3
Code for my sum is; (Still without results for ID=2)
SELECT ID, SUM(Fx) AS SumFx
FROM MyBottomTwo
GROUP BY ID;
InsertSums
ID Fa Fb Fc Fd FE LOW
1 4 7 1 2 6 3
As per the new code supplied, the code now becomes;(and returns the above figures)
SELECT MyTableQ.ID, MyTableQ.Fa, MyTableQ.Fb, MyTableQ.Fc, MyTableQ.Fd, MyTableQ.FE, MySum.SumFX AS LOW
FROM MySum INNER JOIN MyTableQ ON MySum.ID=MyTableQ.ID;
So as you can see MyBottomTwo, MySum and InsertSums are all giving me expected results for ID=1 but are withholding results for ID=2
More help from you will be highly appreciated.