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?

 

[GingerR]

are the strings in LIST field always the same format? always the same placement/length (except for the TITLE that's in the middle)?

COM-HB-{TITLE HERE}-mmdd.mpg?

if so, then you can add another column to the first table, where you will extract the TITLE to. using built-in functions LEN and MID you can figure it out.

you can write an update query to fill your new field with the TITLE which is gleaned from the LIST field:

Mid(

• ,8,Len(
  • )-16)
    
    run the update query, then there you have your TITLE in your first table. then just proceed with another update query that joins the two tables together on that TITLE field, and updates the DURATION field. you can then call the two queries in a button's OnClick event with Docmd.OpenQuery.
    
    however, i'd suggest that you don't update the data directly into the first table. just join the two tables whenever you need to see that data. it's kind of against good database design to be duplicating data like that. is this a one-time thing? is this imported data? may upon importing it, you concoct that TITLE field in the first table using the query expression i supplied.
    
    anyhow, there you go. if you want to supply further details on your process, why this data is like it is and why you want to update the DURATION when and how you do, maybe we can find a better way for you to proceed.....
    

 

[hongbin81]

Thanks for the reply.
But the problem is length are not always the same.

It could be
LOC-HONDA-TITLE-mmdd.mpg
LOC-CENTURY21-FIRST_R0.mpg

etc...

I'll give it a try tho.

 

[GingerR]

you could then make an update query that fills your new TITLE field:
put both tables into a query, but with no join between them.
make it an update query.
bring down the new TITLE field
bring down the LIST field
in the criteria for LIST, put

Like "*" & [Master List]![Title] & "*"

sub in correct names for the master list table, and title field in that table.

in the UPDATE TO portion of the TITLE (the 'new' title field in the first table), put [Master List]![Title].

so this will look for LIST fields like *TITLE* and put in the TITLE into the new field.

 

[hongbin81]

question:

Field TITLE LIST
TABLE tblMaster tblLogs
UPDATE TO [tblNew]![Title]
CRITERIA Like blah blah

this is what I have.
Problem is that LIST field should come first then the TITLE field. But When I try to move the LIST field where the Title field is and vice versa....
When i close it and open it again.....the TITLE field would show first automatically.
How can I fix that?

 

[GingerR]

it doesn't matter what order things are in the query.

but i'm not sure if you have the right fields/tables here. maybe you do and i'm just confused. not sure what tblNew is. i think you should make a brand new field in tblLogs that is called TITLE. just to keep things not confused, call it NewTitle i guess.

Field LIST NewTitle
TABLE tblLogs tblLogs
UPDATE TO [tblMaster]![Title]
CRITERIA Like "*" & tblMaster!TITLE & "*"

 

[hongbin81]

The order seem to matter...
if you put NewTitle first....Inputbox pops up asking for a [tblMaster]![Title]...

but when i close it and open it back again, the 'List' is automatically placed first.

 

[hongbin81]

It worked!
I'm going to experiment a little more.!

Thanks GingerR! ^^