Query problem - greater value required

[funkmiesterk]

Hi everyone.

What I currently have is 2 columns in one table 1 is required and 1 isnt.

However what i need to do is work out the sum value of the greater amount from each line.

IE
A B
1 2
1 0
2 1

which would give a total of 5 as its adding B1+A2+A3

I tried creating a 3rd columb and entered this IF Formula but apparently access doesnt support If formulas

=If([claims]![Mileage1 IR]>[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])

my plan was to get the "IF column to show me the higher amounts which could then be totalled up.

Any ideas where I may be going wrong?

Kev

 

[Mike375]

YourNewFiledName:IIF([A]>,[A],)

On your example that would create a new column with the following results

A B YourNewFieldName
1 2 2
1 0 1
2 1 2

So you can now total the field YourNewFieldName

 

[funkmiesterk]

ill give it a spin now.

If it works ur next rounds on me!

kev

 

[neileg]

'Iif' is correct for queries.
'If' is correct for VBA code.

 

[funkmiesterk]

no success i get the following message

"The expression you entered has an invalid . (dot) or ! operator or invalid parentheses."

here is the formula i entered

IF:IIF( [claims]![Mileage1 IR] > [claims]![Mileage1] , [claims]![Mileage1 IR] , [claims]![Mileage1] )

and it highlights the open bracket.

any ideas?

Kev

 

[Mike375]

Don't us IF as a field name.

FieldnameYouCreate:IIF([SumOfMileage1 IR]>«Expr» «Expr»[SumOfMileage1],[SumOfMileage1 IR],[SumOfMileage1])

There should be no spaces between brackets, commas. This is for a calculated field in a query, which based on your original post sound like what is required

Mine will not accept the above because of the <<expr>>

Firstly, this FieldName:IIf([A]>,10,63) works. The first part...[A]> is either true or false and according which is true you get either 10 or 60

It could also be [A]= or [A] Like "xyx"

It can aslo involve And/Or in the first part but you must repeat then as in...

IIf([Mileage]>50 Or [PetrolPrice]<[Caltex]
IIf([Mileage]>50 And [PetrolPrice]<[Caltex]

Depending on what you have you may need to add other brackets of this type (()) and that might mean extra brackets at the very end.

You can enter expressions as part of the criteria and in fact IIF functions themselves can be part of the criteria.

 

[funkmiesterk]

Sorry to bump this but I didnt work over the weekend

[claims]![FieldName]:IIf([claims]![Mileage1 IR]>=[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])

entered that (added the equals too incase the numbers are the same) and it comes up with the error

The expression you entered contains invalid syntax. You may have entered and operand without an operator. and it highlights the : next to the IIF.

Any ideas cos this is basically the last bit i need to do before i release this database!

Kev

 

[neileg]

The [claims]![fieldname] bit is wrong. Your formula is correct for a calculated field.

Typically, this would appear as
MileageValue:IIf([claims]![Mileage1 IR]>=[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])

You can't use this approach to amend the value of an existing field in the table. You need an update query for this

 

[funkmiesterk]

right because im getting totally lost i have pulled the table and query into a seperate database and have uploaded it here : http://files.ukcs.net/11308/db1.mdb

if you run the query it will ask for a batching number its 0609.

The totals will show Sumofmileage1 - 1447 and sumofmileageIR - 999...

can someone figure out a way to do the formula to get the correct IR (which should be 1477 because there is only one IR claim with 30 more miles on it)

If you can please post the formula directly onto the forum I would be most grateful!

Kev

 

[Mike375]

I am not fully understanding what you after.

Mileage1 Mileage1 IR
69 0
48 0
109 0
200 230
208 208
14 14
547 547
252 0

In your example your query is adding the colums and Mileage 1 is 1447 and Mileage 1 IR is 999 and you want 1447 as the answer from IIF.

What is the criteria for 1447 to be selected. Is it because Mileage 1 IR has one value bigger than Mileage 1.....If that is the situation then it is very easy for IIFto produce a field that will pick that out. For example the created field produced by IFF could have 230 and a 0 or a Null for the other records. Once you have that you can move on from there. It appears that the total of the column would not be a criteria.

 

[funkmiesterk]

Hi mate

Well all the first column does is takes a total of their journeys, the second total includes their home to work mileage and we have to record that info.

All i need to do is get a query that just finds the highest figure from either of the 2 columns and then adds those together to get a total. I can make it so both default value is null if that makes it any easier.

Sorry for the delayed response!

Kev

 

[Mike375]

Perhaps the attached is the idea you are looking for.

Query1 has taken the highest value from the fields a and b

Query2 has added the two highest values

If you then wanted to know which "high value" from each field was the highest value then the IIF will ge that....

Is the criteria you outlined in your first post still valid?

 

[Mike375]

I forgot to add.

If you put xyz: [MaxOfa]+[MaxOfb] as a field in Query1 and select expression on the grid row, same as where Max is for a and b then you get the result from one query.