By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

append question

P: n/a
I have copied a function that appends from table orders2 into table
orders1 the row that has the value SubOrder = True in the table
orders2. This function finds the highest ordered in the table orders2.
But I want to find the highest ordered in the table orders2 instead.
In short, I want to append the order from the table orders2 into the
table orders1 and this order to get the next highest ordered.Somehow I
canot manage it.Can you help me :
Below is the working function that gets the highest ordered in the
tabnle ordersd2:
Can you help me amend it with my new requirement?My own function is a
mess so i do not show it i am showing only the working function that i
have copied.

'target table : orders1
' source table : orders2

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngMaxID As Long

Set dbs = CurrentDb

' Get highest OrderID
strSQL = "SELECT Max(Orders2.OrderID) AS MaxID " & _
"FROM Orders2 LEFT JOIN Orders1 " & _
"ON Orders2.OrderID = Orders1.OrderID " & _
"WHERE Orders1.OrderID Is Null AND Orders2.SubOrder=True"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If IsNull(rst!MaxID) Then
MsgBox "No records to append.", vbInformation
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function

End If

lngMaxID = rst!MaxID
rst.Close

' Insert orders
strSQL = "INSERT INTO Orders1 " & _
"SELECT * FROM Orders2 " & _
"WHERE Orders2.OrderID = " & lngMaxID
dbs.Execute strSQL

Jan 12 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"solar" <sp*******@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
I have copied a function that appends from table orders2 into table
orders1 the row that has the value SubOrder = True in the table
orders2. This function finds the highest ordered in the table orders2.
But I want to find> canot manage it.Can you help me :
Below is the working function that gets the highest ordered in the
tabnle ordersd2:
Can you help me amend it with my new requirement?My own function is a
mess so i do not show it i am showing only the working function that i
have copied.

'target table : orders1
' source table : orders2

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngMaxID As Long

Set dbs = CurrentDb

' Get highest OrderID
strSQL = "SELECT Max(Orders2.OrderID) AS MaxID " & _
"FROM Orders2 LEFT JOIN Orders1 " & _
"ON Orders2.OrderID = Orders1.OrderID " & _
"WHERE Orders1.OrderID Is Null AND Orders2.SubOrder=True"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If IsNull(rst!MaxID) Then
MsgBox "No records to append.", vbInformation
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function

End If

lngMaxID = rst!MaxID
rst.Close

' Insert orders
strSQL = "INSERT INTO Orders1 " & _
"SELECT * FROM Orders2 " & _
"WHERE Orders2.OrderID = " & lngMaxID
dbs.Execute strSQL


Perhaps forget about the function for a minute and concentrate on the
correct sql - you can do it all in one statement. However, with Orders1,
Orders2, Order By, etc I cannot quite see what you are doing.

The sql below, finds the most recent order in Orders2 which does not occur
in Orders1 and which has SubOrder=True, and appends this to Orders1. The
"most recent order" means sort by date and then by ID if more than one
occurs on the same date. You could obviously change this to give you the
highest value order or whatever you want - it is this part I didn't
understand.

Anyway, here is the single sql statement which should do the lot:

INSERT INTO Orders1
SELECT TOP 1 Orders2.*
FROM Orders2 LEFT JOIN Orders1
ON Orders2.OrderID=Orders1.OrderID
WHERE Orders1.OrderID Is Null
AND Orders2.SubOrder=True
ORDER BY Orders2.OrderDate DESC,
Orders2.OrderID DESC
Jan 12 '06 #2

P: n/a
I believe you misspoke in your statement. You are currently getting the
highest order number in Table2 that does not exist in Table1. I assume
you're wanting to add to Table1 any order in Table2 that doesn't exist in
Table1. If that's the case, you don't need the Max statement. Instead, just
let the first query return all records from Table2 that don't match Table1.
Use that query as the source for the Append query and you will then add all
records from Table2 to Table1 that don't currently exist in Table1. This
will be ALL records that don't currently exist in Table1, regardless of how
high their OrderID value is.

However, if you only want to add records with order numbers greater than the
highest order number in Table1, you can do this by using the following query
in lieu of your first query then use this new query as the source of the
Append query.

