Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    make new table (Access 2000)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make new table (Access 2000)

    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#"

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make new table (Access 2000)

    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

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: make new table (Access 2000)

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make new table (Access 2000)

    To expand on patt's repy:
    <code>
    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#"</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •