Compare Min and Max value of a field

  • Thread starter Thread starter Agent_KGB
  • Start date Start date
A

Agent_KGB

I need to see if MIN and MAX value of a field match, I know how to do IIF
statement to compare them, but i can't figure out how to reffer to MIN and
MAX values of a field...

say i have [account] field and [credit card] field, and i want to see if i
have any accounts with more then one credit card, so in essence i am looking
to do somethin glike this:

IIF(Min[credit card]=Max[credit card], "Great", "Problem")

having problems with my refferences
 
Try this --
SELECT [account], IIF(Min([credit card]) = Max([credit card]), "Great",
"Problem") AS Credit_Card_Status
FROM SomeTable
GROUP BY [account];
 
I am still lost Karl...

would it make difference if my account and credit card fields are in two
ceparate tables (but have relationship in place)?

KARL DEWEY said:
Try this --
SELECT [account], IIF(Min([credit card]) = Max([credit card]), "Great",
"Problem") AS Credit_Card_Status
FROM SomeTable
GROUP BY [account];

--
Build a little, test a little.


Agent_KGB said:
I need to see if MIN and MAX value of a field match, I know how to do IIF
statement to compare them, but i can't figure out how to reffer to MIN and
MAX values of a field...

say i have [account] field and [credit card] field, and i want to see if i
have any accounts with more then one credit card, so in essence i am looking
to do somethin glike this:

IIF(Min[credit card]=Max[credit card], "Great", "Problem")

having problems with my refferences
Click to expand...
Click to expand...
 
Yes, you would have to join the tables if you wanted to show status for the
account.
Try this --
SELECT OtherTable.[account], IIF(Min([SomeTable].[credit card]) =
Max([SomeTable].[credit card]), "Great", "Problem") AS Credit_Card_Status
FROM SomeTable INNER JOIN OtherTable ON SomeTable.AcctID = OtherTable.AcctID
GROUP BY OtherTable.[account];

or how ever your two tables are related.

--
Build a little, test a little.


Agent_KGB said:
I am still lost Karl...

would it make difference if my account and credit card fields are in two
ceparate tables (but have relationship in place)?

KARL DEWEY said:
Try this --
SELECT [account], IIF(Min([credit card]) = Max([credit card]), "Great",
"Problem") AS Credit_Card_Status
FROM SomeTable
GROUP BY [account];

--
Build a little, test a little.


Agent_KGB said:
I need to see if MIN and MAX value of a field match, I know how to do IIF
statement to compare them, but i can't figure out how to reffer to MIN and
MAX values of a field...

say i have [account] field and [credit card] field, and i want to see if i
have any accounts with more then one credit card, so in essence i am looking
to do somethin glike this:

IIF(Min[credit card]=Max[credit card], "Great", "Problem")

having problems with my refferences
Click to expand...
Click to expand...
Click to expand...
 
Back
Top