Re: How to find Minimum of four fields


Douglas J. Steele wrote:
Let's assume you currently have a table with Id, Field1, Field2, WebCost,
PhoneCost, StoreCost, and ShowCost.

Create a UNION query along the following lines:

SELECT Id, Field1, Field2, "WebCost" AS CostType, WebCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "PhoneCost" AS CostType, PhoneCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "StoreCost" AS CostType, StoreCost AS CostAmount
FROM MyTable
UNION
SELECT Id, Field1, Field2, "ShowCost" AS CostType, ShowCost AS CostAmount
FROM MyTable

Use that query to populate the properly normalized table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"galsaba" <galsaba@xxxxxxx> wrote in message
news:1166195092.934251.294400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Sounds good, but I really dont know how to do it. How should I convert
columns to rows?
galsaba

Douglas J. Steele wrote:
What happens if you suddenly have a 5th option? You'd have to redo your
table, all the forms that use that table, and so on.

Having 4 separate rows is definitely the correct approach. It represents
the
resolution of the many-to-many relationship between the Item table and
the
MethodOfSale table.

"galsaba" <galsaba@xxxxxxx> wrote in message
news:1166176989.999988.127620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
say i have a store , and I have 4 option of how to seel an item. web,
phone, store, show.
each one will have adifferent cost of sale for me, based on many
inputs. there is a list of items, that's ehy i think the "my cost
selling by web", "cost selling by phone", etc should be columns, and
the items are the rows.


Jeff Boyce wrote:
As others have pointed out, your data design is not optimized for what
Access can do. If you feed Access '*** data, you won't get the best
of
its features and functions.

If you provide a bit more detail about what you are storing in those
four
fields, the newsgroup readers may be able to offer suggestions about
improving the table design (and hence be better able to use Access'
strengths).

This is BEATIFUL! It works! (although I dont exactly understand what I
did...)
is there a way to do it with "Design View"? or just "SQL View"?

Thanks!

galsaba

.