Hello,

First, let me explain where I'm starting from...

I help out with a private non-profit club as a Board member and as
Secretary. Our current membership list is done via a MS Works database
that was set up by persons unknown the better part of 10 years ago. The
person doing the data entry portion and mailing out the newletters, etc.
is just barely computer-literate, i.e. surf, check email, maybe fill out
a template or form if it's not too complicated - maybe. Lots of
hand-holding involved. Along the way there have been some computer
crashes and just general data entry mistakes, so the existing 'database'
is in pretty sorry shape. I got a copy, purchased a new copy of Works
9.0, exported the flat-file db as a .csv file and imported it into
Excel, and using Autofilter proceeded to find and fix the worst of the
data inconsistencies (mostly wrong or incomplete data in various
fields). I re-imported the .csv file as a Works db, then cut and pasted
the 'sanitized' data back into the table of the original database file.
I added a few basic filters & reports to give us some things like
annual & current membership lists, new members in last 30 days, etc.

Now, for where I want to go from here...

For better or worse, I'm probably the computer 'guru' amongst the
available candidates, by a considerable margin. Which means... if
anything is going to change for the better, it's going to have to be me
that does it. I don't have the spare time to actually just assume the
whole membership/newsletter role, as I'm about 20-30 years younger than
most of the board, i.e. not retired yet So... either I have to keep
supporting this Works database, or find a better solution - that I can
deal with on my own terms. I'm reasonably proficient with Excel, up to
the point of simple macros. Actual VBA programming is something I'm
just starting to learn. Given that this is a non-profit club, just
buying a pre-canned solution off the shelf isn't a viable option; I'd
still have to support it, and likely not be able to get at the guts of
it if I needed to. And they tend to be priced for deep pockets We
aren't actually doing anything *that* complex with our membership list
at the moment - tracking who's paid up and who isn't, and generating a
mailing list for printing labels for the newsletter. There's a number
of other things I'd like to do in addition or instead of the way we are
doing things now, but none of them seem to really require a full
relational database... but they start to get a bit involved in terms of
manipulating a flat list - I think, anyways. In a perfect world I'd
prefer building something that could run on Excel if a person has it
installed, or OpenOffice if they don't i.e. somewhat platform agnostic.

So... would I be heading in the right direction to try to migrate this
scenario over to an Excel spreadsheet? I can see having a master
membership list worksheet, along with other worksheets for easy viewing
of life members or other special categories, and a worksheet to function
as a data entry form for entering / viewing single records. That's just
for starters.

Any suggestions or advice?

Thanks,

Monte

You might like to take a look at this article by Tushar Mehta:

http://tushar-mehta.com/excel/newsgr...cel/index.html

in which he explains how you might use Excel as a relational database
- not that you would necessarily need to have something so complex
from what you describe.

Hope this helps.

Pete

