Tables - Membership DB - advice needed

Hi John,

If you already have the data in Excel, you would create the tables; link to
the spreadsheet with File... Get External Data... Link, and run Append queries
to migrate the spreadsheet data into the tables.
The link lasts until you delete the link - which you can do at any time
without damage to the data. Linking to the spreadsheet gives you a view very
much (but not exactly!) like importing the worksheet into a single table; it's
just an external table not a local one.
You would need to construct a suite of Append and perhaps Update queries to
migrate the wide-flat data from the spreadsheet into your normalized tables.

Ok, I'm sort of understanding what you are saying. I am sitll half asleep
and am only up for a quick e-mail check before heading back to bed for
another hour or so.

Again... YOU DON'T NEED TO TYPE THE DATA. If you have the data in computer
readable form already *that's been done*. All you need to do is move it. Sorry
if I gave the wrong impression above!

No, it was probably me just not understanding what you were trying to tell me.
Lisa

Hi Angel,

As I said to John....I think it was just me. This is very new to me. Some
things I'm getting right away and some are taking a bit of time to sink in.
The funny thing is that before getting your first message with your take on
things.....I had only been researching for a only few days and playing with
the program for a few weeks. I had actually written down a table structure
that I thought would work and it was very similar to what you suggested. I
just didn't have all the PK's & FK's so I had no idea how to relate these
tables to one another.

I do very much appreciate your offer but I'm one of those people who want to
try to figure things out on their own. I feel that I spent a lot of money on
this program as I already had Office 2007 standard edition and had to buy
this seperately. I better figure out how to use it. I have a lot of things
personal stuff in Excel that could be in Access but I want to get this big
"job" done first. Our "helper" here offered to do the same thing. I gave
him what my original setup was (seperate tables for each group at that point)
but it had way too many check boxes and was confusing. I didn't like what he
did being based on that, etc. I'm just too particular & picky about things I
suppose.

If I need anymore help, I will probably just start a few topic based on
whatever questions pop up. I know for a fact that when I get to the forms,
I'm going to have issues. I did already post a Form question but it hasn't
gotten me any help. I don't want the basic form that Access will setup for
you. I want to customize it with the order I want to type in information and
then adding "buttons" for different functions.
Lisa

Lisa,

There are numerous technical solutions to implement what you describe. But
you have to first define your "business rules" and use the technology to
implement them. For example: who can make which changes.

Those 6 orgs. I do it for range in size from 15 to 800 people. Using the
"800" as an example most similar to your situation...... My own "medium
tech" solution for input / updates is based on the observation that 95% of
the "input" work is funnelling / vetting / merging / cleaning up / converting
the updates into legible, trustworthy "once and one once" data identified as
suchj. And the other 5% is data entry. And so I have other good caliber
people be the "interface" and do the "95%" and send me the cleaned up "once
and only once" update info and I do all of the data entry which is the other
5%. For mailings, I use stored procedures/ queries to create a mini
database (takes only seconds, could also be a spreadsheet) which I email to
to the person who is doing the mailing, and they print the labels.

Most other political issues can be solved by sending people copies /
mini-databases of whatever they need to have. But you have to hang tough
that yours is the "master" and ALL updates must go only to it.

Sincerely,

Fred


"Lisa - NH" wrote:

Hi Fred,

You've touched on one of my long list of real world issues that I had in
mind. An expert solution may make this your job for life. When you go to
try to hand it off you'll need to find someone who can deal with the more
sophisticated relational abstract structure rather than than the "flat"
structure I suggested.

I didn't fully think about that until tonight. The funny thing is that
right now out of the people who do the membership for the post (me for
Auxiliary and two others). Only one other person has the know how to figure
it out. The other person does his file updating at home in a Works
spreadsheet.

