make new table (Access 2000)

Topic

New Reply

I want to create a new table called MyTable1 from the table MyTable covering only the dates from 31 march 2005 to 18 april 2005
I am not quite certain how to create the new table,i know that i could use INSERT INTO in the following way:

Public Function Dummy()
Dim SQL As String
SQL = ” INSERT INTO MyTable1 SELECT * FROM MyTable WHERE (((MyTable.orderdate) Between #3/31/2005# And #4/18/2005#))”
CurrentDb.Execute SQL

End Function

However the above function does not make a new table,it only appends.What is the right way to make a new table only for the dates given?

Reply | Quote

Replies

INSERT INTO is the SQL for an append query. To create a new table, you need the SQL for a make-table query; its general syntax is

SELECT field1, field2, … INTO newtable FROM oldtable WHERE condition

So try

SQL = “SELECT * INTO MyTable1 FROM MyTable WHERE MyTable.orderdate Between #3/31/2005# And #4/18/2005#”

Reply | Quote

Thank you !!!!! It works perfect and i have made now my first table orders1 in the way you have proposed.
I came accross difficulties with the table [order details], since there is no field orderdate and naturally i will get errors.
Is there any way to extract the data for the table [order details],matching the orders for
the given interval?
The tables orders and order details are related with one to many relationship.The connecting field is orderid for both tables
So,my question is, can i make a new table [order details1],based on the table orders1 ?

The following function naturally is erroneous for the second table,since there is no field orderdate:
Public Function Dummy()
Dim SQL As String
SQL = “SELECT * INTO orders1 FROM orders WHERE orders.orderdate Between #3/31/2005# And #4/18/2005#”
CurrentDb.Execute SQL
SQL = “SELECT * INTO [order details1] FROM [order details] WHERE [order details].orderdate Between #3/31/2005# And #4/18/2005#”
CurrentDb.Execute SQL
End Function

Reply | Quote

In the second SQL join the orders table with the order details table.

Reply | Quote

To expand on patt’s repy:

SQL = "SELECT [Order Details].* INTO [Order Details1] FROM Orders " & _
"INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID " & _
"WHERE Orders.OrderDate Between #3/31/2005# And #4/18/2005#"

Reply | Quote