SQL not working

[Djblois]

I am trying to switch my dlookup over to SQL in my queries in order to speed it up. Here is the SQL that I have entered:

SELECT Orders_Table.ord_ID, Orders_Table.Customer, Orders_Table.Storer, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO
FROM Orders_Table, WMS_PKO_HD
ORDER BY Orders_Table.ord_ID, Orders_Table.Customer
WHERE (([WMS_PKO_HD].REF) =[Forms]![frmOrders]![cobOrderNum]));

It keeps giving me a syntax error on the Where statement.

 

[Brianwarnock]

I think that the Where comes before the Order by

Brian

 

[Djblois]

Thank you that fixed the error but now my query comes up blank. Here is what I have:

SELECT Orders_Table.ord_ID, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO
FROM Orders_Table, WMS_PKO_HD
WHERE (([WMS_PKO_HD].REF) =[Forms]![frmOrders]![cobOrderNum])
ORDER BY Orders_Table.ord_ID;

The SQL looks wrong to me logically but I do not know how to fix it. What I want it to do is pull up the order# from the Orders_Table then pull the Ship to that corresponds to that order from the WMS_PKO_HD table. I had it working with a d lookup but in order to speed it up I want to put it in my Query. here is the Dlookup that I had working if it would help:

=DLookUp("[SHIPTO]","WMS_PKO_HD","[REF]=" & Chr(34) & [cobOrderNum] & Chr(34))

 

[DCrake]

You do not have a join between the two tables according to your sql

SELECT Orders_Table.ord_ID, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO
FROM Orders_Table, WMS_PKO_HD
WHERE (([WMS_PKO_HD].REF) =[Forms]![frmOrders]![cobOrderNum])
ORDER BY Orders_Table.ord_ID;

BTW this is a continuation from an earlier post SQL Not Working

Unless this is a totally different query then continue form earlier post.

David

 

[Djblois]

I tried an Inner Join and I am having the same issue. Here is my SQL and a pic of the joined tables.

SELECT Orders_Table.ord_ID, Orders_Table.Customer, Orders_Table.Storer, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO
FROM Orders_Table INNER JOIN WMS_PKO_HD ON Orders_Table.[Order#] = WMS_PKO_HD.REF
WHERE (((WMS_PKO_HD.REF)=[Forms]![frmOrders]![cobOrderNum]))
ORDER BY Orders_Table.ord_ID, Orders_Table.Customer;

 

[Brianwarnock]

Without seeing the whole story I would guess that you should join REF to order# and the where should lookk for order#=cobordernum

Brian

Edit I see you have made the join, But I think you need to change the Where.

 

[Djblois]

Brian don't I already have ref joined to Order#? That is what it looks like to me. Also, I tried what you said and it is a little better. now the combobox shows on the form but it still does not show any of the orders saved to the appointment. Here is the SQL statement now:

SELECT Orders_Table.ord_ID, Orders_Table.Customer, Orders_Table.Storer, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO
FROM Orders_Table INNER JOIN WMS_PKO_HD ON Orders_Table.[Order#] = WMS_PKO_HD.REF
WHERE (((Orders_Table.[Order#])=[Forms]![frmOrders]![cobOrderNum]))
ORDER BY Orders_Table.ord_ID, Orders_Table.Customer;

and here is a pic of my form what it is doing.

 

[Brianwarnock]

What is the result of just executing the query, ie look at the query result not the form bound to it.

Maybe we are going to need to see the DB

Brian

 

[Djblois]

I got it brian. With the Join I do not need the where statement.

 

[Brianwarnock]

OK thanks for the feedback, but I thought the Where was to filter down to 1 ordernumber, selected on a form, I guessed we were maybe on the wrong tack which is why I wondered about seeing the DB.
Anyway glad its all ok now.

Brian

 

[Djblois]

Now I have another issue. It is reading the data for any orders previously inputted but it won't allow my users to add new orders to appointments. I have the combobox set to look up all the order numbers we have in our main Oracle Database. (They should not be able to edit this data) I set it with this row source:

SELECT [WMS_PKO_HD].[REF] FROM [WMS_PKO_HD] ORDER BY [REF];

then I set the control source to save that order # that they chose to a table (Orders_Table) that I created in Access and the field is Order# . This is how they attach orders to appointments in my program.

Here is the SQL for the query that the form is attached to:

SELECT Orders_Table.ord_ID, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO, WMS_PKO_HD.STORER, tblStatus.Status
FROM (Orders_Table INNER JOIN WMS_PKO_HD ON Orders_Table.[Order#] = WMS_PKO_HD.REF) INNER JOIN tblStatus ON WMS_PKO_HD.FLAG = tblStatus.Code
ORDER BY Orders_Table.ord_ID;

when a user choses one of the order #'s it doesn't even fill the other controls with the information. I think this reason is because it isn't saving to my table.

 

[Djblois]

can somebody please help me? I ran out of ideas what can be wrong? it will not let my users enter new orders to the appointment. Look at the post right above this one for a better explanation. If anyone needs I can send the front end but I will not be able to send the back end because I am attaching to Oracle now for part of it.

 

[Djblois]

Let me repost the whole situation into one post. Here is the issue:

I have created a query that will query from an access table that I created and our main Oracle tables. When I first created it I had my users input all the orders and the customers and storers attached to the orders into my system. However, since they are already inputting that information into our Main Oracle database. I thought it would be more efficient to pull the customer name and storer name from Oracle. Less redundancy. All they are supposed to do now is input the order number that is attached to the appointment and it is supposed to read the customer and storer from the oracle table. It is supposed to save the order number from the oracle table and put it in my table so it would be attached to the appointment. I think this is where I am having the problem.

However, It is reading the data for any orders previously inputted but it won't allow my users to attach any new orders to appointments. It will allow them to type it in but once they exit the orders box the whole line dissapears therefore not saving it. I have the combobox set to look up all the order numbers we have in our main Oracle Database. (I even trried to change it to a textbox) (Also, they should not be able to change the numbers in the oracle table) I set it with this row source:

SELECT [WMS_PKO_HD].[REF] FROM [WMS_PKO_HD] ORDER BY [REF];

then I set the control source to save that order # that they chose to a table (Orders_Table) that I created in Access and the field is Order# . This is how they attach orders to appointments in my program.

Here is the SQL for the query that the form is attached to:

SELECT Orders_Table.ord_ID, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO, WMS_PKO_HD.STORER, tblStatus.Status
FROM (Orders_Table INNER JOIN WMS_PKO_HD ON Orders_Table.[Order#] = WMS_PKO_HD.REF) INNER JOIN tblStatus ON WMS_PKO_HD.FLAG = tblStatus.Code
ORDER BY Orders_Table.ord_ID;

when a user choses one of the order #'s it doesn't even fill the other controls with the information.

 

[EraserveAP]

I do not know anything useful about Oracle nor how you have it set up in Access, so pardon me if I ask what may be a stupid question..
Can you manually input a new record?

 

[Djblois]

If I go directly into the query I can add a new Order. Just the Form will not let me add them.

 

[gemma-the-husky]

not trying to be silly, but cant you do all this stuff with stored queries in the query design window -

a) you wont get syntax errors,
b) far easier to develop
c) and a stored query will work quicker than a SQL statement - might be the same, but cant be slower.

 

[Djblois]

gemma-the-husky,

I dont want to seem ungrateful, I do appreciate your help but that makes almost no sense because when you create a stored query it creates the SQL statement for you. Maybe I am wrong and If I am please tell me how I am wrong. but this is what I see

1) so they will run the same speed
2) there are certain things you can do with SQL statements that you can not do with the query design window.
3) I did not know how to do a join initially with the design window but I have that part working now anyway. I did figure out how to do that throught the design window also.

 

[gemma-the-husky]

i agree that a stored query is just sql anyway, and you can see the sql anyway by going to view, sql window

the only queries you cant create directly in the query pane are union queries.

and then i generally just

select * from storedquery1
union select * from storedquery2

----------
right -

the reason a stored query is more efficient is that when it runs the first time, access creates a query plan, to decide the most efficient way to ask the server for info to resolve the query. if you run the sql each time, it cant use the saved query plan, and has to recompute it each time.

thats why a stored query cant be slower, but can be quicker.

eg if you have select fields from from tableA , fields from tableB , join tablea tableb on some field, where something or other - access can fetch the reocrds it needs in a variety of orders, some of which are quicker than others

loads of people here know all this in much more detail than me!

------------
the other thing is speed - i find it far easier to design queries visually

the only time i use sql is if i have to do something by iterating a recordset, because what i need is based on a non updateable query - and even then sometimes i can redesign it to make it updateable.

-------------
all the things you can do in SQL (distinct, orderby, top etc) are all avaiable in query design - just right click the design area, and select properties

its just far easier (for me) to design a complex query by basing it on a series of simpler queries, rather than trying to get very very complex sql statements with correct syntax You can examine the output etc etc, as you go to be sure everything is going right.

---------
in fact i never even write sql as the query source in comboboxes etc - i just use reusable stored queries - i just find it faster for me, and i know what i need to maintain if i change things.