Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: UpDate Query Problems . . .

    TblScmlsNormalize
    Field 1 UID*
    Field 2 NormalizedOfficeName

    tblScmls
    Field 1 UID (Not keyed)
    Field 2 ListName
    Field 3 UID (Not Keyed)
    Field 4 SellName

    What I'm attempting to do here is UPDATE the Scmls.ListName AND Scmls.SellName fields based on ScmlsNormalize.NormailzedOfficeName

    Whew!

    Thanks . . .


    Code:
    SELECT ScmlsNormalize.NORMALIZEDOFFICENAME, Scmls.ListName, Scmls.SellName
    FROM ScmlsNormalize INNER JOIN Scmls ON (ScmlsNormalize.UID = Scmls.SellID) AND (ScmlsNormalize.UID = Scmls.ListID);

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    UPDATE is unlikely to happen with a SELECT query.
    ...it more regularly takes the general form:
    UPDATE myTable SET this = blah WHERE that = whatever

    completely baffled by the idea that tblScmls.Field1 and .Field3 seem to be the same.

    you might have to add a few words of explanation to get a more sensible response.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you explain why you want to UPDATE at all?

    seems to me you can pull the normalized names whenever you SELECT

    what you should do, instead of updating ListName and SellName, you should remove those two columns altogether, and simply join fields 1 and 3 (shurely they don't have the same name, UID) to the normalized table twice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Code:
    NOT MORMALIZED			
    E120	ADVANCE REALTY	E120	ADVANCE REALTY
    E120	ADVANCE REALTY	E120	ADVANCE REALTY
    E120	ADVANCE REALTY	PB11137	PARK PLAZA REALTY
    PB6630	ADVANCED REALTY	H02997	STAR REAL ESTATE
    NORMALIZED			
    E120	ADVANCE REALTY CC	E120	ADVANCE REALTY CC
    E120	ADVANCE REALTY CC	E120	ADVANCE REALTY CC
    E120	ADVANCE REALTY CC	PB11137	PARK PLAZA REALTY SSB
    PB6630	ADVANCE REALTY DB	H02997	STAR REAL ESTATE HB
    tblScmlsNormalized has the normalizedofficenames that must be corrected (Upated) in the Scmls.ListName and Scmle.SellName.

    The tblScmls has roughly 4,000 records like this. The tblScmlsNormalized has 620.

    I need an UPDATE query to change any of the 620 office names that might be in the tblScmls.

    Thanks . . . Rick

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Okay,this runs but does not UPDATE [Scmls].[ListName]

    Code:
    UPDATE Scmls INNER JOIN ScmlsNormalize ON Scmls.ListID = ScmlsNormalize.UID SET Scmls.ListName = [Scmls].[ListName];
    Any ideas?

    Thanks . . . Rick

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rick, you're not paying any attention to what i said, are you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Not Sure I Understand . . .

    . . . "you should remove those two columns altogether, and simply join fields 1 and 3 (shurely they don't have the same name, UID) to the normalized table twice"

    I'm sorry Tony, I don't understand your suggestion. Do you mean query fields 1 an 3 against field 1 in the Scmls.Normalized table? Does this become an OUTER JOIN?

    Looking at my example of my NORMALIZED and NOT NOTMALIZED tables you can see that there are many examples of the same UID referrence in the Scmls table. This shows 4 examples out of 4,000!

    These numbers are not KEYED in the SCMLS table and they don't need to be.

    They are keyed in the ScmlsNormalized table.

    In Paradox ObjectPal this works well.
    Code:
    qbe=Query
    
    ScmlsNormalize.db | UID | NormalizedOfficeName |
                      | _ID  | _Name |
    
    Scmls.db | ListID | ListName |
             | _ID    | changeto _Name  |
    
    endquery
    if not qbe.executeQbe()then
    errorShow()
    	return
    	endif
    Access has proved to be a different animal for me, but I'm beginning to like it.

    Thanks . . . Rick

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's start over, okay brad?

    why do you have two tables? why are you trying to update one from the other? will one of them be discarded afterwards? or is this a permanenet situation? it seems like there is already plenty of redundant information, why are you redundantly updating one table from the other?

    that's the part i would like to discuss, without even thinking about the sql you're going to need
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    let's start over, okay brad? . . . Rick . . .

    "why do you have two tables? "
    The Scmls table is downloaded once a month, up to 6,000 records.

    "why are you trying to update one from the other? "
    The office names in the Scmls table are so bungled, miss spelled and unidentifiable that I normalize them according to my clients wishes for tracking, sorting, comparison, etc. purposes.

    "will one of them be discarded afterwards? or is this a permanent situation?"
    The Scmls table is archived after all of my calculation queries have run and reports printed.

    "it seems like there is already plenty of redundant information, why are you redundantly updating one table from the other?"

    Because the monthly (Scmls) downloaded data is a mess the way it is. No one can make much sense of it.

    (Did you watch Seattle lose to chicago? I’m rooting for the San Diego Charges later today as my sister’s older girl is a cheerleader for them. She is on the Chargers Calendar Cover.)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you give the actual table and column names, please (not "field 1" etc)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Tale & Fields

    Here it is . . .

    All fields are text fields

    Scmls.db
    ListID
    ListName
    SellID
    SellName

    ScmlsNormalize.db
    UID keyed*
    NormailzedOfficeName text

  12. #12
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Half Completed Success

    This code works perfectly in updating the Scmls.Listname field.
    Code:
    UPDATE Scmls INNER JOIN ScmlsNormalize ON Scmls.ListID = ScmlsNormalize.UID 
    SET Scmls.ListName = [scmlsnormalize].[normalizedofficename];
    Now, How do I include the Scmls.SellName in this query at the same time?

    Can I just add . . .
    AND Scmls.SellName = [scmlsnormalize].[normalizedofficename]; ?

    Rick

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need two statements
    Code:
    UPDATE Scmls INNER JOIN ScmlsNormalize ON Scmls.ListID = ScmlsNormalize.UID 
    SET Scmls.ListName = [scmlsnormalize].[normalizedofficename]
    Code:
    UPDATE Scmls INNER JOIN ScmlsNormalize ON Scmls.SellID = ScmlsNormalize.UID 
    SET Scmls.SellName = [scmlsnormalize].[normalizedofficename]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Thanks Rudy . . . could I place a command button on a form and then attach them in a code section of the form?

    Something like . . .

    Dim strSQL As String
    strSQL = "... ListName UPDATE query GOES HERE ...”
    DoCmd.RunSQL strSQL

    Dim strSQL As String
    strSQL = "... SellName UPDATE GOES HERE ...”
    DoCmd.RunSQL strSQL . . ?

    Or is there an even easier way?

    Thanks . . . Rick

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yes, but DON'T
    faster (and less nag-O-grams from mr Gates (and no messing with .SetWarnings which is risky))

    Dim strSQL As String
    strSQL = "... ListName UPDATE query GOES HERE ...”
    currentdb.execute strSQL

    'Dim strSQL As String <<<<< it is already declared above
    strSQL = "... SellName UPDATE GOES HERE ...”
    currentdb.execute strSQL

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •