Hi
I'd like some advice about the best way of setting up tables and
worksheets for recording lots of different, but connected, information
ie. a database. Most people have said that's what access is for but I
don't have the expertise to build what I think would be a complicated
database and I also don't have the software (use a Mac).

I want to build a nutrition database that will contain details of
foods, vitamins, minerals, symptoms, diseases etc but I'm not sure of
the best way to lay it out. If I have a worksheet with symptoms in rows
and vitamins in columns I can easily record the link between the two so
that at a glance it's easy to see what symptoms might be caused by a
lack of a vitamin C for example or what the possible vitamin
deficiences were causing headaches. However if I wanted to search on a
combination of symptoms I'm not sure how I'd do it if they are in rows.
If I switched them round so that the symptoms were in columns I could
use the the filter options but then that doesn't make it as user
friendly as an A-Z row list.

I don't know much about linking worksheets so is it better to put as
much info as you can on a single page or is there a canny way of
linking worksheets to cross reference. ie. If there is a worksheet
for symptoms/vitamins and a worksheet with food/vitamins how do you
cross reference them so that you can easily see which foods to
recommend when you've identified a certain deficiency.

Sorry that's a bit long winded and I appreciate it's not really a
technical problem but I imagine it could be if I don't get it right
from the beginning.
Thanks in advance for any replies
Cheerio
Nic


--
Nic M
------------------------------------------------------------------------
Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
View this thread: http://www.excelforum.com/showthread...hreadid=544359

When starting a new program in Excel, I first look at all my "goes-in's" and
"goes-out's" to try to visualize the relationships. Basically I prefer my
data entered in rows, all in one large database, although I suppose multiple
databases could be employed, just haven't found that necessary yet. Outputs
or reports can be filtered and re-arranged to suit and presented on a
separate sheet, to any format, be they lists or graphical. I generally have
either a separate "MasterMenu" type sheet, or freeze about 10 rows above the
database to place data-input cells and/or buttons for running macros.
Usually it takes 2-3 attempts before things are arranged the way I
want.....with sample test data that will produce the results easy to
evaluate........then I can input the mass of real data.

hth
Vaya con Dios,
Chuck, CABGx3



"Nic M" wrote:


Hi
I'd like some advice about the best way of setting up tables and
worksheets for recording lots of different, but connected, information
ie. a database. Most people have said that's what access is for but I
don't have the expertise to build what I think would be a complicated
database and I also don't have the software (use a Mac).

I want to build a nutrition database that will contain details of
foods, vitamins, minerals, symptoms, diseases etc but I'm not sure of
the best way to lay it out. If I have a worksheet with symptoms in rows
and vitamins in columns I can easily record the link between the two so
that at a glance it's easy to see what symptoms might be caused by a
lack of a vitamin C for example or what the possible vitamin
deficiences were causing headaches. However if I wanted to search on a
combination of symptoms I'm not sure how I'd do it if they are in rows.
If I switched them round so that the symptoms were in columns I could
use the the filter options but then that doesn't make it as user
friendly as an A-Z row list.

I don't know much about linking worksheets so is it better to put as
much info as you can on a single page or is there a canny way of
linking worksheets to cross reference. ie. If there is a worksheet
for symptoms/vitamins and a worksheet with food/vitamins how do you
cross reference them so that you can easily see which foods to
recommend when you've identified a certain deficiency.

Sorry that's a bit long winded and I appreciate it's not really a
technical problem but I imagine it could be if I don't get it right
from the beginning.
Thanks in advance for any replies
Cheerio
Nic


--
Nic M
------------------------------------------------------------------------
Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
View this thread: http://www.excelforum.com/showthread...hreadid=544359

Hi Chuck
Thanks for the reply. Can you point me in the direction of some simple
help guides on using macros? Have only come across them in finance
spreadsheets at work and they crash the system when they're enabled!
Cheers
Nic


--
Nic M
------------------------------------------------------------------------
Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
View this thread: http://www.excelforum.com/showthread...hreadid=544359

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://www.cpearson.com/

Vaya con Dios,
Chuck, CABGx3




"Nic M" wrote:


Hi Chuck
Thanks for the reply. Can you point me in the direction of some simple
help guides on using macros? Have only come across them in finance
spreadsheets at work and they crash the system when they're enabled!
Cheers
Nic


--
Nic M
------------------------------------------------------------------------
Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
View this thread: http://www.excelforum.com/showthread...hreadid=544359

