Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: i am stuck on this one

    I have two tables . the first one will have two feilds size and lenght.
    the size will have data like 0204 the legth will have data like 07200
    ,09600 ext . what i need to do is take the size 0204 the merge it together with the width so in the second table will now have a feild with 020407200 for one record then 020409600 and so on .. so to break it down one record in the size feild need to mearge with many records in the lenght feilds in the second table
    i have attached a test database will how it should look like in the end i hope

    thanks for any help any one can give
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2011
    Posts
    23
    Wouldn't it be easier to merge it in a query?

    [Field1]&[Field2]

    This response isn't based on the attached example as we don't have download privileges in the office

  3. #3
    Join Date
    Nov 2011
    Posts
    2

    thats not really what i am doing

    that not what i am really doing . not sure if you looked at the small database i made as a test .

    lets say feild one has one record 0204
    now lets say feild two has many records 07200,09600 ext
    i want the single record in feild one to automatical merge with thr many record in feils two so it would look like 020407200 , 020409600

    mabe you can do that in a simple quarie but i tried and all i get is the records in the same record sets to merge

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by cslr11 View Post
    I have two tables . the first one will have two feilds size and lenght.
    the size will have data like 0204 the legth will have data like 07200
    ,09600 ext .
    1. In your sample database there is one table "size" with 3 columns: "key", "size" and "width". There is no column named "length" and the contents of the "width" column (04, 05, 06, 07, 08, 9) does not look like what you describe (data like 07200, 09600).

    2. Is the second table "plc code" an (incomplete) sample of what you expect as result?

    Quote Originally Posted by cslr11 View Post
    what i need to do is take the size 0204 the merge it together with the width so in the second table will now have a feild with 020407200 for one record then 020409600 and so on .. so to break it down one record in the size feild need to mearge with many records in the lenght feilds in the second table
    3. Do you actually mean that you want to concatenate each "size" value to each "width" value in the resulting table "plc code"? (That's what I infer from your explanations, but I'm not sure).

    4. Why do you want to store what seems to be a computed value (if I'm right) in another table?

    5. You should refrain from using the same name for a table and one of its column. You should also refrain from using spaces and other non alphanumeric characters in the names of the objects in your database.

    6. If I correctly understand what you want to achieve, it can be done with a query performing a cartesian product of the table "size" on itself:
    Code:
    SELECT [size].[size] & [size_1].[width] AS plc_code
    FROM [size], [size] AS size_1 
    ORDER BY [size].[size] & [size_1].[width]
    If you absolutely need to store the computed values into a table (which is considered as a bad practice) you can use an INSERT INTO or SELECT INTO statement:
    Code:
    INSERT INTO plccode ( [plc code] )
    SELECT [size].[size] & [size_1].[width] AS plc_code
    FROM [size], [size] AS size_1
    ORDER BY [size].[size] & [size_1].[width];
    or (this will create the table "plc_code" and fill it in one instruction):
    Code:
    SELECT [size].[size] & [size_1].[width] AS plc_code
    INTO plc_code
    FROM [size], [size] AS size_1
    ORDER BY [size].[size] & [size_1].[width];
    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
  •