Results 1 to 14 of 14
  1. #1
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8

    [Syntax help] Query returns more results than expected

    Hello everyone!



    I need some help. I'm new to access, and i don't know the syntax to write the formulas i need.
    Here is what i need help with:
    I have 2 tables. For convenience i will name them A table and B table.
    So, the tables should look like this A(x,y) = {A11, A12, A13, A21, A22, A23, A31, A32, A33} x would represent the rows and y the columns (y would be the field in the database).
    Symmetrically, the B table should look like this: B(a,b) = {B11, B12, B13, B21, B22, B23, B31, B32, B33}. a would represent the rows and b the columns( b would be the field in the database)

    Table A has all its fields filled, Table B has fields b=1 and b=3 filled, and field b=2 is empty.
    I need field b=2 (B(a,2)) to take the values of field y=2 (A(x,2))
    I wrote an update query in design mode and, instead of iterating 3 results, it iterates 9 results, exactly these values (in this order):

    A(x1,2)
    A(x2,2)
    A(x3,2)
    A(x1,2)
    A(x2,2)
    A(x3,2)
    A(x1,2)
    A(x2,2)
    A(x3,2)

    Can anyone please explain to me how to do this correctly?
    Any help would be appreciated.

    Thank you in advance!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    there is no row number in a table, you need an ID to identify each row.

    for example, add an extra column ID to both table A and B, and fill the ID with 1,2,3 in both table. then create the update query:
    update A inner join B on A.ID=B.id set B.b=A.y

  3. #3
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8
    Hello!

    Thanks for your answer.
    I've used that expression and i am getting this error:

    JOIN expression not supported

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    do you have the field name y in table A, and field name b in table B?

  5. #5
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8
    I've managed to enter the data i need in the second table.

    I left the colum in the second table empty and i used the condition B.b Is Null

    The only problem is that the data is not entered in the empty fields of B.b, but it creates new rows and enters the data in those new rows...

  6. #6
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8
    Quote Originally Posted by weekend00 View Post
    do you have the field name y in table A, and field name b in table B?
    Table A is named products and table B is named product_images
    I need to update product_images.imageprodid to products.prodid

    I mention that product_images.imageprodid is empty (this is why i used "Is Null" in the formula above)

  7. #7
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    can you post you query here, and relative fields of you table? it's hard to discuss on air.

  8. #8
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8
    This is the only query that generates any result. I must mention that i am using the design mode to build queries because i am not familiarised with sql:

    UPDATE [Generare ID-uri produse] LEFT JOIN buffer_isc_products ON [Generare ID-uri produse].productid = buffer_isc_products.imageprodid SET buffer_isc_products.imageprodid = [Generare ID-uri produse]![productid]
    WHERE (([buffer_isc_products]![imageprodid] Is Null));

  9. #9
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    there are a few problems in your update query:

    1 you should use INNER join instead of LEFT join;
    2 you should not join on buffer_isc_products.imageprodid because has all null.
    3 it's very rare to update the join field.

    So, all you need is an ID field which can identify each row and can match in two tables.

  10. #10
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8
    Can you please tell me more about that ID field please?
    Are you reffering to the primary key of the table? Or a field with AutoNumber type?
    I have checked the table and they have ID fields, but the id's of the fields i need to copy values to/from are different. The id of the source row is not the same as the id of the destination row...

    Any further help would be appreciated. Thank you!

  11. #11
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    ID is the field that can link two tables together to identify which row in table B match which row in table B

    e.g:
    table A:
    ID a1 a2 a3 a4
    r1 2 3 4 6
    r2 4 5 7 8
    table B:
    ID b2 b3 b4
    r2 4 x 5
    r4 3 x 2
    r5 1 x 1

    query:
    update A inner join B on a.id=b.id set b.b3=a.a3
    after above query is run, table b will be updated to:
    table B:
    ID b2 b3 b4
    r2 4 7 5
    r4 3 x 2
    r5 1 x 1

    because r2 in table B match r2 in table A, therefore B3 on this row in B was updated to A3 on this row in A

    the b3 of the rows in table B with ID = r4 or r5 were not updated because there is no match in table A

    in other word, if there is no ID field in both table, you want Access to update the B.b3 in row 1,2 3, to A.a3 in row 1,2 3. but as I mentioned before, there is no row number in table, so Access doesn't know which row of A3 to put in which row of B3.

  12. #12
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8
    Thanks for the info. I'm starting to understand how Access is working. It needs a row ID to know where to find info on a table.

    This rises another question to me:
    My source table has row ID's starting from 1 and ending with the last record which is 180, but the rows in the second table that i need to update start from ID 800+, and the destination id constantly changes depending on the value of the the source id...
    I don't know how to explain this better.. i hope you understand it.
    I'm starting to belive that it should be better for me to look for someone who knows his way with databases and show him in person what i need to do

    Thank you for your help!

  13. #13
    pitox is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Posts
    8
    Hello!

    I did it. Thank you for your help! I changed the colums on which i compared the ID's so that i can compare fields that both have values in them. I was comparing an empty field to a field which contained values...


    Thanks again for your help

  14. #14
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    glad that you worked it out finally.

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

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  2. Query returns duplicates
    By RobRay in forum Queries
    Replies: 3
    Last Post: 10-26-2010, 01:38 PM
  3. Replies: 3
    Last Post: 07-21-2010, 02:41 AM
  4. Syntax Error while calling sub: Expected: =
    By cowboy in forum Programming
    Replies: 3
    Last Post: 07-12-2010, 02:21 PM
  5. Replies: 1
    Last Post: 07-10-2010, 09:56 PM

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