Results 1 to 3 of 3
  1. #1
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74

    Add records to a new table for each column of a table

    Good morning everyone. I need your evaluate support to get the following using a VBA function if possible:



    I have the following table (Table1):

    MaintItem M1 M2 M3
    12345678 10 2
    12345679 2 10
    11223344 2 2 2








    and i would like to add the records in the following Table2:

    MaintItem Freq HRS
    12345678 M1 10
    12345678 M2 2
    12345678 M3
    12345679 M1 2
    12345679 M2
    12345679 M3 10
    11223344 M1 2
    11223344
    11223344
    M2
    M3
    2
    2

















    For each frequency (M1,M2,M3) of the MaintItem in Table1 i would like to have a record in the Table2 with the indication of the HRS.
    Hope it is clear.

    Thanks in advance for any suggestion.
    LM

  2. #2
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Just to inform that i solved the issue looking again in this group messages where i find the following solution which suite perfect for me:

    Dim db As DAO.Database, recIn As DAO.Recordset, recOut As DAO.Recordset
    Set db = CurrentDb()
    Set recIn = db.OpenRecordset("q_sel_MI_FREQ_HRS", dbOpenDynaset, dbReadOnly)
    Set recOut = db.OpenRecordset("MI_HRS_EACH_FREQ", dbOpenDynaset, dbEditAdd)


    recIn.MoveFirst
    Do Until recIn.EOF
    For i = 1 To recIn.Fields.Count - 1
    If Left(recIn.Fields(i).Name, 2) = "M1" Or Left(recIn.Fields(i).Name, 2) = "M2" Or Left(recIn.Fields(i).Name, 2) = "M3" Or Left(recIn.Fields(i).Name, 2) = "M6" Or Left(recIn.Fields(i).Name, 1) = "Y" Then
    recOut.AddNew
    recOut.Fields("MI") = recIn.Fields("Maintenance Item")
    recOut.Fields("FREQ") = recIn.Fields(i).Name
    recOut.Fields("MAN_HRS") = recIn.Fields(i).Value
    recOut.Update
    End If
    Next i
    recIn.MoveNext
    Loop

    recIn.Close
    recOut.Close
    db.Close

    Thank you.
    LM

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Would you actually want records where there were no hours?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 7
    Last Post: 01-09-2018, 12:16 PM
  2. Filter records and update table column.
    By Thompyt in forum Queries
    Replies: 5
    Last Post: 11-07-2016, 01:34 PM
  3. Replies: 1
    Last Post: 02-19-2016, 08:16 AM
  4. Replies: 4
    Last Post: 09-04-2013, 06:40 AM
  5. Replies: 3
    Last Post: 11-24-2010, 06:33 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