More suggestions. It's pretty universal that records are in rows and
fields in columns. Excel has room for 256 columns and 65535 rows. Put
field names in row 1, such as Vitamin, Symptom1, Symptom2, Symptom3 and
so on, and then Food1, Food2, Food3, also in row 1. Would 256 be
enough for all the symptoms and all the foods? Then you could put your
vitamin names in column A, beginning with A in cell A2, B in A3, B1 in
A4, and so on. All databases have some kind of "indexer" column for
the records, usually in Column A. In this case, the vitamin name would
be the indexer. If 256 isn't enough room for all the symptoms and
foods, you could go to 2 sheets, one for symptoms and one for foods.
Since the vitamin name is the indexer, the symptoms and the associated
foods for each vitamin would be on the same row on both the sheets as
long as the vitamin names are in the same order on both sheets. This
will greatly ease using a multi-sheet Excel database. The other choice
is to use the columns for the vitamin names instead. Since there
surely aren't more than 256 vitamins (?), everything could fit on one
sheet that way, with the symptoms and foods in the rows under the
vitamin names. But switching the layout to fields in rows and records
in columns is decidedly non-standard and could cause you grief further
down the road, so think about that. Like everything in Excel, there
are simple solutions and involved solutions, and most things tend to
start out simple and become more and more involved, so you are wise to
use care in setting up the database properly to begin with. I've
worked with Excel in healthcare for years, and John Walkenbach's books
have really helped me.
James

Chuck - thanks for the helpful links :)

James - i didn't know about the limitations on rows and columns so
thanks for that. Talking about indexers fits with what I was puzzling
over which was how to decide what the common factor was going to be if
I had more than one sheet. I'm still puzzled as it seems like
different combinations of the sides of a box...
a) sources sheet = foods and vitamin/mineral content
b) symptoms sheet = symptom manifested through deficiency/excess and
vitamin/mineral
c) chinese 5 elements sheet = food and organ effected.
d) qualities sheet = vitamin/mineral and what it does
If I combined a and c by putting food in rows and vits/minerals/organs
in columns how would I link it to d) where 'what it does' would be in
rows but with the same columns? Is that what you were saying I can't
do?



--
Nic M
------------------------------------------------------------------------
Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
View this thread: http://www.excelforum.com/showthread...hreadid=544359

Nic,
Excel is such a rich software that there are thousands of ways of
doing things. Along with this, there are elegant solutions and crude
solutions. Some of the people who provide answers in this forum (such
as Chuck) are legendary, and I would not compare myself to them for
elegant solutions. However, I have been dealing with Excel databases
long enough to know that certain rules apply and one ignores them to
one's peril. A database is fundamentally a grid of rows and columns
that contains information that can be accessed by using an indexer
field. I think of a database as a respository of data that can be
queried by other programs to create tables, charts and reports. I do
not think of a database as information to be sorted, filtered, or
rearranged or in any way by automated processes. Other people may have
other ideas about this, but I see the database as a kind of precious
resource that is to be guarded and protected. In other words, data is
to be entered into the database carefully, under exactly prescribed
conditions, and manipulation of the data should be done by programs
external to the database file. Once you start thinking of the database
as a multidimensional or flexible layout, data integrity is put at
risk. The database must remain a simple matrix of rows and columns
(records and fields), with the indexer as king. Even if it occupies
more than one worksheet, the indexer always appears in the same order
in the same field. In fact, I always protected my database sheets and
put my database input routines in a file separate from the file holding
my output routines, both of which were separate from the database file
itself. The logic is that one can spend a lot of time compiling and
entering all the data that goes into a database, and it's foolish to
risk corrupting that data to get a solution to any particular way of
looking at the data on any particular day. It's quite easy to open and
query a database file from other Excel files, so there's just no reason
to risk corrupting the data for the sake of expediency.

Well, I have expounded at length. I hope this doesn't seem too
pedantic or self-congratulatory. I would be happy to follow up with
you, as I find your project interesting, but one of us will have to
reveal an e-mail address.
James

Hi James
Thanks for the reply - I had the day off when I posted it and haven't
had a chance to get back on-line until now.
I'v realised just how little I know about excel and I'm happy to give
you my hotmail address but what is the best way of doing this without
sharing it with the world?
Nic


--
Nic M
------------------------------------------------------------------------
Nic M's Profile: http://www.excelforum.com/member.php...o&userid=34246
View this thread: http://www.excelforum.com/showthread...hreadid=544359