Clicky

I'm just a preacher, who inherited a pretty complex church management system designed with Access.  I have a table called "Talent Offerings List,"  It has 1,000 records, with about 120 fields.  Most of them are yes / no, a couple are text fields.  What is the simplest way for me to reset or erase all those fields, without touching the very first field, which is the member number in our congregation.  Extra points for an answer I can understand and implement.  Thanks!!!!!!

asked 12/14/2011 03:19

preacherdpa's gravatar image

preacherdpa ♦♦


10 Answers:
first create a query like this

select [Talent Offerings List].[member number] into NewTable
from [Talent Offerings List]


that will create a new table "newTable"


now run this delete query

delete * from [Talent Offerings List]

this will clear all the records from the table


now place back the records created in the first query

insert into [Talent Offerings List]([member number])
select newtable.[member number]) from
newTable



link
's gravatar image


before you do that, you need to change the field [member number] with the actual name of the field//

also, what is the DataType of the field that hold the member number?
link
's gravatar image


One way would be to:

1. Create a backup copy of the database, just in case

2. open the query design grid
3. Select the table and add it to the query
4. Select each of the columns in the table, except the MemberID field, and add them to the grid.
5. Change the query type to UPDATE
6. In the "Update To:" row of the grid type "NULL" (without the quotes), then copy this to the right for every one of the columns
7. Then run the query.

Another would be to:

1. Create a backup (always create a backup)
2. Delete all the records from the table.
3. Create an append query from your Members table to append the member ID for each member.  It would look something like:

INSERT INTO [Talent Offerings List] (MemberID)
SELECT [MemberID] FROM tblMembers

If you have a Status in tblMembers
link
's gravatar image


the member number field is a number field.
link
's gravatar image


preacherdpa,

My first question would be why this needs to be done...

In other words, ...please take a moment to explain the reason for needing this, ...in case there is a more efficient design approach
link
's gravatar image


ok..preacher,
you can proceed with the queries i posted..
link
's gravatar image


this is a table that records each member's volunteering for specific tasks - by way of a yearly survey.  so what they volunteered for last year needs to be erased and a new year's data entered, from what I can tell.  thanks for the question.
link
's gravatar image


Thanks so much!!!!!!!
link
's gravatar image


Ok, then just a note:

By "Clearing" out last year's data you lose all your "History"

So if for any reason a person asked for a listing of all their volunteer work over the years, (for a College application, job interview, ...etc), this information is lost forever.

It might be better, going forward, to create a more normalized structure:
tblMembers:
mID
mName

tblVolunteerWork
vwID
vmName

tblMemberVolunteerWork
mvwID
mID
vmID
mvwYear
link
's gravatar image


thank you for the practical observation and suggestion.  we'll be incorporating it as soon as I bring myself up to speed with access.
link
's gravatar image


Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×14

Asked: 12/14/2011 03:19

Seen: 186 times

Last updated: 12/14/2011 04:03