Basic Update Query Question

[BFP]

Hi All.

Our database is updated every night with new data. One of the columns in the main table that is updated nightly is called FileName and another is Date (in the format [MM/DD/YYYY HH:MM:SS AM/PM]).

I would like to be able to automatically populate another column called Owner (Not part of data dump) based on the FileName, where Owners are assigned to file ranges, and for data from today's date (starting at 12:00). For example, Joe may get FileName ranges from 1001.html to 1100.html, Sue may get 1101.html to 1200.html, and so on.

I assume this would be accomplished with a simple update query. Is this the way to go and if so, what is the query?

Thanks,

BFP

 

[Golom]

Assuming that it's recorded in your system that Joe gets "1001" to "1100" ; Sue gets ... etc.

Assuming further that that's in a table of the form
[tt]
tblAssigned
PersonID From To
Joe 1001 1100
Sue 1101 1200
[/tt]
Then

UPDATE MainTable M INNER JOIN 

 (Select X.FileName, A.PersonID From tblAssigned A
  Where Left(M.FileName, Instr(1,M.FileName,".")-1) >= A.From
    AND Left(M.FileName, Instr(1,M.FileName,".")-1) <= A.To) As X

ON M.FileName = X.FileName
 
SET M.PersonID = X.PersonID

Access may not think that's an updatable query and you may need to run a make-table to build the stuff that's in the sub-query.

 

[BFP]

Hi.

This did not quite work correctly as the query is prompting for X.FILE_NAME and M.FILE_NAME.

FILE_NAME has a text range of 00001300.html to 00002000.html.

The tables are organized as follows:
Table1: Master
Column 1: FILE_NAME (with range listed above)
Column 4: COMMENT_DATE with format specified in 1st posting.
Column 6: IMD_OWNER with names like Joe, Sue, etc.

Table2: Ranges
Column 1: From: list starting text range (say 00001300.html)
Column 2: To: list ending text range (say 00001399.html)
Column 3: Owner: lists names like Joe, Sue, etc.

Query:

UPDATE Master AS M INNER JOIN 

[Select X.FILE_NAME, A.Owner From Ranges A
  Where Left(M.FILE_NAME, Instr(1,M.FILE_NAME,".")-1) >= A.From
    AND Left(M.FILE_NAME, Instr(1,M.FILE_NAME,".")-1) <= A.To]. AS X 

ON M.FILE_NAME = X.FILE_NAME 
SET M.IMD_OWNER = X.Owner;

I am getting prompts for X.FILE_NAME and M.FILE_NAME and I would like to include a restriction that only rows with today's date are updated.

Thanks,

BFP

 

[PHV]

UPDATE Master AS M INNER JOIN Ranges AS A
ON M.FILE_NAME >= A.From AND M.FILE_NAME <= A.To
SET M.IMD_OWNER = A.Owner;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BFP]

Hi..

Two issues remain:
1) I am now getting a message "Cannot join on Memo, OLE, or Hyperlink Object..." The data in the table Master is dumped to every night via a link to it from another dB.
2) I originally wanted to include a restriction that the update query only be run on data dumped since the last time the dB was opened.

Thanks,

BFP

 

[PHV]

UPDATE Master
SET IMD_OWNER = (SELECT Owner FROM Ranges WHERE [From]<=Master.FILE_NAME AND [To]>=Master.FILE_NAME)
WHERE IMD_OWNER Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BFP]

Hi PH.

This is definitely closer as the error is gone now, but the behavior I am seeing is that the update query creates a new table with one column and populates it with the values already set in "IMD_Owner". The only thing I can think is that Master.FILE_NAME has a data type of "Memo". I changed Ranges.From and Ranges.To to have the same data type, but to no avail. (I am not sure why Master.FILE_NAME is set to memo. The person that must have set it up that way was the person that set up the link to do the nightly dump.)

BTW: I had to remove the line

 WHERE IMD_Owner Is Null;

because IMD_Owner is never null. (I forgot to mention that the values were already populated and I want to overwrite them with the new values.)

Thanks,

BFP

 

[PHV]

the update query creates a new table
????

UPDATE Master
SET IMD_OWNER = (SELECT Owner FROM Ranges WHERE [From]<=Master.FILE_NAME AND [To]>=Master.FILE_NAME)
WHERE COMMENT_DATE = Date()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BFP]

Yes and this started out as an Inner Join because I wanted to update Master.IMD_OWNER with new values (once, then run the query every time the dB is opened), but I take it that an Inner Join can not be done on a table that is linked?

The new query creates an empty table, so I updated the Where: WHERE ID >= 0, although I now see how "IMD_OWNER Is Null" should work on all rows dumped since the last time the query was run, so that will be the requirement once the table has initially been populated with the correct values.

But I am back to creating a single-column table with the original values of Master.IMD_Owner.

So, to summarize: I am trying to correct the current values in IMD_Owner and then run a slightly different query to update blank entries in IMD_Owner in the Master table if possible.

Sorry that this message has gotten jumbled up over several postings.

Regards,

BFP