Tble with only the last record

  • Thread starter Thread starter canett
  • Start date Start date
C

canett

I need to copy the tables orders and orderdetails containng only the last
order.I am copying them to another database using the formula
DoCmd.CopyObject db.Name, "orders", acTable, "orders"
DoCmd.CopyObject db.Name, "orderdetails", acTable, "orderdetails"

I want to use the criteria (SELECT Max([orderid]) FROM orders) in order to
send the tables with only the last order,but i do not know how to build the
code.Can you help me ?
 
Hi,
you have to run 2 append queries, start new query, add orders table there,
filter it by (SELECT Max([orderid]) FROM orders) , then change it type to
Append and select order as destination table. same you can do with
orderdetails. then you only need to run these queries
 
canett said:
I need to copy the tables orders and orderdetails containng only the last
order.I am copying them to another database using the formula
DoCmd.CopyObject db.Name, "orders", acTable, "orders"
DoCmd.CopyObject db.Name, "orderdetails", acTable, "orderdetails"

I want to use the criteria (SELECT Max([orderid]) FROM orders) in order to
send the tables with only the last order,but i do not know how to build the
code.Can you help me ?
Click to expand...

In addition to my question I guess that i should build a make table query
that contains only the last order.To this end i have tried to build the
following function:
Public Function Alan()
Dim SQL As String
SQL = "SELECT * INTO orders1 FROM orders WHERE orders1.orderid = DMax(orderid,
orders)"
CurrentDb.Execute SQL
End Function

However i get the error "too few parameters".Where am i wrong and i can i do
in that way ?
 
should be:
SQL = "SELECT * INTO orders1 FROM orders WHERE orders1.orderid = " &
DMax("orderid", "orders")

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


canett said:
canett said:
I need to copy the tables orders and orderdetails containng only the last
order.I am copying them to another database using the formula
DoCmd.CopyObject db.Name, "orders", acTable, "orders"
DoCmd.CopyObject db.Name, "orderdetails", acTable, "orderdetails"

I want to use the criteria (SELECT Max([orderid]) FROM orders) in order
to
send the tables with only the last order,but i do not know how to build
the
code.Can you help me ?
Click to expand...

In addition to my question I guess that i should build a make table query
that contains only the last order.To this end i have tried to build the
following function:
Public Function Alan()
Dim SQL As String
SQL = "SELECT * INTO orders1 FROM orders WHERE orders1.orderid =
DMax(orderid,
orders)"
CurrentDb.Execute SQL
End Function

However i get the error "too few parameters".Where am i wrong and i can i
do
in that way ?
Click to expand...
 
Assuming you are copying from one Access DB to another Access DB I
would recommend you use TransferDatabase instead of CopyObject. This
would allow you to copy the results of a Select Query into the
destination DB as a table.

E.g.
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Documents and
Settings\UserName\Desktop\Destination.mdb", acTable, "qryMaxOrder",
"Orders"

Why are you doing this btw? You would need to ensure that no orders
can be added while you are doing this I would imagine, otherwise the
max could change between moving the Order & then the OrderDetails.
 
Assuming you are copying from one Access DB to another Access DB I
would recommend you use TransferDatabase instead of CopyObject. This
would allow you to copy the results of a Select Query into the
destination DB as a table.

E.g.
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Documents and
Settings\UserName\Desktop\Destination.mdb", acTable, "qryMaxOrder",
"Orders"

Why are you doing this btw? You would need to ensure that no orders
can be added while you are doing this I would imagine, otherwise the
max could change between moving the Order & then the OrderDetails.
 
Back
Top