Using conditions in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two table I need to do a comparison against. They are both formatted
with the same spec so they have the identically labeled fields. I have to
show the matching records from the two table, which isn't the problem. Out of
the matching records I have to use the lesser of the two tables value for
the Quanitity fields for each record. I would think that an IF statement
would probably work or is there a way to use the <or>? Thanks in advance.
 
I'm not sure that this is what you are looking for, but try this

SELECT Table1.KeyField, Table2.Quanitity , Table1.Quanitity
FROM Table2 INNER JOIN Table1 ON Table2.KeyField = Table1.KeyField
Where Table2.Quanitity <> Table1.Quanitity
 
I think the function you want to use is the IIF() function.


of course you will need to make sure there are no nulls in the values and/or
protect yourself with a nz(value,x) function to handle the null values.

(what if value1 is NULL but value2 is 100 would you want 100 or 0 or null??)

in your query design pane enter something like the following:

ValueReported:
iif([table1]![value]<=[table2]![value],[table1]![value],[table2]![value])
This asks the following question:
"If the value in table1 is <= to the value in table2 then use the table1
value, otherwise use the table2 value"

Ed Warren
 
Back
Top