The other thing we're trying to figure out is that a copy of this DB will be
kept at the post. If we give them full access to it to edit the Legion &
Sons membership and do the label printing (which they were thinking about
starting to do at the post...I don't know why as we do it here and there have
been no problems other than my complaining that it was a pain doing it from
Excel with it having two seperate mailing lists based on zipcodes...don't
ask...that was part of why the upgrade to Access to make that easier.) #1 -
they will more than likely make a mess out of that file and #2 - how do I get
the Auxiliary updates into that file and get the other updates out and into
my file. I thought they just wanted this down there for informational
purposes and that we'd do queries to export out each membership roster to
Excel for them to edit...highlight changes and send back. There's too much
darn confusion with this. I don't know why they ever suggested we go this
route. I do however like the concept of the program and what it can do.
Maybe I just give them the file and let them have at it and when I make
Auxiliary updates I write the information down and have my husband bring it
down to the post every few weeks to update the file there. Time will tell.
Lisa

Hi Fred,

My original hope was that I would remain in full control of the main file
here at home. The copy at the post would be just for informational purposes.
We'd export out the Legion & Sons Rosters into an Excel spreadsheet for them
to do the updates on and send back to me with the information highlighted so
we know what data was changed. That is supposed to be what's happening
now...it's not working.

The person doing the Sons membership is doing a pretty good job of keeping
us updated. He highlights whatever he changes or adds in the current Excel
file. He also does this work on the post computer. So my husband just goes
down there with his USB key to bring home the changed file and then I put the
updates into my file and he brings it back down there.

The person doing the Legion membership does his work at home. (Which I
can't say anything about as it's up to the group to decide what they will
allow and it's up to the person who does the membership to do what he or she
is comfortable with. I do all my work at home. It's too hard for me to get
to the post to do the work there and it would be physically uncomfortable for
me.) He uses Works which isn't too big of a deal as you can open it with
Excel. But updates aren't being done properly and given to us. This is the
problem when the person who does membership is a post officer and those roles
usually change yearly. Although the current person doing the membership, is
planning on running for another year in this position.

Your solution sounds like it's working very well. I just see us continuing
to have issues. My husband has been talking about trying to split the Legion
membership off of the senior vice position at least for a year or two to get
it all straightened out. I think it's a good idea if they had someone do the
job for at least 2 years at a time instead of 1. Your first year is really
figuring things out. I've been doing the Auxiliary membership for 6 years
now and Secretary/Treasurer for 5 and I've been doing the mailing list for
about 4 or 5 years now.

5%. For mailings, I use stored procedures/ queries to create a mini
database (takes only seconds, could also be a spreadsheet) which I email to
to the person who is doing the mailing, and they print the labels.

That's something that I will keep in mind if they decide they really want
print the labels at the post. I still don't know why they were thinking
about doing it. They don't really have very good equipment down there.
Whereas here, we bought a very expensive color laser all in one machine
that's like a small business machine and we just upgraded our computers as
well.

It seems like Access will make the printing of the labels a little easier.
In the Excel file, once we started putting in the Zip+4's (we still have some
regular zips)....I had changed the field format and when I went to merge that
into Word for printing. The begining 0's weren't there. I was so ticked
off. I tried changing the field back and it screwed things up. I didn't
want to spend the time to sit down, create a new field, format it properly
and then type in all the zipcodes again. The solution I found was to install
the Avery Wizard but I still couldn't use it right from Word. I had to use
it from inside Excel. It did work though

I do have a question on the mailing list. Our post office requires us to
sort by zip. It requires the use of 2 boxes. One for all the 030's and one
for the rest. The people who do the labeling were having trouble keeping
things straight as they would just hand out a couple sheets of labels to each
person. So they asked us to split the file into two and print two groups of
labels. Is this something that could be done easily enough in Access?

Most other political issues can be solved by sending people copies /
mini-databases of whatever they need to have. But you have to hang tough
that yours is the "master" and ALL updates must go only to it.

I do like that thought as well. But if that use that to make the updates,
how I can tell what's been changed/added without having to go line by line?
We've had to do that so many times over the years it gets frustrating.
Lisa

Lisa,

Roughly speaking, with Access, if you can imagine it you can do it.

