UPDATE Query

[Shaun]

Hi,

I have (amogst others) three tables in my database named Bookings, User, and
Representative. A User and a Representative are different types of user,
however I now want to merge these tables into one - User. When a Booking is
made, the User_ID and the Rep_ID are stored in the Booking table. I have now
merged the two tables (User and Representative), how can I update the Rep_ID
column in Bookings so that it refers to the User_ID for each Representative
in the User table?

Thanks for your help

[Richard K]

"Shaun" <re**********@mania.plus.com> wrote in
news:bj**********@reader-00.news.insnet.cw.net:

Hi,

I have (amogst others) three tables in my database named Bookings,
User, and Representative. A User and a Representative are different
types of user, however I now want to merge these tables into one -
User. When a Booking is made, the User_ID and the Rep_ID are stored in
the Booking table. I have now merged the two tables (User and
Representative), how can I update the Rep_ID column in Bookings so that
it refers to the User_ID for each Representative in the User table?

Thanks for your help

If you have kept your Representative table, and if there are field(s) that
you can use to relate Representative to User (such as a Name field), then
you can use:

UPDATE (Booking INNER JOIN Representative ON Booking.Rep_ID =
Representative.Rep_ID) INNER JOIN [User] ON Representative.Name = User.Name
SET Booking.Rep_ID = [User_ID];

hth
Richard

[Jamie]

I don't know if this is possible unless you have the original tables. If you
do then I have a very uneligant solution for you, but it does mean that
there's no problems if people have say, the same name, or what ever.

First find the highest id in User, lets says it, 26, then change the type of
id in both the Representative table and the User table from autonumber to
number.

Then run this query on representative, replacing 26 with what ever number
you found earlier:
UPDATE Representative SET Representative.id = Representative.id+26;
and this one on the Bookings table, replacing 26 again:
UPDATE Bookings SET Bookings.rep_ID = Bookings.rep_ID+26;

Then copy the rows of the Represtative table and past them on the end of the
User table. Set the User tables id field back to autonumber and delete the
Representative table and your away.

Jamie

"Shaun" <re**********@mania.plus.com> wrote in message
news:bj**********@reader-00.news.insnet.cw.net...

Hi,

I have (amogst others) three tables in my database named Bookings, User, and Representative. A User and a Representative are different types of user,
however I now want to merge these tables into one - User. When a Booking is made, the User_ID and the Rep_ID are stored in the Booking table. I have now merged the two tables (User and Representative), how can I update the Rep_ID column in Bookings so that it refers to the User_ID for each Representative in the User table?

Thanks for your help