Hi,

Here is me with my problems again. its been 48 houres without sleep and without solution. So i hope someone can help me in here like allways.
ok, this is bothering me:
I have two tables, [orderTable] and [closeTable]
I polulate [orderTable] by clicking on add button. that works fine.
now i want another button that will copy the data from [orderTable] to [closeTable], but if the item already egzist in [closeTable], i need to inner, left, or right join or append or what ever works (in docmd.runsql) with multiple criteria, I need to check if it is the same item and waiter, and only update the Quantity.
ill post some of non working code, so you have a better idea of what im trying to do


Code:
Private Sub porosit_Click()
DoCmd.SetWarnings False
---ignore this part----
If (DCount("Qty", "[orderTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "'") = 0) Then
Me.labelmsg.Caption = "Nuk ka asgje per tu shtypur"
Me.labelmsg.Visible = True
Else
If DCount("Qty", "[orderTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "' and [Type] = 'Pizza'") > 0 Then
DoCmd.OpenReport "KitchenRpt", acViewNormal, , , acWindowNormal
Else
End If
If DCount("Qty", "[orderTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "' and [Type] = 'Drink'") > 0 Then
DoCmd.OpenReport "BarRpt", acViewNormal, , , acWindowNormal
End If
---- from here is where I cnat figure it out------
 
If (DCount("Qty", "[closeTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "'") = 0) Then
DoCmd.RunSQL "INSERT INTO closeTables select * FROM orderTables"
 
'DoCmd.RunSQL "UPDATE closeTables AS O RIGHT JOIN orderTables AS N ON O.Item = N.Item AND O.Table = N.Table AND O.Waiter = N.Waiter O.Item = N.Item, O.Table = N.Table, O.Waiter = N.Waiter, O.[Qty] = (N.[Qty] + O.[Qty])"
'DoCmd.RunSQL "UPDATE orderTables LEFT JOIN closeTables ON orderTables.ID = closeTables.ID SET closeTables.Qty = [closeTables].[Qty] + [orderTables].[Qty] SET"
'DoCmd.RunSQL "UPDATE orderTables INNER JOIN closeTables ON orderTables.Item = closeTables.Item SET closeTables.Qty = [closeTables].[Qty] + [orderTables].[Qty]"
'DoCmd.RunSQL "UPDATE orderTables LEFT JOIN closeTables ON orderTables.Item = closeTables.Item SET closeTables.Qty = [closeTables].[Qty] + [orderTables].[Qty] SET"
Else
'DoCmd.RunSQL "UPDATE orderTables INNER JOIN closeTables ON orderTables.Item = closeTables.Item and orderTables.Table = closeTables.Table and orderTables.Waiter = closeTables.Waiter SET closeTables.Qty = [closeTables].[Qty] + [orderTables].[Qty]"
'DoCmd.RunSQL "UPDATE orderTables INNER JOIN closeTables ON orderTables.Item = closeTables.Item SET closeTables.Qty = [closeTables].[Qty] + [orderTables].[Qty]"
'DoCmd.RunSQL "UPDATE orderTables inner JOIN closeTables ON ((orderTables.Item = closeTables.Item) AND (orderTables.Table = closeTables.Table) AND (orderTables.Waiter = closeTables.Waiter)) SET orderTables.[Qty] = [closeTables].[Qty] + orderTables.[Qty]"
'If DCount("Qty", "[closeTables]", "[Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "'") = 0 Then
'DoCmd.RunSQL "INSERT INTO closeTables select * FROM orderTables"
'Else
'DoCmd.RunSQL "UPDATE orderTables LEFT JOIN closeTables ON orderTables.Item = closeTables.Item and orderTables.Table = closeTables.Table SET closeTables.Qty = [closeTables].[Qty] + [orderTables].[Qty]"
DoCmd.RunSQL "UPDATE closeTables FROM orderTables [ LEFT | RIGHT ] JOIN closeTables ON orderTables.Qty = closeTables.Qty + orderTables.Qty"
 
End If
End If
DoCmd.RunSQL "DELETE  * FROM orderTables where [Table] = " & Me.tableNameLbl.Caption & " and [Waiter] = '" & Me.usertxts.Caption & "'"
End Sub
I really appreciate it, and thank you for your time