simple add records to new table based on existing table

[Guest]

Problem 1: I want to run a one-time command query that will go through an
existing table (People), find anyone marked as an Active Volunteer, and
create a Volunteer record in the Volunteer table for each Active Volunteer.
(The only data that currently needs to be created is the People ID file that
ties the two tables together relationally)

Problem 2: I'd like the People Entry form to have a button to open a
Volunteer entry form, so that you can review/edit the data for that
volunteer. If that person is not a volunteer, I'd like the Volunteer Entry
to create a record for them, but Im concerned about when a user clicks on the
Volunteer button to open the Volunteer form and doesn't actually have data to
enter (no point in having an empty record created) maybe a save function?

will appreciate the assistance...
Amanda

 

[Guest]

that is, for problem 1, I'm looking for a SQL command....

 

[John Vinson]

Problem 1: I want to run a one-time command query that will go through an
existing table (People), find anyone marked as an Active Volunteer, and
create a Volunteer record in the Volunteer table for each Active Volunteer.
(The only data that currently needs to be created is the People ID file that
ties the two tables together relationally)

Well, an Append query will certainly do that. Create a query based on
your People table, use a criterion that selects the active volunteers,
and change it to an Append query.

However, what does this Volunteer table give you that the select query
selecting Active Volunteers doesn't give you already??

Problem 2: I'd like the People Entry form to have a button to open a
Volunteer entry form, so that you can review/edit the data for that
volunteer. If that person is not a volunteer, I'd like the Volunteer Entry
to create a record for them, but Im concerned about when a user clicks on the
Volunteer button to open the Volunteer form and doesn't actually have data to
enter (no point in having an empty record created) maybe a save function?

IF the Volunteer table exists at all - which is what I'm questioning!
- then you may want to put it on a Subform of the People form. This
well ensure that the tables are kept properly linked and won't let you
put in data until a linking record exists, presuming that you have
defined the table relationships correctly.

John W. Vinson[MVP]

 

[Guest]

Thanks John!

I had actually managed to find some append syntax that worked with some
tweaking. Still working on figuring out how to accomodate adding new
volunteer interests. I made the decision to have a separate Volunteer table
only because it seemed to make more organizational sense, but perhaps I am
wrong. The Account table has a large number of records in it (11,000+),
whereas the Volunteer table only has 73 at the moment, but it is strictly a
one-to-one thing- so maybe you are right that this would be better tracked in
my larger table. Right now the Volunteer table is strictly to track
volunteer interests (i.e. a bunch of yes/no fields), but I could see it
growing to accomodate other info as well. If I created all those fields in
the Account table, that's a lot of fields for a lot of accounts that won't be
using those fields. I still wouldn't want to use a subform because that
would clutter up the main entry screen too much, but I suppose that I could
control my volunteer data manipulation through forms and queries rather than
a separate table. What would you recommend?
Thanks for the advice.

- Amanda

 

[John Vinson]

I had actually managed to find some append syntax that worked with some
tweaking. Still working on figuring out how to accomodate adding new
volunteer interests. I made the decision to have a separate Volunteer table
only because it seemed to make more organizational sense, but perhaps I am
wrong. The Account table has a large number of records in it (11,000+),
whereas the Volunteer table only has 73 at the moment, but it is strictly a
one-to-one thing- so maybe you are right that this would be better tracked in
my larger table. Right now the Volunteer table is strictly to track
volunteer interests (i.e. a bunch of yes/no fields), but I could see it
growing to accomodate other info as well. If I created all those fields in
the Account table, that's a lot of fields for a lot of accounts that won't be
using those fields. I still wouldn't want to use a subform because that
would clutter up the main entry screen too much, but I suppose that I could
control my volunteer data manipulation through forms and queries rather than
a separate table. What would you recommend?

I'd recommend a normalized many to many relationship. Rather than
separate Yes/No fields for interests, consider a three table
structure:

Account
AccountID
<your existing member fields>

Interests
InterestID
Description

VolunteerInterests
AccountID << link to Account, who's interested
InterestID << link to Interests, what they're interested in
Comments << level of interest or expertise
<other fields pertaining to this volunteer/this interest if needed>

If one of your members is interested in three areas, there'd be three
records in the VolunteerInterests table.

I'd still use a subform to enter data into this; it could be on a
separate popup form, or you could just use a Tab Control on the main
form to put the subform offscreen until it's needed.

John W. Vinson[MVP]