Urgent Query needed to fix database problem

G

Guest

last night my colleague messed up big time, and one of the table has had it's
data overwritten to one value.

ie: i have 3 tables, one for the booking, one for the home, and one for the
extra details. the extra details has a list number for the home, which has
changed to one specific value. the booking has the correct home.

how can i write a query to update the extra details table and replace the
incorrect home with the correct one from the bookings table?

Any help would be crreatly apprecaited, as the swine who messed this up is
away today.
 
E

EeOr

Can you give us more information? Which field links the Booking table to
the extra details table?

Jon
 
G

Guest

the home table has homeID and HomeName

the bookings table has homeID (which is a dlookup) and the extra details has
the bookingID and the HomID also.
I want to take the HomID and put its value into HomeID when the BookingID
is the same
 
E

EeOr

Ok Still not 100% clear, so the table Bookings has the correct ID for the
home and the Booking and the table ExtraDetails has the correct ID for the
booking and the wrong ID for the Home?

If so then this should work:

UPDATE ExtraDetails INNER JOIN Bookings ON ExtraDetails.BookingID =
Bookings.BookingID SET ExtraDetails.HomID = [Bookings]![HOMID];

Please backup your data before running this.

Hth

Jon
 
G

Guest

Thankfully, I was able to use an old backup to get most of the data back, and
simply have to manually change the last few bits.

Thanks for your help anyway, as it worked on some test data I made (just in
case the database gets corrupted by my colleague's bad code again ;)

EeOr said:
Ok Still not 100% clear, so the table Bookings has the correct ID for the
home and the Booking and the table ExtraDetails has the correct ID for the
booking and the wrong ID for the Home?

If so then this should work:

UPDATE ExtraDetails INNER JOIN Bookings ON ExtraDetails.BookingID =
Bookings.BookingID SET ExtraDetails.HomID = [Bookings]![HOMID];

Please backup your data before running this.

Hth

Jon



OTWarrior said:
the home table has homeID and HomeName

the bookings table has homeID (which is a dlookup) and the extra details
has
the bookingID and the HomID also.
I want to take the HomID and put its value into HomeID when the BookingID
is the same
 
J

John W. Vinson

e: i have 3 tables, one for the booking, one for the home, and one for the
extra details. the extra details has a list number for the home, which has
changed to one specific value. the booking has the correct home.

Ummm...

Sounds like you're storing the home information redundantly in both tables.
Why? If the Booking has the correct home, and if Booking is related one to
many to the ExtraDetails table, then you don't NEED - nor should you have! -
the home in the ExtraDetails table; you can always get to it by linking to the
booking table!

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top