inner, left or right join ?

[toqilula]

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

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

 

[Red17]

You might run some code something like this when you click the button:

Private Sub porosit_Click()

Dim db as database
Dim rst as recordset
Dim rstUpdate as recordset
Dim intItemNumber as Long
Dim strWaiterName as String
Dim strQry as String
Dim intQty as Long

Set db = CurrentDB

Set rst = db.OpenRecordset("orderTables", dbOpenDynaset)

While (not rst.eof) and (not rst.bof)

intItemNumber = rst.Fields("Item").Value
strWaitername = rst.Fields("Waiter").Value
intQty = rst.Fields("Quantity").Value

strQry = "SELECT * FROM closeTables " & _
"WHERE Item = " & intItem & " and waiter = '" & _
strWaiter & "';"

Set rstNew = OpenRecordset(strQry, dbOpenDynaset)

If Not rstNew.eof Then
rstNew.Edit
rstNew.Fields("Quantity") = intQty
rstNew.Update
Else
rstNew.AddNew
rstNew.Fields("Item") = intItem

'This is where you can update each field from the orderTables table for the current record into the closeTables table

rstNew.Update
Endif

rstNew.Close
rstNew = Nothing

rst.MoveNext

Wend

rst.close
db.close

rst = Nothing
db = Nothing

End Sub

 

[toqilula]

Hi Red17,

thank you for your email, i have tried, copy and passed the code you provided. It didnt work
and it looked complicated for me....