Prev: Decimals
Next: Trying to update a field
From: Eric B on 16 Apr 2010 12:13
Thank you for the quick reply. That worked.

"Daryl S" wrote:

> 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
>
>
> "Eric B" wrote:
>
> > 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
 | 
Pages: 1
Prev: Decimals
Next: Trying to update a field