Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2013
    Posts
    17

    Unanswered: INSERT INTO Or UPDATE Multivalued field

    I want to to copy data from one table to another. I have read about MVF, I know that isn't good solution, can't migrate data between others software and many other problems. But in my situation it's really helpful. Also I have read about work with MVF and all what I know, that there should be a LOOP to move data. I found information that isn't possible to INSERT INTO with MVF, but I found on the Internet example works with INSERT INTO. In my e.g. I need to copy and paste data, where order = me.order. It's to difficult case for me, so please guys help Please see attachment
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    MVF... WTF?
    if you are moving rows from one table to another, consider using an append query

    an update query would only be good if the row already existed in the destination table

    an insert query qould only be good if the row didn't already exist in the destination table

    if your deisng is so flaky that you have to do an insert AND an update then runt he append query first
    then run an update query (but you will have to have some mechanism to limit the rows the update query processes otherwise all rows will be updated and make this query a performance hog over time

    when using SQL there should be very few occasions when a loop algolrhytm makes any sense or is of any use.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ive had a look at your attachment and it isn't clear (or at least isnt clear to me) what the point of attaching it is
    there are no queries
    there is no sample data in your destination table

    MVF is a coliumn that isn't intrinsincally meaningful

    what sample data there isn't at first glance looks like garbage

    I doubt many people will be prepared to waste, sorry spend time tryign to work out what your problem actually is


    http://www.dbforums.com/microsoft-ac...seriously.html
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I second Healdem. I tried to understand what you mean, examined your database, but to no avail.
    Have a nice day!

  5. #5
    Join Date
    Apr 2013
    Posts
    17
    Quote Originally Posted by healdem View Post
    ...WTF?
    Quote Originally Posted by Sinndho View Post
    I second Healdem. I tried to understand what you mean, examined your database, but to no avail.
    Right guys, sorry for that. Forget about last example, Now I created a new simple example. All what I need, is copy data, create new row and paste data in the same table.

    Please see attachment
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Private Sub Polecenie2_Click()
    
        ' Change the RowSource propertry of the combobox Kombi0 to:
        ' SELECT tblCourses.Id_Course, tblCourses.Title, tblCourses.Instructors FROM tblCourses ORDER BY tblCourses.Id_Course;
        '
        Const c_QSL As String = "INSERT INTO tblCourses ( Title, Instructors ) VALUES ( '@T', '@I' );"
        
        Dim strSQL As String
        
        strSQL = Replace(Replace(c_sql, "@T", Me.Kombi0.Column(1)), "@I", Me.Kombi0.Column(2))
        CurrentDb.Execute strSQL, dbFailOnError
        
    '    MsgBox "Now in the table 'tblCourses' should be create a new line, with data in columns 'Title' and 'Instructors'"
    
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I still don't see how you expect to cvopy rows from one table to another

    however I will make an observation on your table design
    ... in my view you need
    1) remove the 'instructors' column in tblCourses
    2) create a new table called, say CourseInstructors. this will be an intersection table which associates an one or more instructors with one or more courses. google intersection tables
    ...whenever you see a column with similar values separated by a comma or other punctuation its nearly always a sign of flaky tables design

    extending the model further
    well a course is something that can be studied. Usually an academic institution wioll have multiple instances of a course. so you'd need a table for CourseDates (Ie when a sepcific course is being run
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2013
    Posts
    17
    Thank you for the insight guys.

    Quote Originally Posted by Sinndho View Post
    Here's a solution:
    Code:
    Private Sub Polecenie2_Click()
    
        Const c_QSL...
        
        strSQL = Replace(Replace(c_sql...
    After correct this, code still doesn't work. Ms Access shows the error "You can't use INSERT INTO with multivalued field" - and that's the problem what I was talking about:/

    Quote Originally Posted by healdem View Post
    ...
    however I will make an observation on your table design
    ...


    I contrived this example. Actually I need this solution to other situation, but language of printing is a purely technical, so I should describe every field what it mean. I doubt that You want to read all of this description, so I thought to contrive simple example. I know that database of this example should be designed a little different, but trust me, in my real problem I need to know how to copy and paste data from multivalued field.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem comes from your using the so called "multivalue fields". This is NOT SQL compliant, this is NOT compliant with the normal forms that define how a relational database should work (A Simple Guide to Five Normal Forms in Relational Database Theory) and probably is one of the worst idea Microsoft ever had. Follow Healdem's advice and revert back to using a junction table.

    Other than that, you could use a Recordset to perform the insertion (probably a DAO.Recordset2 object in this case) See: Recordsets for Beginners - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com and Microsoft Access tips: VBA Traps: Working with Recordsets.
    Have a nice day!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You can acheive what you want. But its a crap design. If you must persist with this crap design then:-
    You need to combo or listboxes, one for unallocated teachers, one fir allocated teachers.
    you need a function that populates the list boxes based on the values in the column, and a function that populates the column based on the selected values in the list box

    populate the list box from the oncurrent event

    populate the column from the forms before update event

    effectively what you are creating is a new grouped control. In the past I have used 4 buttons to transfer a row from one to the other list box. Uts a bit like the workgroup process in pre A2007.

    and no im not going to provide code, if you want to use this approach you will need to do the grunt work. The reason... its crappy design, I dont like promoting such bad design
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2013
    Posts
    17
    Ok, thx for trying to help me. The solution is DAO.Recordset

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    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
  •