new problem

J

jerry

I am a doctor recently I made a DB on medical store management in
which there are number columns, in excess of 30, there is a
corresponding string column associated with each of these, the last
column of the query is calculated one, representing the minimum number
across the row(minimum number from the set of fields) the problem is
using switch or iif function to retrieve the value of the
corresponding string column or to get the name of the column equlling
the value in the minimum field works only for 14 fields. Is there a
way using array function extend this number.
 
D

Douglas J. Steele

You're probably not going to like this, but your design is incorrect.

You shouldn't be storing the data as multiple columns in a single row.
Instead, you should be storing the data as multiple rows in a second,
related table. You'd then be able to do a simple Min query on the data.
 
J

Jeff Boyce

Jerry

I can't be sure from your description, but it sounds like you are describing
a spreadsheet (multiple repeating columns). Access is a number of things,
but what it is not is a bigger, stronger spreadsheet.

If your table has "in excess of 30" columns, your database would probably
benefit from more normalization.

Rather than explaining how to drive nails with a chainsaw, I'd rather make
sure your underlying data design can make the best use of Access' features
and functions first.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
J

jerry

If I understand what you are doing, it is a violation of the
normalization rules of relational databases. In other words you are trying
to use Microsoft Access the database like Microsoft Excel the spreadsheet.
They are not the same thing and need to be used differently.

Lets talk about a family. Mother Father and children.

You will have a family table:

Smith
Jones
etc.

Next you will have a individual table

Sally Smith
Jane Smith
Gregg Smith
Tom Jones
Nancy Jones
etc.

Sally Gregg and Jane are all part of the Smith family so they will be
"related" to the "Smith" record in the Family database. Tom and Nancy will
be related to the Jones family.

If Nancy were to marry Gregg, then Nancy would be related to both The
Smith family and the Jones family, but there would still be only one Nancy.

Relationships can become very complex (as this example would if I
worried about the differences between mother father and children). But a
properly designed database can handle all that.

Another table that you would likely want would be addresses and each
person would be related to one address, which would be related in turn to
several people, but only indirectly to a family or families.

Until you "Normalize" the data, you will experience problem after
problem.
Click to expand...

Thank You Joseph I posted a reply to Douglas, Please go through it.
After normalisation I can,t see a way of importing the vendor price
and specifiction data in bulk they will have to done singly, for two
thosand items and ten suppliers, this would take days, or am I
mistaken
 
J

jerry

If I understand what you are doing, it is a violation of the
normalization rules of relational databases. In other words you are trying
to use Microsoft Access the database like Microsoft Excel the spreadsheet.
They are not the same thing and need to be used differently.

Lets talk about a family. Mother Father and children.

You will have a family table:

Smith
Jones
etc.

Next you will have a individual table

Sally Smith
Jane Smith
Gregg Smith
Tom Jones
Nancy Jones
etc.

Sally Gregg and Jane are all part of the Smith family so they will be
"related" to the "Smith" record in the Family database. Tom and Nancy will
be related to the Jones family.

If Nancy were to marry Gregg, then Nancy would be related to both The
Smith family and the Jones family, but there would still be only one Nancy.

Relationships can become very complex (as this example would if I
worried about the differences between mother father and children). But a
properly designed database can handle all that.

Another table that you would likely want would be addresses and each
person would be related to one address, which would be related in turn to
several people, but only indirectly to a family or families.

Until you "Normalize" the data, you will experience problem after
problem.
Click to expand...

Thank You I am on the Job, apart from my other routine duties in the
OPD, since I am a novice to programming, and this db of mine did
actually evolve from excel spreadsheet, I still feel that a function
to address this problem should be too hard to come by, maybe by using
array, which calculates minimum value could also return position of
the array element, which could then be related to the column heading
--It's just a thought.
 
J

jerry

Jerry

I can't be sure from your description, but it sounds like you are describing
a spreadsheet (multiple repeating columns). Access is a number of things,
but what it is not is a bigger, stronger spreadsheet.

If your table has "in excess of 30" columns, your database would probably
benefit from more normalization.

Rather than explaining how to drive nails with a chainsaw, I'd rather make
sure your underlying data design can make the best use of Access' features
and functions first.

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/






- Show quoted text -
Click to expand...

I got the point, but I do need the chainsaw Thanks
 
Top