On Nov 13, 12:25*am, Monte Milanuk <mo...@milanuk.net> wrote:
> Hello,
>
> First, let me explain where I'm starting from...
>
> I help out with a private non-profit club as a Board member and as
> Secretary. *Our current membership list is done via a MS Works database
> that was set up by persons unknown the better part of 10 years ago. *The
> person doing the data entry portion and mailing out the newletters, etc.
> is just barely computer-literate, i.e. surf, check email, maybe fill out
> a template or form if it's not too complicated - maybe. *Lots of
> hand-holding involved. *Along the way there have been some computer
> crashes and just general data entry mistakes, so the existing 'database'
> is in pretty sorry shape. *I got a copy, purchased a new copy of Works
> 9.0, exported the flat-file db as a .csv file and imported it into
> Excel, and using Autofilter proceeded to find and fix the worst of the
> data inconsistencies (mostly wrong or incomplete data in various
> fields). *I re-imported the .csv file as a Works db, then cut and pasted
> the 'sanitized' data back into the table of the original database file.
> * I added a few basic *filters & reports to give us some things like
> annual & current membership lists, new members in last 30 days, etc.
>
> Now, for where I want to go from here...
>
> For better or worse, I'm probably the computer 'guru' amongst the
> available candidates, by a considerable margin. *Which means... if
> anything is going to change for the better, it's going to have to be me
> that does it. *I don't have the spare time to actually just assume the
> whole membership/newsletter role, as I'm about 20-30 years younger than
> most of the board, i.e. not retired yet *So... either I have to keep
> supporting this Works database, or find a better solution - that I can
> deal with on my own terms. *I'm reasonably proficient with Excel, up to
> the point of simple macros. *Actual VBA programming is something I'm
> just starting to learn. *Given that this is a non-profit club, just
> buying a pre-canned solution off the shelf isn't a viable option; I'd
> still have to support it, and likely not be able to get at the guts of
> it if I needed to. *And they tend to be priced for deep pockets *We
> aren't actually doing anything *that* complex with our membership list
> at the moment - tracking who's paid up and who isn't, and generating a
> mailing list for printing labels for the newsletter. *There's a number
> of other things I'd like to do in addition or instead of the way we are
> doing things now, but none of them seem to really require a full
> relational database... but they start to get a bit involved in terms of
> manipulating a flat list - I think, anyways. *In a perfect world I'd
> prefer building something that could run on Excel if a person has it
> installed, or OpenOffice if they don't i.e. somewhat platform agnostic.
>
> So... would I be heading in the right direction to try to migrate this
> scenario over to an Excel spreadsheet? *I can see having a master
> membership list worksheet, along with other worksheets for easy viewing
> of life members or other special categories, and a worksheet to function
> as a data entry form for entering / viewing single records. *That's just
> for starters.
>
> Any suggestions or advice?
>
> Thanks,
>
> Monte

Monte,

I did something very similar for my children's youth soccer club.

A single sheet database with all the information that you have is really all
that you need. And it is a good idea to limit yourself to _one_ sheet
only - otherwise, you end up putting data in the wrong place, or forget to
enter it into two places, etc.

You can apply filters to view, say, lifetime members, or new members, or
members who paid by check, or whatever. You can also use the database as
the source for a pivot table, to categorize or summarize the data easily.
The database (or an extract from the data base, selected using filters, say)
can also be used in a mail merge in Word to print labels for mailings. You
can use the built-in data form functionality to create new records easily,
with all the fields that need to be filled in.

I don't know how platform agnostic this solution is - I have never used
OpenOffice (the horror!)

HTH,
Bernie
MS Excel MVP



"Monte Milanuk" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> First, let me explain where I'm starting from...
>
> I help out with a private non-profit club as a Board member and as
> Secretary. Our current membership list is done via a MS Works database
> that was set up by persons unknown the better part of 10 years ago. The
> person doing the data entry portion and mailing out the newletters, etc.
> is just barely computer-literate, i.e. surf, check email, maybe fill out a
> template or form if it's not too complicated - maybe. Lots of
> hand-holding involved. Along the way there have been some computer
> crashes and just general data entry mistakes, so the existing 'database'
> is in pretty sorry shape. I got a copy, purchased a new copy of Works
> 9.0, exported the flat-file db as a .csv file and imported it into Excel,
> and using Autofilter proceeded to find and fix the worst of the data
> inconsistencies (mostly wrong or incomplete data in various fields). I
> re-imported the .csv file as a Works db, then cut and pasted the
> 'sanitized' data back into the table of the original database file. I
> added a few basic filters & reports to give us some things like annual &
> current membership lists, new members in last 30 days, etc.
>
> Now, for where I want to go from here...
>
> For better or worse, I'm probably the computer 'guru' amongst the
> available candidates, by a considerable margin. Which means... if
> anything is going to change for the better, it's going to have to be me
> that does it. I don't have the spare time to actually just assume the
> whole membership/newsletter role, as I'm about 20-30 years younger than
> most of the board, i.e. not retired yet So... either I have to keep
> supporting this Works database, or find a better solution - that I can
> deal with on my own terms. I'm reasonably proficient with Excel, up to
> the point of simple macros. Actual VBA programming is something I'm just
> starting to learn. Given that this is a non-profit club, just buying a
> pre-canned solution off the shelf isn't a viable option; I'd still have to
> support it, and likely not be able to get at the guts of it if I needed
> to. And they tend to be priced for deep pockets We aren't actually
> doing anything *that* complex with our membership list at the moment -
> tracking who's paid up and who isn't, and generating a mailing list for
> printing labels for the newsletter. There's a number of other things I'd
> like to do in addition or instead of the way we are doing things now, but
> none of them seem to really require a full relational database... but they
> start to get a bit involved in terms of manipulating a flat list - I
> think, anyways. In a perfect world I'd prefer building something that
> could run on Excel if a person has it installed, or OpenOffice if they
> don't i.e. somewhat platform agnostic.
>
> So... would I be heading in the right direction to try to migrate this
> scenario over to an Excel spreadsheet? I can see having a master
> membership list worksheet, along with other worksheets for easy viewing of
> life members or other special categories, and a worksheet to function as a
> data entry form for entering / viewing single records. That's just for
> starters.
>
> Any suggestions or advice?
>
> Thanks,
>
> Monte

