I needed it to show the last payment made on an invoice. But thanks for your advice.

Thank You Richard. I'm coming From VB.Net learning C#, and SQL, Didn't think about a cross Join.

I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables. The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance. I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order). I tried This:

SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance
FROM OrderPymts
JOIN Orders ON OrderPymts.OrderId = Orders.OrderId
WHERE (Balance >= 0.01)
ORDER BY Orders.OrderId, PaymentDate

To just get the last payment but it still returns all the payments. Example Result of My query: OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance| 1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 | 1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 | 1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 | Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this. Thanks for your help.

Does anyone know of a good reference, learning book that targets vb.net and .Net Core 6? Most of the books I see are for C# and .Net core 6

Hi Everyone, I hope I'm posting this in the right place. I'm using VistaDb version 6.2 I have a Main Database with several tables Like Clients, Inventory, etc. I have a Backup Database with the same table(s) and structure as the Main database. I'm trying to copy 1 or more records from the Backup Database, like the Inventory Table to the Main Database Inventory Table. The code below is inserting records from the Main database inventory table, and not from the backup database. Do I need to do this by First selecting the data and placing it in a tmp table then insert the records from the tmp table? Any help would be appreciated. Here is the code I have:

Private Sub TransferInventory()

    Dim MyFile As String = TreeListTransfer.FocusedNode(0).ToString
    Dim VdbConn1 As String = "Data Source=" & Application.StartupPath & "\\Backup\\" & MyFile & ".vdb6"
    Try
        'Count the files in the Listbox
        For i As Integer = 0 To LstFiles.Items.Count - 1
            LstFiles.SelectedIndex = i
            FileId = CInt(LstFiles.Text)

            'Add the Contents to the Database
            Using conn As New VistaDBConnection(VdbConn)
                conn.Open()
                StrSql = "INSERT INTO Inventory(ClientId, Category, Product, PartNo, PurchaseDate, Unit, UnitPrice, InStock, OnOrder, Photo) 
                      SELECT ClientId, Category, Product, PartNo, PurchaseDate, Unit, UnitPrice, InStock, OnOrder, Photo
                      FROM dbo.Inventory
                      WHERE (InventoryId = @InventoryId)"

                Using cmd As New VistaDBCommand(StrSql, conn)
                    With cmd.Parameters
                        .AddWithValue("@InventoryId", FileId)
                    End With
                    'Execute the Statement

                    cmd.ExecuteNonQuery()
                    'Close the Connection
                    conn.Close()
                End Using
            End Using

        Next
        MessageBox.Show("Transfer successfully completed on the 'Inventory' table", "Transfer Inventory Data", MessageBoxButtons.OK, MessageBoxIcon.Information)
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Transfer Inventory Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    Finally

    End Try

End Sub

Thanks in Advance.

The problem was in the OrderPymts table. I used the PaymentId to resolve the issue. Now it displays the right number of items in the Invoice.

How can I post an Image of the Invoice and Tables ?

Dim strSql As String = "SELECT DISTINCT Orders.OrderId, Orders.ClientId, OrderDate, DueDate, SubTotal, SalesTax, CityTax, LaborTax, Discount, Delivery, OrderTotal,
OrderDetails.ItemId, OrderDetails.Category, OrderDetails.Description, OrderDetails.Qnty, OrderDetails.Unit, OrderDetails.RetailPrice, OrderDetails.Total, (Select MIN (OrderPymts.PrevBal)) As PrevBal, (Select MAX(OrderPymts.PaymentDate)) As PaymentDate,
(Select MIN (OrderPymts.Payment)) As Payment, (Select MIN (OrderPymts.Balance)) As Balance, OrderPymts.Terms, (Select MAX(OrderPymts.PaymentId)) As PaymentId, Clients.Name,
Clients.Address + ' ' + Clients.City + ', ' + Clients.State + '. ' + Clients.Zip AS ClientsAddress,
'Phone : ' + Clients.Phone + ' Fax : ' + Clients.Fax + ' CellPhone : ' + Clients.CellPhone + ' Email : ' + Clients.Email As ContactInfo, Clients.Since
FROM Orders, Clients
INNER JOIN OrderDetails ON OrderDetails.OrderId = Orders.OrderId
INNER JOIN Clients ON Orders.ClientId = Clients.ClientId
INNER JOIN OrderPymts ON OrderPymts.OrderId = Orders.OrderId
WHERE (Orders.OrderId = @OrderId)"

I'm having trouble with this statement. This statement is used to call the records in the invoice. Odd since I have 2 records, One with multiple items and only 1 payment and it displays fine. The other record with multiple detail items and multiple payments displays the items twice on the invoice report and shows the first payment record instead of the latest payment record. There are 3 tables, Orders, OrderDetails, and OrderPymts. So I'm trying to query from all three tables based on the orderId. Any Help would be greatly appreciated.