Top of multiple columns. (1 Viewer)

Infinite

More left to learn.
Local time
Today, 11:01
Joined
Mar 16, 2015
Messages
402
So, I have a table with 3 columns.

Item____Cost1___Cost2___Cost3
One_____$10____$14____$2
Two_____$3_____$17____$15
Five_____$1_____$6_____$7


What I would like to do is get something to get me the top of those. That something can be a query or a SQL code. Anything in access will work for me.

So, let's look at Item 1. I want it to get me the value under Cost 2 because Cost 2 has the highest value for Item 1.

For Item 2 I would want also Cost 2 because again, Item 2 has the highest value.

With Item 5 I would want the value under Cost 3 because, for this item, Cost 3 has the highest value.



Any way to do that?
 

isladogs

MVP / VIP
Local time
Today, 18:01
Joined
Jan 14, 2017
Messages
18,186
Hi

Yup easy to do. For example, use a query - see attached for design & results
Adapt to fit your table name

In SQL that is:
Code:
SELECT Table1.Item, Table1.Cost1, Table1.Cost2, Table1.Cost3, IIf([Cost3]>=[Cost2],[Cost3],[Cost2]) AS HighValue
FROM Table1;
 

Attachments

  • qryDesign.PNG
    qryDesign.PNG
    10.6 KB · Views: 67
  • qryResults.PNG
    qryResults.PNG
    5.9 KB · Views: 62

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:01
Joined
Aug 30, 2003
Messages
36,118
If it's not too late, I'd normalize the table, which would look like:

Item____Code___Amount
One_____1______$10
One_____2______$14
One_____3______$2
Two_____1______$3

Simple SQL finds the highest price per item, and it would allow more flexibility. More here:

http://www.r937.com/Relational.html
 

isladogs

MVP / VIP
Local time
Today, 18:01
Joined
Jan 14, 2017
Messages
18,186
True - I did the query exactly as Infinite described it.
He/she he didn't ask for cost 1 to be included!

If he wants that included then the IIf statement could be adapted, or as you say, use an array instead
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:01
Joined
Aug 30, 2003
Messages
36,118
Well, the sample data didn't have any with 1 being the highest, but the description mentioned 3 columns and wanting the top of those. That to me includes 1.
 

isladogs

MVP / VIP
Local time
Today, 18:01
Joined
Jan 14, 2017
Messages
18,186
I know! Was just trying to make an excuse for reading it literally rather than sensibly. Modified code based on IIf

Code:
SELECT Table1.Item, Table1.Cost1, Table1.Cost2, Table1.Cost3, IIf([Cost3]>=[Cost2] And [Cost3]>=[Cost1],[Cost3],IIf([Cost2]>=[Cost1],[Cost2],[Cost1])) AS HighValue
FROM Table1;

I think 3 items is about the limit for IIf statements without making mistakes.
Now I know you're going to read it carefully & try to find an error!
 

Infinite

More left to learn.
Local time
Today, 11:01
Joined
Mar 16, 2015
Messages
402
I am going to bed now, will try all those in the morning. Thanks for the help! I'll let you know tomorrow if it works! Thanks!
 

Infinite

More left to learn.
Local time
Today, 11:01
Joined
Mar 16, 2015
Messages
402
Ok, I do have more than 3 columns. I have about 17 of them, and I want to get the top 3 and display them in a message saying "You lost money because of Cost1, Cost2, and Cost 3. But mostly Cost1."

Pretty sure I shouldn't make an IFF statement longer than 3 IFFs ;)

The attached image is what I have, and a message box saying the "you lost money because of Booth Labor, Booth Cost, and Electric. But mostly because if Booth Cost." is what I want. And currently, I don't think it's possible for me to change my table setup to make it how you said pbaldy.
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    5.9 KB · Views: 51

plog

Banishment Pending
Local time
Today, 13:01
Joined
May 11, 2011
Messages
11,611
Pretty sure I shouldn't make an IFF statement longer than 3 IFFs

If "should" is what you are aiming for, then table normalization is the path. You've effectively stored values that belong in a field in the name of fields. You need to create a new table (Costs) and move the data in those 17 fields there:

Costs
CostID, autonumber, primary key
EventID, number, foreign key to existing table
CostType, short text, will hold what is now those field names (Booth, Labor, Electric, etc.)
CostAmount, number, will hold the actual cost
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:01
Joined
Aug 30, 2003
Messages
36,118
I still think the design should be normalized, as suggested in post 3. Failing that, the link in post 4 will give you a more dynamic method. But I'd normalize. When they add a new cost item, you don't want to have to add fields, modify queries, forms, and reports.
 

Infinite

More left to learn.
Local time
Today, 11:01
Joined
Mar 16, 2015
Messages
402
Look at the attached image. I'm not sure how I can change that to any other format...
And that table is the one I'm using for this query as the rest of my data is kept in separate tables like tblShows, tblShowCost etc, etc. I just have an append query running and making me the table I'm using right now.
 

Attachments

  • Screenshot_2.png
    Screenshot_2.png
    23.2 KB · Views: 59

isladogs

MVP / VIP
Local time
Today, 18:01
Joined
Jan 14, 2017
Messages
18,186
Its a pity you didn't print the screenshot at the start to show there were actually 6 fields to include or I wouldn't have bothered doing the query with an IIf function

I agree with both my colleagues about normalising the data but if that too much to deal with at this point, then do one of the following:
a) develop a nested IIf statement with 6 'clauses' - feasible but messy & I'll leave that to you!
b) create a function to loop through each column in turn and select the highest
c) use the array approach suggested by Paul in #4

Up to you ... over and out
 

Users who are viewing this thread

Top Bottom