updating a table using another table.

[hongbin81]

I have a table with the following fields.

List Date Time Duration
COM-HB-NEVERBETTER-0506.mpg 5/12/2003 10:13:11 PM 0
COM-HB-DEALERLAUNCH-0506.mpg 5/13/2003 11:58:12 AM 0
COM-HB-DEALERLAUNCH-0506.mpg 5/13/2003 6:27:44 PM 0
COM-HB-DEALERLAUNCH-0506.mpg 5/13/2003 10:14:37 PM 0
COM-HB-CHASE-0506.mpg 5/14/2003 7:53:43 AM 0
COM-HB-CHASE-0506.mpg 5/14/2003 11:58:47 AM 0
COM-HB-CHASE-0506.mpg 5/14/2003 6:27:33 PM 0

so on....

and I have a Master's List with the following fields
Company Title Duration
HB Neverbetter 30
HB Dealerlaunch 30
HB Chase 45
so on...

With a click of a button, I want it to update the Duration on the first table with zeros to whatever the master's list has for its duration. Problem is....the first table's List Field is a filename of COM-HB-NEVERBETTER-0506.mpg. How do I use the Master's List to update with a filename that has other characters involved?

 

[JeremyNYC]

Check out the MID function in the help files. I would suggest breaking that first field into a bunch of different fields. Also, there are multiple records in the first table for each one in the second one. Doing an update query will put the same value in for each matching record. Is that what you want?

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[hongbin81]

I guess that's basically what I want...but the company and title has to much with the first table given.

 

[hongbin81]

I'm not sure if MID function would work.