Replacing a Value within a Query

[MisterMan]

This may not be possible, but here is my boggle.

I have a query that joins two tables, one table has very minute, down to school-building-level data. The other uses information on school districts, so that it applies to all school buildings in that district.

One thing that the joining query does it apply the results of one field for the district level information, to all schools. For example, if the district level table says that all schools have lunch programs, when I run that query it says that all schools in that district have a school lunch program.

However....

I have a case now, wherein two schools do not have the same information as every other school in the district. Is there a way to add something to to the query that would allow me to change the information for just those two schools? Please note that the information for school lunch program does not exist within the school level table, but only in the district.

Any help would be appreciated.

 

[dhookom]

If the information you want to store is based on the School then you will need to store it in the School table. The alternative would be to set up a third table to handle exceptions.

Duane
MS Access MVP

 

[MisterMan]

Could you walk my ignorant self through an example in using the third table?

 

[dhookom]

A third table could be used to store SchoolIDs for schools that have a different value from their District. You could then join this table to your query using the option to select all records from the existing school table and ones from the new table where they match. If a matching record isn't found in the new table then you can assume the value is the same as what is stored in the district table.

Duane
MS Access MVP