Results 1 to 11 of 11
  1. #1
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25

    update query question

    Hello everyone,
    I am reaching out to all of you if hope to get help. I have a table that contains CompnyID, ParentCompanyName. I built a query using this table as a source which takes ParentCompanyName and manipulate the data to generate UniqueParentCompanyIDs and then I wanted to update add this column "Parent Company ID" to my existing table by using an update query. The obstacle that I came across with is that I was unable to do both steps in the same query. Is this something that can be done in one query? If yes, can you please help me to figure this out?


    SQL for my query that manipulates the data to get Parent Company ID as follows:

    SELECT ListOfParentCompanies.CompanyID, ListOfParentCompanies.[Parent Company Name], Trim(Left([Parent Company Name],8)) & "" & "101" AS [Parent Company ID]
    FROM ListOfParentCompanies
    ORDER BY ListOfParentCompanies.CompanyID;

    Thanks for your help.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How about:

    UPDATE ListOfParentCompanies
    SET [Parent Company ID] = Trim(Left([Parent Company Name],8)) & "" & "101"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have a requirements document that describes what you are trying to do?

    First, I recommend you do not use any names in Access that have embedded spaces for any tables, fields or objects.

    Suppose you have some records in this table, what happens if:

    -an existing ParentCompany gets bought by a new Company?
    -you find a new company --is it a parent? How do you know? Where are you getting current data?

    Is a single Company automatically a ParentCompany?

    How do you define ParentCompany?

    Perhaps a structure (and this is pure guessing while typing)


    tblCompany
    CompanyId PK
    CoompanyLegalName
    CompanyOperatingName
    otherCompanyINfo

    tblCompanyRelationship

    RelationshipID PK
    CompanyID FK to tblCompany
    IsParentOf long to hold CompanyID of subsidiary companies [1 record per subsidiary]
    other info about the relationship (Dates, Public/Private...???? whatever you may be interested in..)
    possible maintenance info .....who modified the record last, when was that exactly....etc


    tblCompany--->tblCompanyRelationship
    where tblCompany may be the Parent of 0,1 or many other companies (depends on your definition)

    Just some thoughts to consider.
    Good luck.

  4. #4
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Hello pbaldy,
    Thank you for your response. I have tried this approach before but the issue is that the SQL takes Parent Company Name column, manipulates the data how I needed and posts this data into the came column. It should create another column: Parent Company ID and add those records there and leave the data in the Parent Company Name untouched.
    Input looks like this:
    CompanyID; Parent Company Name
    111 Apple
    121 Pear
    Output should look like this:
    CompanyID; Parent Company Name; Parent Company ID
    111 Apple Apple101
    121 Pear Pear101
    Thanks.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    An update query won't create a new field, but it can set the value of a new field you create. You didn't notice I updated a different field?

    By the way, you might play with the calculated field data type for a new field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    pbaldy,
    Thank you for your reply. I did notice the change. ( I misspelled word "same" in my previous reply). I tied to add a blank field into the source table and then do update query, but because I want to keep the Parent Company Name column as in original state and update manipulated records into blank Parent Company ID column and since the Parent Company Name column is used as source, the query gives me an error. I can create work around with make a table, because I wanted to reduce a quantity of the queries and tables in my BD, I wanted to see if I can do it in one stop.

    Thanks for your input.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear on what you're saying. Done correctly, the name column wouldn't change, it would just be used to update the new column. I just tested:

    UPDATE TableName
    SET Test = [Field1] & " : " & [Field2];

    The Test field was updated with the values from the other 2 fields, which were unmodified. What is the SQL of the query you tried to run?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Pbaldy,
    Thank you for your reply. I did my work around... So, we can consider this issue as being resolved. I built an select query that manipulated the data to get the desired outcome and then added the result into a new column. Then, I built another query that creates a new table and posts the data from the first query into the table. Then I wrote a VBA code that calls both queries. Thanks.

  9. #9
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    pbaldy,
    I was to quick to say that my issue is solved. But when I went back and tried to organized my process I did not like how this work around works.
    I came up with the following code base on some of the information that you mentioned earlier "update query can't add a column but I can set values first and then update them".
    VBA:
    Dim db As Database
    Dim strTableName As String
    Dim tdf As TableDef
    Dim fld As Field
    Dim strSQL As String
    Dim rst As Recordset

    Set db = CurrentDb()
    strTableName = "Copy Of ListOfParentCompanies"

    'I am taking my table and adding a new column
    DoCmd.RunSQL "ALTER TABLE [Copy Of ListOfParentCompanies] ADD [Parent Company ID] VARCHAR(30);"

    'I wanted to add default values to this column so I could update them further down the stream but unfortunately I hit a wall here. The values do not appear no matter what I tried.
    Set tdf = db.TableDefs(strTableName)
    CurrentDb.TableDefs("Copy Of ListOfParentCompanies").Fields("Parent Company ID").Properties("DefaultValue") = "0"

    ' Code to run an update SQL
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [Copy Of ListOfParentCompanies] SET [Parent Company ID] = [Parent Company ID] WHERE [Parent Company ID] = 0"
    DoCmd.SetWarnings True
    rst.Close
    Set rst = Nothing
    Would you be able to help me to figure out why the default value is not populating? What else could be done to make it happened.
    Thanks,

  10. #10
    Magnolia1 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Location
    USA
    Posts
    25
    Never mind everyone. Thank you for your time.
    I figured it out. My code as follows:
    Sub UpdateCompanies()
    Dim db As Database
    Dim strTableName As String
    Dim tdf As TableDef
    Dim fld As Field
    Dim strSQL As String
    Dim rst As Recordset

    Set db = CurrentDb()
    strTableName = "Companies"

    DoCmd.RunSQL "ALTER TABLE [Companies] ADD [Company ID] VARCHAR(30);"
    Set tdf = db.TableDefs(strTableName)

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [Companies] SET [Companies].[Company ID] = Trim(Left([Company Name],20)) & """" & ""101"";"
    DoCmd.SetWarnings True

    End Sub

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Or simply:

    Code:
    Sub UpdateCompanies()
    
      DoCmd.RunSQL "ALTER TABLE [Companies] ADD [Company ID] VARCHAR(30);"
    
      DoCmd.SetWarnings False
      DoCmd.RunSQL "UPDATE [Companies] SET [Companies].[Company ID] = Trim(Left([Company Name],20)) & """" & ""101"";"
      DoCmd.SetWarnings True
    
    End Sub
    unless all the code wasn't shown.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Question on creating an update query
    By Ayiramala in forum Access
    Replies: 4
    Last Post: 10-28-2015, 11:00 AM
  2. Replies: 1
    Last Post: 01-25-2013, 10:34 AM
  3. Update Query Question
    By Profector in forum Queries
    Replies: 2
    Last Post: 10-13-2010, 09:24 AM
  4. Update Query Which Asks Question
    By jhillbrown in forum Access
    Replies: 3
    Last Post: 02-15-2010, 06:36 AM
  5. update query question
    By blusk06 in forum Queries
    Replies: 3
    Last Post: 05-30-2008, 05:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums