make table (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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]));

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Punctuation, that’s the word I was looking for. I must be getting old, wadda ya mean, I am old.

Reply | Quote

Hah! Ye’re nuthin but a whippersnapper, kid.

Reply | Quote

Kid, that’s the nicest thing I’ve been called in a long, long, long time.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote