Planning content and layout when using Excel as a database

[Nic M]

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

 

[CLR]

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]

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

 

[CLR]

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

http://www.cpearson.com/

Vaya con Dios,
Chuck, CABGx3

 

[Zone]

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

 

[Nic M]

Chuck - thanks for the helpful links

James - i didn't know about the limitations on rows and columns s
thanks for that. Talking about indexers fits with what I was puzzlin
over which was how to decide what the common factor was going to be i
I had more than one sheet. I'm still puzzled as it seems lik
different combinations of the sides of a box...
a) sources sheet = foods and vitamin/mineral content
b) symptoms sheet = symptom manifested through deficiency/excess an
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/organ
in columns how would I link it to d) where 'what it does' would be i
rows but with the same columns? Is that what you were saying I can'
do?
:confused

 

[Zone]

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

 

[Nic M]

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

 

[bgeier]

2 quick thoughts

Is it possible to put all of your categories (foods, minerals
vitamins, etc) with all of their characteristics on their own sheet
then it may be easier to reference each characteristic from each othe
because each category will be in the same place (all category entrie
will be in column "A" for example) then in another column you woul
have the characteristics.

As to trading e-mails, you could use private mail through exceltip.co
(assuming you both have accounts, if either of you do not, they ar
free and easy to setup), that way you can work privately together

 

[Nic M]

Hi bgeier
Thanks for taking the time to post your suggestions. What this forum
has shown me is that excel can do everything I could want it to do and
more but what I will get out of it will only be as good as what I put
into it. And therein lies the problem for me because I'm too in
experienced. I'm thinking too big with my database without the skills
to make it work and all the time spent on puzzling this out is using up
the precious time I have for what I'm really interested in which is
nutrition and health.
Hey ho...

 

[bgeier]

I know the feeling, that is the way I started with Excel, then VBA.

I found something repetitive I did everyday that I absolutely HATED
then found Excel could do it. Then I started tweaking it, the
tweaking it some more. Then my boss found out I "knew Excel inside an
out" so she gave me stuff to do, and so on and so on and so on.

Then it started to fascinate me, so I kept pushing myself to learn mor
and more. The end result is now, I know enough to get myself INT
trouble, but a lot of times I can even get myself OUT of trouble!!!
(Scary thought, that!)

Keep plugging at it, little by little, learn what you "need" to know
and while you are learning what you need to know, you will learn thing
that will be even more useful down the line. Remember, if you have
question, just ask!

If you have any questions, you can private e-mail me here, or just pos
it.
Let me know how your drug interaction database is coming, and if yo
need help just shout

 

[Zone]

Nic,
Sorry for the delay in getting back to you. I'm still interested in
talking via e-mail, but like you, I don't know how to get our e-mail
addresses exchanged without giving them to the rest of the world.
bgeier's suggestion is interesting. Do you want to try that? I know
what you mean by spending a lot of time on something and not having any
results for the trouble. It's hard to get started. Could be that you
could cut back to a more simple concept to streamline things. I would
keep at it, though. Once you get the hang of it, there's a lot you can
do that will eventually make your life easier.
James

 

[bgeier]

To trade "real" e-mail addresses without broadcasting them to the world
you can set up an account on exceltip.com which includes a private
e-mail feature you can use to trade "secret" information.

The accounts are free and secure enough to exchange e-mail address.

Just a thought!