Update Query - School names

[Kevin Labore]

I have an access table which I need to update the school names
The school codes(SchCode) are codes are consistent from year to year but the
school names(SchName) are spelled differently year to year.
Since I am doing reports with pivot tables I need to be sure the names are
consistent(exactly the same, other wise the pivot report either wont be
accurate or wont be easier to read) (i.e.. "My Town School" vs. "MyTown
Scho" etc)

The field names I would use to update (year(of data), SchCode(the code for
the School) , SchName( the school name))
I figure it would be easier to change any previous year name to the current
years SchName
My Question is how to write an update query to accomplish this or if another
approach would make more sense
I have seen update queries to replace based on specific criteria but this
different.
If Schcode = AND (<year 2006) then replace SchName with SchName (year 2006)

I don't want to have to try to figure what schools names needs to be fixed
and fix them one by one by hand.(as there are over 1/4 Million record)

Thank you in advance for any assistance you can offer.

Kevin Labore

 

[raskew via AccessMonster.com]

Hi Kevin -

Please correct me if I'm wrong, but it sounds as if users are
allowed/required to enter whatever school name or abbreviation feels good at
the moment. If so, that smacks of a design problem. For each SchCode there
should be one SchName, and users should not have the ability to arbitrarily
modify it to suit their mood.

Think this is going to be labor intensive, but I'd want to develop a
mechanism (combo box) to limit each SchCode to just one SchName. You'll
need two tables (tblSchNames), consisting of:
SchCode - number or autonumber, no duplicates allowed
SchName - text

tblInfo, which includes just the SchCode, with a relationship set to
tblSchNames.

For the users, they are presented with a form with a combo box displaying
SchNames. They select the one they want, and the SchCode is stored. No
options to monkey-around with the name.

Please provide us a little more info.

Best wishes - Bob

 

[John Spencer]

If the school code is consistent from year to year, then I would just have
build table that consisted of the SchoolCode and the desired (current?)
school name. And I would link to that and ignore the school name in the
existing table.

SELECT CurrentTable.SchCode, NewTable.SchName
FROM CurrentTable INNER JOIN NewTable
On CurrentTable.SchCode = NewTable.SchCode...

I guess you could UPDATE the CurrentTable
UPDATE CurrentTable INNER JOIN NewTable
On CurrentTable.SchCode = NewTable.SchCode
SET CurrentTable.SchName = [NewTable].[SchName]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Kevin Labore]

The data is raw data available at the states Department of Education website
which consists of tests scores, in 3 subjects (Reading, Writing,
Math(Arithmetic))
The School Names should be consistent I agree -- the data (at least which is
available public) is of poor design to work with(maybe that was intentional)
I also had to use filters to filter garbage row data, and some other info
(has it had summary by school, district, etc))

The problem is that I have no control over how the data got here ---the
school names certainly should be in some kind of lookup table and be
consistent
--- part of the problem might arise is that I imported about 10 different
CSV files (1 for each grade (3-8) ,each subject(Writing is only tested only
5 & 8), and each year(new testing so only the past 2 year)) Still the data
should could be consistent regardless of subject, year, etc.

I am generating some comparison reports of how our local schools compare to
the state, themselves, and other specific schools (when necessary) in order
to track progress and our schools stand. The problem of how, why the data
got generated this way is moot as I have no control or say to correct that
problem.
My problem is --- this is the data I have to work with --- while fixing the
problem of the data at the source makes the most sense --- I cant make that
happen
In fact the data in general is not consistent from year to year (even some
of the categories or subcategories are not named the same --- but the
category code is.
Stupid and frustrating believe me I know.

Below is a crosstab view of one of the pivot view showing show how Cornish
did in math (TY vs LY) as well as "all"(all schools in state)
There data consists of MeanScaledScore , and levels 1-4 (4 level of
proficiency), # students enrolled, # students test, etc)
The problem I am encountering if I pivot based on SchName(School Name) that
because the names aren't the same exactly (due to different field length or
whatever from other years) then I cant get desirable results.

Math 2006


Grade
3 4 5 6 7 8 Total
Average of MeanScSc Cornish Elementary School 2005 333.00 440.00
537.00 644.00 746.00 834.00 589.00
2006 334.00 435.00 540.00 642.00 744.00 845.00 590.00
Diff 1.00 (5.00) 3.00 (2.00) (2.00) 11.00 1.00
(All) 2005 343.54 443.16 542.96 641.89 741.02 840.16 547.45
2006 344.31 443.34 543.53 643.58 741.79 840.62 548.15
Diff 0.76 0.18 0.57 1.69 0.78 0.46 0.70
Diff vs Comp (10.31) (8.34) (3.53) (1.58) 2.21 4.38 41.85


Unfortunately the update query is not a simple one because I need to replace
field data based on what in other records/fields.
My only solution is to fix the data I have to work with and not to fix it at
the source (the more desirable solution)

 

[raskew via AccessMonster.com]

Kevin-

My sympathies go out to you. Your situation is just bizarre and not
something anyone should have to deal with. What state is this?

Bob

If the school code is consistent from year to year, then I would just have
build table that consisted of the SchoolCode and the desired (current?)
school name. And I would link to that and ignore the school name in the
existing table.

SELECT CurrentTable.SchCode, NewTable.SchName
FROM CurrentTable INNER JOIN NewTable
On CurrentTable.SchCode = NewTable.SchCode...

I guess you could UPDATE the CurrentTable
UPDATE CurrentTable INNER JOIN NewTable
On CurrentTable.SchCode = NewTable.SchCode
SET CurrentTable.SchName = [NewTable].[SchName]

I have an access table which I need to update the school names
The school codes(SchCode) are codes are consistent from year to year but

[quoted text clipped - 21 lines]

Kevin Labore

 

[Kevin Labore]

Kevin-

My sympathies go out to you. Your situation is just bizarre and not
something anyone should have to deal with. What state is this?

Bob

Hi Bob

yeah there many bizarre things --- the subcategories are different from one
year to next (for the same category code)
I can deal with the issue --- I thought it was important to bring the
Question here to try to find help as I am not an expert (yet at least --
more experience with Excel) , I believe there is a solution, I needed help
and I am sure some one else has run into a similar situation (although the
shouldnt have to) and could benefit from the solution. Basically I think the
solution needs to be written in code as it needs to loop through the
records checking for matching SchCode and fix the SchNames

The State is NH (New Hampshire) the scores are for NECAP (New England Common
Assestment Program) --- part of the no child left behind...
I believe each state must format what they put out because when I looked at
VT the format was different
The whole idea of raw data, CSV Files is to be able to do custom reports ---
be nice if it was setup like it should

Thanks for the sympathies and suggestions


Kevin

 

[Kevin Labore]

HI John

that looks like it might be what I need --- at least give me something to
go on.

Thanks for the help

Kevin