Adding a table

[Patty]

I am a new Access user. I created a database with members name for our
organization and I want to create a table do copy ID and Names for more info
on the member (role, joind date, etc.)

How do I create a table, copying name and ID and then add more fields into
my new table. Each time I try to do it, I either get new ID numbers or it
will not copy, etc.

Appreciate any advice.

 

[John W. Vinson]

I am a new Access user. I created a database with members name for our
organization and I want to create a table do copy ID and Names for more info
on the member (role, joind date, etc.)

How do I create a table, copying name and ID and then add more fields into
my new table. Each time I try to do it, I either get new ID numbers or it
will not copy, etc.

Appreciate any advice.

Are you sure you want a new TABLE? or do you just want to add fields to the
existing table?

I fear you're applying spreadsheet thinking here. In Excel it would be common
to simply copy one spreadsheet page onto another and type more info in
additional columns. But Excel is a spreadsheet, and Access a relational
database; they are different and require different logic!

You CAN copy a table into a new table, retaining the existing ID's and fields
- it's just not something you should ordinarily need to do. Could you explain
how you are doing the copy, and in what way it "will not copy"? Do you get an
error message, or what? Also, what version of Access are you using?

 

[Patty]

What we want to use it for is a member database. I guess everything could be
kept on the first table and we could query what we need. We have member info,
birthdates, family member names, positions and interests in the organization,
member status, donations, etc. Some of the info (family member names) would
not really need to be in the first table, we just want the member number,
first & last names and then we will enter family member details.

Not sure the best way to go about this.

 

[John W. Vinson]

What we want to use it for is a member database. I guess everything could be
kept on the first table and we could query what we need. We have member info,
birthdates, family member names, positions and interests in the organization,
member status, donations, etc. Some of the info (family member names) would
not really need to be in the first table, we just want the member number,
first & last names and then we will enter family member details.

You need to identify the "Entities" - real-life persons, things or events -
and all of the relevant "Attributes" of each entity. Each kind of Entity will
be given a table; each Attribute will be a field of a table.

I can see several tables here. Members are people; a person has attributes
like firstname, lastname, birthdate, etc. Interests are not people; they're an
abstract "thing", and a given person might have zero, one, two or fifty
interests! So you would need a table of Interests (all of the interests that
you are concerned with for purposes of your organization), and then another
table with links to the table of Members and to the table of Interests, so
each person would have as many rows in this table as interests.

Donations are a simple one to many relationship. You wouldn't put Donations
into the member table, since one member could have made zero, one, two, or
hundreds of donations. You would need a Donations table with a link to the
Members table, the date and amount of the donation, etc.

Families get complicated and I don't want to go into that here!

There are membership databases available - I've been giving away a very simple
church membership database that you could adapt if you wish; if you would like
a copy email me at jvinson <at> wysard of info <dot> com. In addition you
might want to check out the tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Patty]

A church membership is exactly what we are setting up!! I will email you and
keep on plugging along reading the resources and tuts!

Thanks for taking the time to help!!!

Patty