Finding the larger of 2 values in a query

**jik_ff** · 08-19-2011, 02:59 PM

I have a query base off data from an ODBC source. Unfortunately, they messed with the tables (head office) and now I need to make some changes to my database.

My issue is that they use 1 of 2 fields now to track Life expectancy dates for equipment. The first (original one) is a scheduled refresh date. The other is a suggested refresh date. Needless to say, I need to poll which value is higher. I'd prefer to make a new field in the query, and have it pick which is the higher between the 2 (creating a new expression field in the query). Is this possible, and how?

**davegugg** · 08-19-2011, 03:23 PM

You can create a custom max function like this:

SELECT 0.5 * ((sched_refresh + sugg_refresh) + ABS(sched_refresh - sugg_refresh)) AS NewRefresh

Edit: If numbers are really big, you may pick up rounding errors.

**jik_ff** · 08-19-2011, 03:46 PM

Originally Posted by davegugg

You can create a custom max function like this:

SELECT 0.5 * ((sched_refresh + sugg_refresh) + ABS(sched_refresh - sugg_refresh)) AS NewRefresh

Edit: If numbers are really big, you may pick up rounding errors.

Can this be built in the query? (such as an expersion to create a new field)?

**davegugg** · 08-19-2011, 04:02 PM

Yes, you can see that's kind of how I set it up. I used generic field names and no tables since you did not provide any in your original post.

PS, no need to quote my post, I can scroll up and see it.

**jik_ff** · 08-19-2011, 04:07 PM

sorry, habbit. Would this work if one of the fields was null (no data entered). They are date fields, so I think the numbers would be fine?

Edit:

Also, I don't think I know where to put the code. A bit new to doing this. Is it put in the Criteria field? or is put in the Field field?

**davegugg** · 08-19-2011, 04:22 PM

I would think it'd work as long as both fields weren't null, but I'd check to be sure. Can you post your current query's sql, then I may be able to give you a better idea of where to put it.

**jik_ff** · 08-19-2011, 04:49 PM

I don't think it works with null, (also it doesn't like the SELECT at the front) as when I did this:
0.5 * (([SCHEDULED_RETURN_DATE] + [SUGGESTED_REFRESH_DATE]) + Abs ([SCHEDULED_RETURN_DATE] - [SUGGESTED_REFRESH_DATE]))

it came back blank. SCHEDULED_RETURN_DATE is empty in my test value.

I know it works though cause this:
0.5 * ((9 + 7) + Abs (9 - 7))
returns 9.

(BTW, doing this in Access 2007, not SQL)

**davegugg** · 08-19-2011, 05:44 PM

Ok, even in Access, there is SQL behind each query. You can change the view for the Query builder to SQL to get what the SQL behind the query is. I'm not sure if you can perform arithmetic on dates, you may have to use date-specific functions. I'll look into it, but probably not before Monday, so you could try Googling it if you want to find an answer before then.