Updating Records Question

[Guest]

I keep some Access databases for a school (2003). All are pretty
straightforward. I have kept them for a few years now.

In an effort to make these more available to some staff, I am re-working a
few of them. The change is that I am keeping student data in a single
database which is then linked to other databases. The idea is that I will
maintain the student data so we don't have numerous people duplicating the
effort and the staff will maintain their data (discipline, health, etc.)
themselves.

But this brings me to something I have never quite figured out and would
like some assistance. I keep the student data in various tables. What is
linked to these other databases are demographic data (ID#, firstname,
lastname, address, homeroom assignment, etc.). Presently I have last school
year's data in such a table.

I receive dumps from the district database of these data periodically. How
do I import these data to my demographic table to update the list of students?

For instance, if I have a student John Smith who was in second grade last
year in room 40, but this year is in third grade in room 53, I would like his
record updated to reflect that.

I also do not want to delete records of students who no longer attend our
school. Until now, I just made a new database each year with the new student
data. I want to get beyond that.

I appreciate your thoughts on how best to approach this.
Thank you.

 

[Guest]

Hello Shoelaces -

I receive dumps from the district database of these data periodically.
How do I import these data to my demographic table to update the list
of students?

To update a table with existing records, use an update query. To add new
records to an existing table, use an append query. The following KB article
should be helpful:

HOW TO: Convert a Select Query to an Action Query in Access 2000
http://support.microsoft.com/?id=304355

Note: This KB article applies to all versions of Access. It is not specific
to Access 2000.

For instance, if I have a student John Smith who was in second grade last
year in room 40, but this year is in third grade in room 53, I would like his
record updated to reflect that.

Import the data dump into a new temporary table. Then create a select query
that involves this temporary table, and the table that you wish to update,
with the student ID field used to join the two tables. Select the field(s) in
the table that you wish to update. Apply any criteria, as required, to
restrict the query. Run the query as a select query, to verify that it
returns the desired records.

Now, convert the query into an update query. You should see a new "Update
To" row in the query design grid. Enter the name of the temporary table and
field. For example:

Field: Grade
Table: Name of permanant table
Update To: [TempTableName.FieldName]

Use square brackets and separate the name of the table and field with a
period. If your table and / or fieldname includes spaces, you may need to use
the following form:

Update To: [Temp Table Name].[Field Name]

You should create a back-up copy of your database first, before running any
action queries. In case something goes wrong, it's much easier to recover
from a back-up copy.

I also do not want to delete records of students who no longer attend our
school. Until now, I just made a new database each year with the new student
data. I want to get beyond that.

Add a boolean (Yes/No) field to your table, named perhaps CurrentStudent.
Use this field as a criteria for your forms and reports.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
____________________________________________

:

I keep some Access databases for a school (2003). All are pretty
straightforward. I have kept them for a few years now.

In an effort to make these more available to some staff, I am re-working a
few of them. The change is that I am keeping student data in a single
database which is then linked to other databases. The idea is that I will
maintain the student data so we don't have numerous people duplicating the
effort and the staff will maintain their data (discipline, health, etc.)
themselves.

But this brings me to something I have never quite figured out and would
like some assistance. I keep the student data in various tables. What is
linked to these other databases are demographic data (ID#, firstname,
lastname, address, homeroom assignment, etc.). Presently I have last school
year's data in such a table.

I receive dumps from the district database of these data periodically. How
do I import these data to my demographic table to update the list of students?

For instance, if I have a student John Smith who was in second grade last
year in room 40, but this year is in third grade in room 53, I would like his
record updated to reflect that.

I also do not want to delete records of students who no longer attend our
school. Until now, I just made a new database each year with the new student
data. I want to get beyond that.

I appreciate your thoughts on how best to approach this.
Thank you.

 

[Guest]

Import the data dump into a new temporary table. Then create a select query
that involves this temporary table, and the table that you wish to update,
with the student ID field used to join the two tables. Select the field(s) in
the table that you wish to update. Apply any criteria, as required, to
restrict the query. Run the query as a select query, to verify that it
returns the desired records.

That does it. Thank you.

 

[Guest]

Glad I could help.

Thank You for marking my response as an answered question.

Tomn
________________________________________