Trying to update multiple fields in the same record (same table)

[Eric B]

I am currently creating my second database using Access 2003. The first,
while still leaving me with a few headaches, did not seem to give me the same
problems as this project. I am trying to create a database to help keep
track of students standardized test scores. To get the scores I had to
download each individual test (for each grade level) for each year in Excel.
(Reading Total, Composite, etc.) Or, 12 total sets of scores.

I was able to create a table for student information and am now stuck with
trying to "merge" the actual test scores into a separate table. To start
with I imported 3 excel files (Comp 2010, Comp 2009, and Comp 2008) which
includes all the composite scores from the ITEDs for the past three years. I
want to combine all the test scores in a single table. All four tables
contain: St_Id (key), Comp_9, Comp_10, Comp_11. (Comp_# is the composite
score they earned in the # grade.) Each St_Id will have only one score /
test / test year.

I am trying to use an update query to move the test scores over to a new
table (ITED Data). I want to get rid of the individual test tables as that
just seems like clutter to me. This is where I am stuck. (All related
fields in different tables have the same name and Data Types and the St_Id is
linked amongst the tables.) My goal is to end up with 2 tables, one for
student information and one for test scores.

In the update query I am looking at:
Field: Comp_9 | Comp_10 | Comp_11
Table: ITED Data | ITED_Data | etc.
Update To: [Comp_2010].[Comp_9] | etc.

The only scores that seem to update are the scores for Comp_11. All other
records are blank.

I tried changing Update To: to look something like: IIf([fldname]=Null,[Temp
Comp 2009].[Comp_9],"") but still am not having luck.

Is this possible with a "simple" query or am I looking at having to code
(VBA) what I need?

I apologize for leaving a novel, but wanted to be as thorough as possible.

Thanks,
Eric

 

[Daryl S]

Eric -

I expect that for each year's file, each student will only have one of the
three columns filled in (e.g. a student in 9th grade in 2008 would only have
Comp_9 in the Comp 2008 table, only Comp_10 in the 2009 table, and Comp_11 in
the 2010 table. You would have students in 11th grade in 2008 that are not
in the 2009 or 2010 tables. You also don't want to replace the Comp_9 score
that was pulled for a student from the 2008 file with the NULL value that is
in that column in the 2009 table.

This means you need to deal with the nulls. I would recommend running an
update on only one column at a time from each table, and replace only where
there is no value in the table and the source table has a value. It will
look like this:

Field: Comp_9 | Comp_9
Table: ITED Data | Comp 2008
Update To: [Comp_2008].[Comp_9] |
Criteria: Is Null |is not null

Notice there is nothing in the Update To: area under the Comp_9 field for
the Comp 2008 table, as you do not want to update that table. You just need
to make sure it has a value. Do this for each column one at a time and for
each source table.

--
Daryl S

I am currently creating my second database using Access 2003. The first,
while still leaving me with a few headaches, did not seem to give me the same
problems as this project. I am trying to create a database to help keep
track of students standardized test scores. To get the scores I had to
download each individual test (for each grade level) for each year in Excel.
(Reading Total, Composite, etc.) Or, 12 total sets of scores.

I was able to create a table for student information and am now stuck with
trying to "merge" the actual test scores into a separate table. To start
with I imported 3 excel files (Comp 2010, Comp 2009, and Comp 2008) which
includes all the composite scores from the ITEDs for the past three years. I
want to combine all the test scores in a single table. All four tables
contain: St_Id (key), Comp_9, Comp_10, Comp_11. (Comp_# is the composite
score they earned in the # grade.) Each St_Id will have only one score /
test / test year.

I am trying to use an update query to move the test scores over to a new
table (ITED Data). I want to get rid of the individual test tables as that
just seems like clutter to me. This is where I am stuck. (All related
fields in different tables have the same name and Data Types and the St_Id is
linked amongst the tables.) My goal is to end up with 2 tables, one for
student information and one for test scores.

In the update query I am looking at:
Field: Comp_9 | Comp_10 | Comp_11
Table: ITED Data | ITED_Data | etc.
Update To: [Comp_2010].[Comp_9] | etc.

The only scores that seem to update are the scores for Comp_11. All other
records are blank.

I tried changing Update To: to look something like: IIf([fldname]=Null,[Temp
Comp 2009].[Comp_9],"") but still am not having luck.

Is this possible with a "simple" query or am I looking at having to code
(VBA) what I need?

I apologize for leaving a novel, but wanted to be as thorough as possible.

Thanks,
Eric

 

[Eric B]

Thank you for the quick reply. That worked.

Eric -

I expect that for each year's file, each student will only have one of the
three columns filled in (e.g. a student in 9th grade in 2008 would only have
Comp_9 in the Comp 2008 table, only Comp_10 in the 2009 table, and Comp_11 in
the 2010 table. You would have students in 11th grade in 2008 that are not
in the 2009 or 2010 tables. You also don't want to replace the Comp_9 score
that was pulled for a student from the 2008 file with the NULL value that is
in that column in the 2009 table.

This means you need to deal with the nulls. I would recommend running an
update on only one column at a time from each table, and replace only where
there is no value in the table and the source table has a value. It will
look like this:

Field: Comp_9 | Comp_9
Table: ITED Data | Comp 2008
Update To: [Comp_2008].[Comp_9] |
Criteria: Is Null |is not null

Notice there is nothing in the Update To: area under the Comp_9 field for
the Comp 2008 table, as you do not want to update that table. You just need
to make sure it has a value. Do this for each column one at a time and for
each source table.

--
Daryl S

I am currently creating my second database using Access 2003. The first,
while still leaving me with a few headaches, did not seem to give me the same
problems as this project. I am trying to create a database to help keep
track of students standardized test scores. To get the scores I had to
download each individual test (for each grade level) for each year in Excel.
(Reading Total, Composite, etc.) Or, 12 total sets of scores.

I was able to create a table for student information and am now stuck with
trying to "merge" the actual test scores into a separate table. To start
with I imported 3 excel files (Comp 2010, Comp 2009, and Comp 2008) which
includes all the composite scores from the ITEDs for the past three years. I
want to combine all the test scores in a single table. All four tables
contain: St_Id (key), Comp_9, Comp_10, Comp_11. (Comp_# is the composite
score they earned in the # grade.) Each St_Id will have only one score /
test / test year.

I am trying to use an update query to move the test scores over to a new
table (ITED Data). I want to get rid of the individual test tables as that
just seems like clutter to me. This is where I am stuck. (All related
fields in different tables have the same name and Data Types and the St_Id is
linked amongst the tables.) My goal is to end up with 2 tables, one for
student information and one for test scores.

In the update query I am looking at:
Field: Comp_9 | Comp_10 | Comp_11
Table: ITED Data | ITED_Data | etc.
Update To: [Comp_2010].[Comp_9] | etc.

The only scores that seem to update are the scores for Comp_11. All other
records are blank.

I tried changing Update To: to look something like: IIf([fldname]=Null,[Temp
Comp 2009].[Comp_9],"") but still am not having luck.

Is this possible with a "simple" query or am I looking at having to code
(VBA) what I need?

I apologize for leaving a novel, but wanted to be as thorough as possible.

Thanks,
Eric