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

    Unanswered: Searching specific records in a table and copy them into another table

    Hi all, I'm new here...
    I have a problem that I can not fix ...
    I have 2 tables in my access97 db
    Table 1 and Table 2, the same structure.
    I need to find all records in the Table1 that the field IDTab1=miaVar and copy them in Table2
    Someone can help me?
    Thanks in advance
    Last edited by marc80; 07-14-11 at 04:46.

  2. #2
    Join Date
    Nov 2010
    Posts
    84
    INSERT INTO Table2
    SELECT Table1.*
    FROM Table1
    WHERE (((Table1.IDTab1=[miaVar]));

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Look at "DemoTable2AppendA2000.mdb" (attachment, zip).
    Look at Tables, "Query1Append".
    Run Query1Append, and look in the Table2.
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2011
    Posts
    2
    I need to write this via code...because...in my db
    Table1 is OrderDetailsTable
    Table2 is InvoiceDetails
    now...when a order is complete I copy data from OrderTable e OrderDetailsTable into InvoiveTable and InvoiceDetailsTable.

    For OrderTable I have not problems...to copy in InvoiceTable (Fatture)

    Code:
    'Crea Fattura
    Private Sub cmdCreaFattura_Click()
        Dim OrderID As Long
        Dim InvoiceID As Long
        Dim NumFatt As Variant
        Dim DataFatt As Date
        Dim DataScad As Date
        Dim CostSped As Currency
        Dim Netto As Currency
        Dim Aliquota As Long
        Dim Imposta As Currency
        Dim Totale As Currency
        Dim Societ As Variant
        Dim Indir As Variant
        Dim Cap As Variant
        Dim Cit As Variant
        Dim Paes As Variant
        Dim Piva As Variant
        Dim TipoPag As Variant
            
        OrderID = Nz(Me![IDOrdine], 0)
        NumFatt = Nz(Me![NumeroFattura], 0)
        DataFatt = Nz(Me![DataFattura], 0)
        DataScad = Nz(Me![ScadenzaPagamento], 0)
        CostSped = Nz(Me![CostiSpedizione], 0)
        Aliquota = Nz(Me![Aliquota d'imposta], 0)
        Netto = DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & Me![IDOrdine])
        Imposta = DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & Me![IDOrdine]) * ([Aliquota d'imposta] / 100)
        Totale = DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & Me![IDOrdine]) + DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & Me![IDOrdine]) * ([Aliquota d'imposta] / 100) + CostSped
        
        'Variabili per la lettura dei dati da altre tabelle
        Dim IDClient As Long
        Dim IDTipoPag As Long
        Dim rsw As New WrapperRecordset
        'Recupero intestatario fattura dalla tabella clienti
        IDClient = Nz(Me![IDCliente], 0)
        If rsw.OpenRecordset("Clienti", "[IDCliente] = " & IDClient) Then
            With rsw.Recordset
               If Not .EOF Then
                Societ = ![Società]
                Indir = ![Indirizzo]
                Cap = ![Cap]
                Cit = ![Città]
                Paes = ![Paese]
                Piva = ![PartitaIVA]
               End If
            End With
           rsw.CloseRecordset
        End If
        'Recupero il tipo di pagamento fattura dalla tabella Tipi di pagamento
         IDTipoPag = Nz(Me![IDTipoPagamento], 0)
         If rsw.OpenRecordset("Tipi di pagamento", "[IDTipoPagamento] = " & IDTipoPag) Then
            With rsw.Recordset
               If Not .EOF Then
                TipoPag = ![Pagamento]
               End If
            End With
           rsw.CloseRecordset
         End If
            
        ' Se la fattura è già stata creata, l'operazione non verrà eseguita
        If OrdiniClienti.IsInvoiced(OrderID) Then
            If MsgBoxYesNo(OrdineGiaFatturato) Then
                VisualizzaFattura
            End If
        ElseIf ConvalidaOrdine(Ordine_Fatturato) Then
            If IsNull(Me![NumeroFattura]) Then
             MsgBoxOKOnly NecessarioImpostareNumeroFattura
             Exit Sub
            End If
            ' Crea record fattura
            If OrdiniClienti.CreateInvoice(OrderID, NumFatt, DataFatt, DataScad, CostSped, Aliquota, Netto, Imposta, Totale, InvoiceID, Societ, Indir, Cap, Cit, Paes, Piva, TipoPag) Then
                
                ' Contrassegna come fatturato l'ordine
                Me![IDStatoOrdine] = Ordine_Fatturato
                
                eh.TryToSaveRecord
                ImpostaStatoForm
                
                ' Stampa la fattura
                'OrdiniClienti.PrintInvoice OrderID
                VisualizzaFattura
            End If
        End If
    End Sub
    Code:
    Function CreateInvoice(OrderID As Long, NumFatt As Variant, DataFatt As Date, DataScad As Date, CostSped As Currency, Aliquota As Long, Netto As Currency, Imposta As Currency, Totale As Currency, InvoiceID As Long, Societ As Variant, Indir As Variant, Cap As Variant, Cit As Variant, Paes As Variant, Piva As Variant, TipoPag As Variant) As Boolean
        
        Dim rsw As New WrapperRecordset
        If rsw.OpenRecordset("Fatture") Then
            With rsw.Recordset
                If Not rsw.AddNew Then Exit Function
                ![IDOrdine] = OrderID
                ![NumeroFattura] = NumFatt
                ![DataFattura] = DataFatt
                ![ScadenzaPagamento] = DataScad
                ![CostiSpedizione] = CostSped
                ![Aliquota d'imposta] = Aliquota
                ![Netto] = DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & OrderID)
                ![Imposta] = DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & OrderID) * ([Aliquota] / 100)
                ![Totale] = DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & OrderID) + DSum("[Quantità] * [Prezzo unitario] * (1 - [Sconto])", "Dettagli sugli ordini", "[IDOrdine] = " & OrderID) * ([Aliquota] / 100) + CostSped
                ![Società] = Societ
                ![Indirizzo] = Indir
                ![Cap] = Cap
                ![Città] = Cit
                ![Paese] = Paes
                ![PartitaIVA] = Piva
                ![Pagamento] = TipoPag
                
                If rsw.Update Then
                    .Bookmark = .LastModified
                    InvoiceID = ![IDFattura]
                    CreateInvoice = True
                End If
            End With
        End If
    End Function

    Now in the same way i need to open OrderDetailsTable (Dettagli sugli ordini) and search all records with [IDOrdine] = OrderID...copy them and paste in InvoiceDetailsTable...

    I need help for writing this...
    Last edited by marc80; 07-14-11 at 05:43.

Posting Permissions

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