Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Replace City Field With CountyCity Data

    This small query works fine.

    Code:
    SELECT [County] & " " & [city] AS Name
    FROM TblCARETSData;
    I need to have the results ie, (OR NB) in the City field not a field named Name.

    I'm trying to combine the County, ie. (OR) with the city ie. (NB) in the city field for the entire database of 59,390 records.

    Will an Update query accomplish this?

    I tried working on that but couldn't get it to work

    Thanks.

    Rick

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should work:
    Code:
    UPDATE TblCARETSData
        SET city = County & " " & city;
    Notice that in the relational model this is in contradiction with the atomicity principle of the first normal form. See: First normal form - Wikipedia, the free encyclopedia
    Have a nice day!

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

    SET City

    When I run this query only the CITY shows up, not the county?

    Ideas . . .

    Rick

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

    It WORKED . . .

    When I tested it it onle showed the cirt.

    Then I ran it for real and the County and city name was in the City field.

    Awesome!

    Thanks much Sinndho . . .

    Rick

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No, no, no... that is not the relationl db way
    county is nit the same as city and should not be stored in tbe db in tbe same column. Store the as separate elements of the address and DERIVE themas required. Either in a query or in your front end (form ir report)
    I'd rather be riding on the Tiger 800 or the Norton

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

    Still Not Working . . .

    I need to do this . . .

    Original Field Format:
    CENTURY 21 ZINGG & ASSOCIATES

    New Field Format:
    C-21 ZINGG & ASSOCIATES & " " & [City]

    Mine returns C-21 & " " & [City]

    I'm losing the ZINGG & ASSOCIATES which is the independent office name.

    I'm ready to commit suicide over this!

    Thanks . . . Rick

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What's the relationship with the original demand which was about counties and cities (forum rules: new question --> new thread)?
    2. What was the data transformation process that changed "CENTURY 21 ZINGG & ASSOCIATES" into "C-21 ZINGG & ASSOCIATES & " " & [City]"
    3. Which rules explain how to operate the transformation?
    4. What's the code (SQL or VBA) used to perform the data transformation?
    Quote Originally Posted by Rick Schreiber View Post
    I'm losing the ZINGG & ASSOCIATES which is the independent office name.
    5. What do you mean here (remember that we know nothing about your project, specially if you don't bother to explain anything)?
    Have a nice day!

Posting Permissions

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