Hi Monte,

You might want to check out my Excel database tutorial
http://www.edferrero.com/ExcelTutori...0/Default.aspx

Ed Ferrero

Ed,

Thanks for the link; the information there should prove very helpful.
The bit on making an open-ended data validation list will make life
simpler in several spreadsheets I have. Is the User Forms portion of
your tutorial getting closer?

Monte

Bernie Deitrick wrote:
> Monte,
>
> I did something very similar for my children's youth soccer club.
>
> A single sheet database with all the information that you have is really all
> that you need. And it is a good idea to limit yourself to _one_ sheet
> only - otherwise, you end up putting data in the wrong place, or forget to
> enter it into two places, etc.

I guess I didn't word that very well the first time around... my intent
is to have one master list, so the data would be entered and stored in
one place only (like you suggest). The other sheets would be
effectively read only (as far as the user is concerned) and
auto-populated from the master list. At least, thats my 'pie in the
sky' idea as of now. I've came across some macro implementations for
doing that (auto-populate from one sheet to many sheets) in the
newsgroups; I have no idea as of yet as to how well that would work out
in actual practice.

>
> You can apply filters to view, say, lifetime members, or new members, or
> members who paid by check, or whatever. You can also use the database as
> the source for a pivot table, to categorize or summarize the data easily.
> The database (or an extract from the data base, selected using filters, say)
> can also be used in a mail merge in Word to print labels for mailings. You
> can use the built-in data form functionality to create new records easily,
> with all the fields that need to be filled in.
>

Understood. The biggest challenge (I think) is going to be setting
things up so that the user literally cannot screw it up. Data
validation will be one part, along with some auto populating of fields,
but I think the less the user (at least this particular user I'm dealing
with now) actually 'sees' of Excel, the better. Some sort of form or
macro or VBA front end that allows them to select a pre-canned filter
(without having to understand filtering) and then pretty-print the
results is what I have in mind. At the same time, I'd like to leave an
'out' so if a more advanced user (one can always hope) comes along with
a simple password, they can bypass as much of the 'auto-magic' stuff as
they want.

At any rate, it sounds like I'm not exactly headed into uncharted
waters... I was concerned that I might be stretching things too far by
not using a DB, even for a simple list like this. That doesn't appear
to be the case, so I guess its time to get busy learning!

> I don't know how platform agnostic this solution is - I have never used
> OpenOffice (the horror!)
>

Figured I might be asking a bit much, being a microsoft newsgroup and all

Thanks,

Monte

> Is the User Forms portion of
> your tutorial getting closer?

No

Ed Ferrero