Determining sum of n fields in a query row (1 Viewer)

[Kitoned]

I would like to be helped to write a code in VBA that can sum Lowest 2 values in a query of 5 field values, and have this sum placed in a separate field "Low" for each row.

qryLower2:
ID ; Fa=1 ; Fb=2 ; Fc=3 ; Fd=4 ; Fe=5 ; Low=3 i.e Low=Fa + Fb

 

[NauticalGent]

Hello Kitoned...

Unless VBA is absolutely needed, you can achieve the results you want with two very simple queries.

The first one would return your two lowest values and the second would sum them.

Give this a read...and if you would like some assistance with building the queries, I can help with that.

https://support.office.com/en-us/ar...or-field-379FFDAE-9869-4971-9C32-3D0BD1A95B07

Best of luck!

 

[Kitoned]

Thanks NauticalGent.
I have no problem with; -Ranking, Summing Top "n" or Bottom "n" in column Data. I have a problem summing Row data. I have visited the site you refered me to but I have not figured out how to sum up bottom "n" for data Rowise.

 

[NauticalGent]

Ok...so if you are able to write the query that returns the bottom n, then use that query to write the query that sums the column.

 

[sxschech]

Found this:
https://bytes.com/topic/access/answers/197311-need-sql-query-determine-max-value-fields-row

It gets the highest, so you would flip the "<" symbol to get the lowest. But as is won't help you get the next lowest. You may need to add another function to rerun and exclude the actual lowest this function returns. Another suggestion on that post was to use union query.


extracted relevant portion of posting:

iMax(Field1, Field2, Field3, Field4, Field5...)

Public Function iMax(ParamArray p()) As Variant
' Idea from Trevor Best in Usenet MessageID
' ri********************************@4ax.com
Dim i As Long
Dim v As Variant

v = p(LBound(p))
For i = LBound(p) + 1 To UBound(p)
If v < p(i) Then
v = p(i)
End If
Next
iMax = v
End Function

 

[The_Doc_Man]

I have a problem summing Row data.

A query won't do this other than through brute-force methods. Access is record-oriented (in Excel-speak, COLUMN oriented.) Queries don't do rows.

The ONLY way to do this kind of thing in Access is to write manual expressions involving if/then/else/endif sequences.

You are not dealing with normalized data here, which is the start of your problem. This is obviously a translation from Excel. But just remember that Excel and Access are vastly different. Excel can do row-oriented math as well as column-oriented math but it has trouble with relationships that exceed two-dimensional thinking.

 

[Kitoned]

Thanks NauticalGent. I want to sum rows.

Thanks sxschech the refered to web site failed to open but lets see whether I shall be able to tame your code to what I want. Extra help is welcome.

Thanks very much The Doc Man It is true I want to be able to select from row data and then be able to sum the selection. Your extra help will be of great importance.

 

[The_Doc_Man]

To be honest, even the VBA for this would be ugly. To do this technically correctly, you would have to convert the inputs after which you could do several SQL-based queries in a sequence. However, for it to work right, the "Fx" fields cannot be on the same row. You would have to change your structure radically so that you had a series of derived records

ID1, Fa
ID1, Fb
ID1, Fc
ID1, Fd
ID1, Fe
ID2, Fa
ID2, Fb... etc.

Where of course ID is the same for the five related values (so that you know they are related). Then you could write a query for the TOP 2 Fx values in DESCENDING order (which is the same as the BOTTOM values in ASCENDING order, but Access doesn't have a BOTTOM 2 syntax option). You would use a GROUP BY ID so that your result would be two records for each ID. After that you could write a query of a query to do the sums, again grouped by ID. The second query would then display your sums of the two lowest values for each of your original rows.

However, to do this in VBA, you are looking at a very complicated operation, perhaps a shell sort algorithm. It has been AGES since I did one of those.

The difficulty here is that some structures lend themselves very easily to Access methods. This is a case where your structure doesn't fit the mold. This is where the differences in Excel and Access really get different. But to be honest, doing this with Excel and VBA would be no better. You still have to determine the per-record or per-row minimums in order to complete the computation. A general Excel data sort is going to be no better than Access in this case.

It occurs to me that as long as you have a very limited number of Fx values (5 in this case) you COULD get to this in a crazy approach with SQL and some UNION queries.

Query MyUnion:


SELECT ID, Fa As Fx From MyTable
UNION
SELECT ID, Fb As Fx From MyTable
UNION
SELECT ID, Fc As Fx From MyTable
UNION
SELECT ID, Fd As Fx From MyTable
UNION
SELECT ID, Fe As Fx From MyTable ;

Query MyBottomTwo:

SELECT TOP 2 ID, Fx FROM MyUnion ORDER BY Fx DESC GROUP BY ID ;

Query MySum:

SELECT ID, SUM(Fx) As SumFx FROM MyBottomTwo GROUP BY ID ;

Query InsertSums:

UPDATE MyTable INNER JOIN MySum ON MyTable.ID = MySum.ID SET Low=SumFx

Then just execute query InsertSums. Since it references MySum, it forces that to run. But MySum references MyBottomTwo, so that gets invisibly run. But in turn, that references the MyUnion query, which is how you might divide this up into the right "virtual" structure.

Doing this with VBA would be ugly too. But this approach SHOULD work. Play with it for a while before you trust it completely because I was "shooting from the hip."

 

[Kitoned]

Hi The Doc Man
I Have created the queries as per the code you attached.
MyUnion works right
MyBottomTwo gives an error; "syntax error in ORDER BY clause"
MySum gives an error; "you tried to execute a query that does not include the specified expression 'Fx' as part of an aggregate function.
InsertSums gives an error; "operation must use an updatable query"

To be honnest I have tried my level best to tame this code but the best I could come up with is result for only one row. With ID=1, ID=2, ID=3, etc SumFx for ID=1 only is returned without the rest. I cannot figure out why GROUP BY ID is not working right. I am using access 2007. Could it be the reason why.

I really still need your help because I feel I am only an inch away from the solution.

Thanks in advance.

I Shall welcome any extra help from any other member of the forum.

 

[The_Doc_Man]

Like I said, I was shooting from the hip. Also, it was late and I was suffering from extreme caffeine depletion.

SELECT TOP 2 ID, Fx FROM MyUnion ORDER BY Fx DESC GROUP BY ID ;

Perhaps should be

SELECT TOP 2 ID, Fx FROM MyUnion ORDER BY ID, Fx DESC ;

My foggy brain probably thought you needed a GROUP BY for this one but you need an ORDER BY for both fields. It is the MySum query that needs the grouping because the aggregates require it. The "TOP 2" option doesn't need grouping and probably doesn't like it anyway.

I think the error in the MySum is because of the problem with MyBottomTwo. The query I originally showed there would have produced a mess.

I'll have to think about InsertSums if you really need the table updated. But here's a question. Does that sum actually need to be in the table? Remember that a query can often stand in for a table, particularly if the information is merely for display or reporting purposes. If you can use a query, perhaps you don't need to update the table.

If so, then you could use

SELECT MT.ID, MT.Fa, MT.Fb, MT.Fc, MT.Fd, MT.FE, MS.SumFX AS LOW
FROM MyTable AS MT INNER JOIN MySum AS MS 
ON MT.ID = MS.ID ;

This would not be an updatable query, so it might not be useful for a form, but reports should not have any trouble with it.

 

[Kitoned]

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.