Extracting Info from Two Fields?

[Nancy in Redmond, WA]

Hi. I'm a very, very novice Access user. I use it to maintain a class list
for a co-op preschool. The list was already created when I took over this
job. I need to take an existing database that contains fields such as name,
address, phone, e-mail, etc. and create a new database that only contains the
fields for name and e-mail. Rather than manually delete all the fields I
don't need in this new database, I'm sure there must be a way to extract only
the information I need. I'm actually going to have to do this same thing for
the two or three previous years' class databases in order to create one large
alumni contact database. So, if this makes sense to anyone out there, can you
help me figure out how to extract the info I need from two or three databases
and combine the results into one new database? Yikes! Help me please!

 

[RBear3]

First, I would recommend using a separate FirstName and LastName field.
That will allow you to pull only the first name to some reports, or to pull
various formats of the name (last, first or first, last or first initial and
last name etc.)

To do what you are trying to do, I'd create a new table and then run several
APPEND queries to pull the data from your "old" tables and insert it in your
new table. Since you are adding all the records to one new table (which is
the correct way to do it) you will need to figure out how to track when a
student attended. What if they are in the 2005 table and the 2006 table?
Do you need to know that? If so, I'd recommend adding a separate related
table to track attendance. There are lots of ways to do this, so you'd have
to tell us more about your needs and what data you have available if you
want help with that.

You may also want to add a field to your student table to determine if they
are "active" or not. This will allow you to pull only current students (if
desired) to some of your reports, forms, and drop-down fields, but will
still allow you to pull inactive students to other forms and reports.

Post back as you run across specific questions.

 

[John W. Vinson]

On Mon, 30 Apr 2007 09:26:00 -0700, Nancy in Redmond, WA <Nancy in Redmond,

Hi. I'm a very, very novice Access user. I use it to maintain a class list
for a co-op preschool. The list was already created when I took over this
job. I need to take an existing database that contains fields such as name,
address, phone, e-mail, etc. and create a new database that only contains the
fields for name and e-mail. Rather than manually delete all the fields I
don't need in this new database, I'm sure there must be a way to extract only
the information I need. I'm actually going to have to do this same thing for
the two or three previous years' class databases in order to create one large
alumni contact database. So, if this makes sense to anyone out there, can you
help me figure out how to extract the info I need from two or three databases
and combine the results into one new database? Yikes! Help me please!

Actually you DON'T need to create a new Database (a .mdb file container for
multiple Tables, Forms, Reports and other objects), or even a new Query.

Create a Query based on your table. Select the fields that you want to see
(name and email I presume, though I'd really recommend having separate
FirstName and LastName fields).

If you have a separate .mdb file for each year... well, don't; that makes your
work more difficult, not easier! What you can do is create a brand-new .mdb
file; use File... Get External Data... import to import one of the class
lists. You can then use File... Get External Data... Link to *link* to the
other databases, and then create an Append query to append the data from the
second database's table into the first table you imported. You will very
likely have duplicate names; to prevent this from happening, you can first
open the table in design view, and use the Indexes tool (looks like lightning
hitting a datasheet) to create a unique Index on the combination of fields
that uniquely identify a person (say, name and EMail). You may still get dups
if you have "Richard Jones" in one database and "Dick Jones" - *maybe* the
same person - in the other, or if someone's email address has changed, but
these can be fixed up manually.

John W. Vinson [MVP]