Tables - Membership DB - advice needed

Hi again StrayBullet,

I did take a little break. Ate some lunch and watched some TV.

The MemberID could be entered so long as intMemberFK (the primary key number
from tblMember for each member) is entered as well. I suggest using a query.

Ok, so to do that the memberID would have to be in the tblMembers?

tblGroup will store intMemberFK (the primary key number from tblMember for
each member) as many times as is necessary (one member can be in a single
group or in multiple groups)

Hmmm....I might be having an ahhhh haaaa moment. Does that mean..that
before I input the member#'s (or use a query to build that table), I have to
have specified what group each member belongs to? Already mentioned
something in other post about possibly doing this in the Excel file and
importing it into Access.

Again I thank you and look forward to your reponse on the other post.
Please explain in detail how to do the query to build a table for the
tblGroup & tblGroupType.
Lisa

Hi Fred,

I appreicate your help & input. I think it will work well once I get it
going. I'm very particular about things. I always like to improve on
things. In the few years (I think it's 4 now...maybe 5). I've re-done the
Excel version a few times. I definately want to get the Access version set
up well from the begining. I just hope that when I finally decide I've had
enough of doing this.....that the next person does as good of a job with it.
Lisa

On Thu, 6 Mar 2008 12:59:01 -0800, Lisa - NH
wrote:

Hmmm....I might be having an ahhhh haaaa moment. Does that mean..that
before I input the member#'s (or use a query to build that table), I have to
have specified what group each member belongs to?

Certainly not!!!

That's what data entry is FOR.

You define your members table.
You define your groups table.
You enter the groups into the groups table.
You start entering members into the members table.
*THEN* you define each member's membership in whatever groups you wish, by
adding new records into the membership table, using a Subform as a tool.
--
John W. Vinson [MVP]

Lisa,

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.

Sincerely,


Fred




"Lisa - NH" wrote:

Hi Fred,

I appreicate your help & input. I think it will work well once I get it
going. I'm very particular about things. I always like to improve on
things. In the few years (I think it's 4 now...maybe 5). I've re-done the
Excel version a few times. I definately want to get the Access version set
up well from the begining. I just hope that when I finally decide I've had
enough of doing this.....that the next person does as good of a job with it.
Lisa

Hi John,

Ok, confusion setting in again. I didn't know you could do sub-forms off of
the table. Was that what that little "plus" sign was after relating tables
earlier? (As I mentioned I did scrub what I was doing and will be starting
it again...definately not tonight though.)

I'm definately trying to avoid having to type in every peice of details for
the almost 1000 records as I'd be here forever.
Lisa

"John W. Vinson" wrote:

Certainly not!!!

That's what data entry is FOR.

You define your members table.
You define your groups table.
You enter the groups into the groups table.
You start entering members into the members table.
*THEN* you define each member's membership in whatever groups you wish, by
adding new records into the membership table, using a Subform as a tool.
--
John W. Vinson [MVP]

On Thu, 6 Mar 2008 15:28:00 -0800, Lisa - NH
wrote:

Hi John,

Ok, confusion setting in again. I didn't know you could do sub-forms off of
the table.

Well... you can. But DON'T.

Tables re for data storage. you can create your tables first, define their
relationships - all with the tables empty.

Then you would create Forms (with subforms) for data entry and editing.

THEN you would enter your data.

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.

Was that what that little "plus" sign was after relating tables
earlier? (As I mentioned I did scrub what I was doing and will be starting
it again...definately not tonight though.)

I'd suggest opening each table in design view; right clicking in the design
window; and change the Subdatasheet property from "[Auto]" to "[None]".
Subdatasheets do (sort of, crudely) let you enter data into related tables in
a Table datasheet view - but they also wreck performance and are very limited
in utility.
--
John W. Vinson [MVP]

Hi John,

Tables re for data storage. you can create your tables first, define their
relationships - all with the tables empty.

Yes I know.

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.

Is this link permanent? I don't want that as I don't want to keep this
Excel file. I don't understand how the data would link into the proper tables
that way. When I imported from Excel before, everything from one worksheet
went into one table...yes I realize that was just a regular import. This
would make me go back th the thinking that in Excel I would have to create
fields for GroupType, etc.

I'd suggest opening each table in design view; right clicking in the design
window; and change the Subdatasheet property from "[Auto]" to "[None]".
Subdatasheets do (sort of, crudely) let you enter data into related tables in
a Table datasheet view - but they also wreck performance and are very limited
in utility.

I'll remember that when I re-do the file. As far as creating the tables
first with no data and then the forms and then entering all the data.....I
just don't want to spend that kind of time for this amount of records.
Lisa

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

On Thu, 6 Mar 2008 18:19:00 -0800, Lisa - NH
wrote:

Hi John,

Tables re for data storage. you can create your tables first, define their
relationships - all with the tables empty.

Yes I know.

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.

Is this link permanent? I don't want that as I don't want to keep this
Excel file. I don't understand how the data would link into the proper tables
that way. When I imported from Excel before, everything from one worksheet
went into one table...yes I realize that was just a regular import. This
would make me go back th the thinking that in Excel I would have to create
fields for GroupType, etc.


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.

I'd suggest opening each table in design view; right clicking in the design
window; and change the Subdatasheet property from "[Auto]" to "[None]".
Subdatasheets do (sort of, crudely) let you enter data into related tables in
a Table datasheet view - but they also wreck performance and are very limited
in utility.

I'll remember that when I re-do the file. As far as creating the tables
first with no data and then the forms and then entering all the data.....I
just don't want to spend that kind of time for this amount of records.

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!
--
John W. Vinson [MVP]

Hi again Lisa,

My apologies if I made things confusing. I'm between positions right now.
Since this is for the American Legion, if you like I could get the structure
and basic forms set up for you. Also, get the current data imported. Strictly
volunteer basis of course. You can email me at aduphily [at] ptd [dot] net.

Angel

--
Message posted via http://www.accessmonster.com