On your label question, yest your can easily do that in Access. (of course,
Access will print the labels directly). To clarify your thinking, remember
that it can implement both sorting and filtering based on criteria. It's
simple, and there are a lot of ways to do it which makes it hard to answer
the question - have to pick one of them. The lowest tech is to run two
reports (label printings) with criteria to put them into those two groups,
and within each printing sort by groups. One step up is add a field (load it
globally) for post office group and do a two level sort (by group by zip
code) and "group" by group with a page break so that you get a fresh sheet
when it changes groups. One step up from that is to set up an expression
in a query that products a group based on zip code ( -iif([zip] = "60530",
"A"."B").

On the other stuff I think that your thinking is evolving along a good track.

You method of them sending you updates via a modified table makes life
complicated, including various "what if's" , but on your immediate question
you could just give them a field to mark records that have bee modified.
When you get farther along in access, you can have them edite through a form
and add code that automatically does that.

The low tech way would be to have them enter updates on a seperate table /
spreadsheet / piece of paper / email rather than having them modify a copy
of the database.

Sincerley,

Fred

"Lisa - NH" wrote:

Hi Fred,

My original hope was that I would remain in full control of the main file
here at home. The copy at the post would be just for informational purposes.
We'd export out the Legion & Sons Rosters into an Excel spreadsheet for them
to do the updates on and send back to me with the information highlighted so
we know what data was changed. That is supposed to be what's happening
now...it's not working.

The person doing the Sons membership is doing a pretty good job of keeping
us updated. He highlights whatever he changes or adds in the current Excel
file. He also does this work on the post computer. So my husband just goes
down there with his USB key to bring home the changed file and then I put the
updates into my file and he brings it back down there.

The person doing the Legion membership does his work at home. (Which I
can't say anything about as it's up to the group to decide what they will
allow and it's up to the person who does the membership to do what he or she
is comfortable with. I do all my work at home. It's too hard for me to get
to the post to do the work there and it would be physically uncomfortable for
me.) He uses Works which isn't too big of a deal as you can open it with
Excel. But updates aren't being done properly and given to us. This is the
problem when the person who does membership is a post officer and those roles
usually change yearly. Although the current person doing the membership, is
planning on running for another year in this position.

Your solution sounds like it's working very well. I just see us continuing
to have issues. My husband has been talking about trying to split the Legion
membership off of the senior vice position at least for a year or two to get
it all straightened out. I think it's a good idea if they had someone do the
job for at least 2 years at a time instead of 1. Your first year is really
figuring things out. I've been doing the Auxiliary membership for 6 years
now and Secretary/Treasurer for 5 and I've been doing the mailing list for
about 4 or 5 years now.

5%. For mailings, I use stored procedures/ queries to create a mini
database (takes only seconds, could also be a spreadsheet) which I email to
to the person who is doing the mailing, and they print the labels.

That's something that I will keep in mind if they decide they really want
print the labels at the post. I still don't know why they were thinking
about doing it. They don't really have very good equipment down there.
Whereas here, we bought a very expensive color laser all in one machine
that's like a small business machine and we just upgraded our computers as
well.

It seems like Access will make the printing of the labels a little easier.
In the Excel file, once we started putting in the Zip+4's (we still have some
regular zips)....I had changed the field format and when I went to merge that
into Word for printing. The begining 0's weren't there. I was so ticked
off. I tried changing the field back and it screwed things up. I didn't
want to spend the time to sit down, create a new field, format it properly
and then type in all the zipcodes again. The solution I found was to install
the Avery Wizard but I still couldn't use it right from Word. I had to use
it from inside Excel. It did work though

I do have a question on the mailing list. Our post office requires us to
sort by zip. It requires the use of 2 boxes. One for all the 030's and one
for the rest. The people who do the labeling were having trouble keeping
things straight as they would just hand out a couple sheets of labels to each
person. So they asked us to split the file into two and print two groups of
labels. Is this something that could be done easily enough in Access?

Most other political issues can be solved by sending people copies /
mini-databases of whatever they need to have. But you have to hang tough
that yours is the "master" and ALL updates must go only to it.

I do like that thought as well. But if that use that to make the updates,
how I can tell what's been changed/added without having to go line by line?
We've had to do that so many times over the years it gets frustrating.
Lisa