Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Unanswered: Help with Queries

    i i have been trying to work out how to merge 2 tabels of data together using a query.

    e.g

    table1

    houseno.address.noofcars.noofpeope

    2.bramall lane.unknown.unknown.
    2.mearsbrooke .unknown.unknown.
    3.bramall lane.unknown.unknown.
    4.bramall lane.unknown.unknown.

    Table2

    houseno.address.noofcars.noofpeople
    2.bramall lane.1.3.
    2.mearsbrooke .4.7.
    3.bramall lane.1.1.
    4.bramall lane.1.2.

    What i need to happen is that the system sees that the data is unknown (know how to o that)

    Then searches Table 2 for the corrisponding houseno and address, then put the noofcars and noofpeople into table1

    Is this possible everything i have read makes it look possible but how actually to do it is a bit confusing

    Any help will be more than apprechated

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    What is the PRIMARY KEY in your tables ??

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    id go with a bit of SQL...

    Code:
    update table1
    set table1.NoOfCars=table2.NoOfCars, table1.NoOfpeople=table2.NoOfpeople
    where
    ( table1.houseno=table2.houseno and table1.address=table2.address)
    and
    (table1.noofcars="unknown" or table1.noofpeoplec="unknown")
    note you will have to match noth the house number and street to be sure you get a match.


    you may need to extend the query to make sure you are using a common capitalistion appraoc.. ie the where clause may not match Bramhall Lane with bramhall lane

    so have a look at the UCASE function

Posting Permissions

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