Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    make table (Access 2000)

    I refer to the wonderful solution proposed by Hans from the present Forum, about making table with criteria.His code for making the table orders is very efficient and i will repeat it:
    StrOrders = "SELECT orderid, customerid, orderdate, [required date]
    INTO Temp " & _
    "FROM orders WHERE orderid = " & Me.orderid

    There is a new problem however with making a temporary TempProducts which depends on the table orders.My tables orders,order details and products are related so:
    the table orders and the table order details in one to many
    the table product with the table order details one to many
    the table order details is the connecting table between orders and products .

    What i want is to obtain the list of products contained only in the order i have created and , and the number of cartons and the quantity against each product.The fields for the cartons and the quantities are to be found in the table orderdetails, so it is rather complicated.


    SELECT orders.orderid, [order details].ProductID, [order details].cartons, [order details].Quantity INTO TempProducts
    FROM orders INNER JOIN ([order details] INNER JOIN products ON [order details].ProductID = products.Productid) ON orders.orderid = [order details].OrderID
    WHERE (((orders.orderid)=[orderid]));

    The above code fails and says that orderid may refer to more than one field.
    In SHort, what i aim is to make a table only for these products that are contained in the order i have issued.I cannot do it and i will be grateful for any help

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: make table (Access 2000)

    I will take a quick stab at this.....
    The problem may be with the Where Clause, specifically =[orderid]. Which table does [orderid] come from? You are telling the query where table orders.orderid = orderid, but sql does not know where to the the orderid from.

    I would suppose you would want = order details.orderid

    WHERE (((orders.orderid)=[orderid]));
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make table (Access 2000)

    YOur problem is the last [orderid] in your WHERE clause. In the SQL itself, you need an actual value there, which is what you would be creating with the expression populating strOrders.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (Access 2000)

    Thank you very much for your kind attention! Please help me further please.It is so important to me.
    I have followd your advices as far as i can.I receive now the message " Join expression not supported"
    Here is the code that i have now, after i have made the order, in the OnClick event of the form:

    Dim StrOrders As String
    Dim StrOrderDetails As String


    StrOrders = "SELECT orders.orderid, orders.customerid, orders.orderdate, orders.[required date], orders.paymentid, orders.PaymentMethodID, orders.bankid,

    orders.invoicedate, orders.AuftragNr " & _
    " INTO Temp FROM orders WHERE orderid = " & Me.OrderID


    StrOrderDetails = " SELECT[order details].ProductID.[order details].cartons,[orderdetails].quantity " & _
    " INTO [TempOrderDetails] FROM [order details] INNER JOIN orders ON [order details].OrderID = [order details].orderid WHERE orderid = " & Me.OrderID

    CurrentDb.Execute StrOrders
    CurrentDb.Execute StrOrderDetails

    The execution of StrOrders is perfect.I receive a temporary table called Temp containing only the order i have issued and it does the job.
    But the execution of StrOrderDetails fails.Then i receive the message "Join expression not supported".
    I will be so grateful as always for the help.

    P.S. In order to help me please note that the ProductID comes from the related table Products, where the ProductId is conected with the table
    Order Details in one to many relationships. Therefore the productid is availabe in the subform and not in the mainform.These are only mine considerations but

    since i am not a proffessional programmer i am surely on the wrong path.All i want is to have a temporry table containing all the details for a given order,as the

    number of the product, cartons and quantity, the same as i have already a table containig only this given order.I cannot explain myself why in the first case i

    receive the table i want but not in the second case.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (Access 2000)

    Further to my question of today i have expreimented again and have tried to revise the code as follows
    StrOrderDetails = " SELECT[order details].ProductID.[order details].cartons,[orderdetails].quantity " & _
    " INTO [TempOrderDetails] FROM [order details] INNER JOIN orders ON [order details].OrderID = [orders].orderid WHERE [orders].orderid = " & Me.OrderID

    Now i receive the error "Too few parameters.Expected2.Why is it so?
    Please help

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

    Re: make table (Access 2000)

    Try replacing the period between ProductID and [Order details] with a comma, or can't I read it properly.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make table (Access 2000)

    See Pat's reply. Is suspect your punctuation is at fault.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make table (Access 2000)

    Try this:
    Where orderid = " & " ' " & Me.orderid & " ' "
    P.S. leave out the spaces around the quote, I did it for readability only!!

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

    Re: make table (Access 2000)

    Punctuation, that's the word I was looking for. I must be getting old, wadda ya mean, I am old. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make table (Access 2000)

    Hah! Ye're nuthin but a whippersnapper, kid. <img src=/S/granny.gif border=0 alt=granny width=20 height=20>
    Charlotte

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

    Re: make table (Access 2000)

    Kid, that's the nicest thing I've been called in a long, long, long time. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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