How can I update a distinct query?

[deanbri75]

I've got a database with every dorm room on campus (thousands of rooms.) Most of the rooms are doubles so there are two beds. Currently the database will have two records for double rooms i.e. Beech 101 and Beech 101. I need to add a field that will denote the separate beds like Beech 101 A and Beech 101 B.

I figured the best way to do this was to run a distinct query on all of the rooms (housing.UnitName), so it would only turn up each room once, and set the BedName field to "A". Then I could go back and update all null values to "B". (hope this is making sense so far.)

I got my distinct query just fine:

SELECT DISTINCT Housing.UnitName, Housing.BedName FROM Housing

This returns all the records that I need to set BedName to "A", however I can't update this query. So, I tried this:

SELECT *
FROM Housing
WHERE UnitName in (SELECT DISTINCT Housing.UnitName FROM Housing);

While this query is updateable, it doesn't return distinct values. (I get Beech 101 twice etc.)

Is there a way to do what to acomplish what i'm trying to do? Thanks in advance!

brian

 

[BNPMike]

Either one of two possibilties applies here. If each of your two records contains the same data then use DISTICT to create a new table. In the new table you will now have only one record for each dorm. Set this to A. Now do an append query back onto the same table only this time setting the field to B. You will now have two records for each dorm - one A and one B.

If on the other hand the records actually differ in other ways (as they are not normalised, you realise) then you've got a more complex problem which you'll need to share with us.

 

[JonFer]

You could create a copy of your current table and make an append query from the Distinct query to append records to the new table. Just add a field for the Bed value:

Bed: "A"

Run the query once, change the Bed value to B, then run it again. You can modify your table names afterwards so the new table gets the old table name.