strSQL = "SELECT Orders2.* " & _
"FROM Orders2 " & _
"WHERE Orders2.OrderID > DMax(""OrderID"", ""Table1"");"
--
Wayne Morgan
MS Access MVP
"solar" <sp*******@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
I have copied a function that appends from table orders2 into table
orders1 the row that has the value SubOrder = True in the table
orders2. This function finds the highest ordered in the table orders2.
But I want to find the highest ordered in the table orders2 instead.
In short, I want to append the order from the table orders2 into the
table orders1 and this order to get the next highest ordered.Somehow I
canot manage it.Can you help me :
Below is the working function that gets the highest ordered in the
tabnle ordersd2:
Can you help me amend it with my new requirement?My own function is a
mess so i do not show it i am showing only the working function that i
have copied.

'target table : orders1
' source table : orders2

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngMaxID As Long

Set dbs = CurrentDb

' Get highest OrderID
strSQL = "SELECT Max(Orders2.OrderID) AS MaxID " & _
"FROM Orders2 LEFT JOIN Orders1 " & _
"ON Orders2.OrderID = Orders1.OrderID " & _
"WHERE Orders1.OrderID Is Null AND Orders2.SubOrder=True"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If IsNull(rst!MaxID) Then
MsgBox "No records to append.", vbInformation
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function

End If

lngMaxID = rst!MaxID
rst.Close

' Insert orders
strSQL = "INSERT INTO Orders1 " & _
"SELECT * FROM Orders2 " & _
"WHERE Orders2.OrderID = " & lngMaxID
dbs.Execute strSQL

Jan 12 '06 #3

P: n/a
Thank you very much for the 2 replies i have received.I see that both
replies might have helped me if i was more clear of what i intend to
do.I will be extremely grateful for your help, i am stuck with this
problem.So,in both tables, orders2 and orders1, there is a YesNo field
called Suborder.Whenever there is an order in the table orders2, with a
field Suborder = Yes,i want to append this order into
the table orders1 and give it the next highest number.For example:In
table order2 there is an order number 7443 having a field Suborder =
Yes.In the table ordrrs1 the last order number is 7.
I want to append the order 7443 into the table orders2 and give it the
next highest order number which will be 8 in my case.I am showing a
part of my function that does the oppposite, gets the highest number in
the orders2.
Could you help me rewrite the function?

Jan 13 '06 #4

P: n/a
Thank you very much for the 2 replies i have received.I see that both
replies might have helped me if i was more clear of what i intend to
do.I will be extremely grateful for your help, i am stuck with this
problem.So,in both tables, orders2 and orders1, there is a YesNo field
called Suborder.Whenever there is an order in the table orders2, with a
field Suborder = Yes,i want to append this order into
the table orders1 and give it the next highest number.For example:In
table order2 there is an order number 7443 having a field Suborder =
Yes.In the table ordrrs1 the last order number is 7.
I want to append the order 7443 into the table orders2 and give it the
next highest order number which will be 8 in my case.I am showing a
part of my function that does the oppposite, gets the highest number in
the orders2.
Could you help me rewrite the function?

Jan 13 '06 #5

P: n/a
After the line

lngMaxID = rst!MaxID

Add the following does what you want:

lngOrders1NewID= DMax("[OrderID]", "[Orders1]") + 1

Then in your SQL Append Query, you'll need to specify the fields instead of
using the * so that you can insert the new ID value.

"INSERT INTO Orders1 " & _
"SELECT " & lngOrders1NewID & " As NewID, Field2, Field3, Field4 " & _
"WHERE Orders2.OrderID = " & lngMaxID & ";"

You would concatenate in the ID field in the order (left to right) that it
is in the Orders1 table. List the other fields from Orders2 as needed to get
the rest of the information.

--
Wayne Morgan
MS Access MVP
"solar" <sp*******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Thank you very much for the 2 replies i have received.I see that both
replies might have helped me if i was more clear of what i intend to
do.I will be extremely grateful for your help, i am stuck with this
problem.So,in both tables, orders2 and orders1, there is a YesNo field
called Suborder.Whenever there is an order in the table orders2, with a
field Suborder = Yes,i want to append this order into
the table orders1 and give it the next highest number.For example:In
table order2 there is an order number 7443 having a field Suborder =
Yes.In the table ordrrs1 the last order number is 7.
I want to append the order 7443 into the table orders2 and give it the
next highest order number which will be 8 in my case.I am showing a
part of my function that does the oppposite, gets the highest number in
the orders2.
Could you help me rewrite the function?

Jan 14 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.