Compare Min and Max value of a field

[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

 

[KARL DEWEY]

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

 

[Agent_KGB]

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)?

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.

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

 

[KARL DEWEY]

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.

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)?

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.

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