I have the following query in Access. Works fine. The only issue that I'm having is that it's not returning the fields in the same order as they are in the select statement. Example is the A.ADJ_IN_SAME_PROD_CD_FLAG
SELECT A.ORD_DOC_NBR, A.ORD_TYPE_CODE, A.ORD_LOAD_DT, A.ORD_APPV_DELIV_DT, A.ACCTG_DEST_NBR, A.ACCTG_DEST_LOC_NM, A.PROCS_LOC, A.CUST_RT_NBR, A.DMD_AGGREGATE_IND, C.DEMD_AGG_DESCP,
A.SHIP_RECV_USEID, A.SHIP_RECV_DT, A.SHIP_RECV_TIME, A.SHIP_ADJ_ACK_DT, A.SHIP_ADJ_ACK_TIME, A.SHIP_ADJ_ACK_CODE, A.ADJ_IN_SAME_PROD_CD_FLAG
FROM (((((TBL_ADJUSTMENT_DETAIL
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Try this:
Create a new query, and go to SQL view. Paste the above and run. You will get the columns in the exact order of the SELECT clause.
Access queries are complex beasts, trying hard to pass for form datasheets. They have now properties such as columns widths, column order (try to move columns around), hidden columns and frozen columns (from the Format menu). All these properties will mess with your original plain SQL.
BTW, all this gets saved if you answer yes to "Save layout?" or if you just click save, thinking you save a plain SQL query.
Cheers!
(°v°)
@wsh2:
This is not corruption. Try it for yourself on a query or a table: open in datasheet, click once to select a column and then drag the column to a new location. When you close, answer [Yes] to: "save layout?" and the new column order is stored, regardless of the field order in the table definition or the select clause.
You can delete these properties (ColumnOrder, ColumnWidth, ColumnHidden) using DAO if you need to.
(°v°)
Business Accounts
Answer for Membership
by: Raynard7Posted on 2007-06-21 at 16:23:32ID: 19337822
How are you running this query?
If you just execute the sql - in a query window, are you saying that the columns are not in the order you gave? ie the 4th column below is in the 5th?
This should not happen - and your syntax looks ok (your left joins will slow everything though)
What version of access are you using?
How do you know the columns are in the wrong order?
Below is how I have re-formatted it, so If I understand correctly what you are saying is that the 17th column A.ADJ_IN_SAME_PROD_CD_FLAG
SELECT A.ORD_DOC_NBR,
A.ORD_TYPE_CODE,
A.ORD_LOAD_DT,
A.ORD_APPV_DELIV_DT,
A.ACCTG_DEST_NBR,
A.ACCTG_DEST_LOC_NM,
A.PROCS_LOC,
A.CUST_RT_NBR,
A.DMD_AGGREGATE_IND,
C.DEMD_AGG_DESCP,
A.SHIP_RECV_USEID,
A.SHIP_RECV_DT,
A.SHIP_RECV_TIME,
A.SHIP_ADJ_ACK_DT,
A.SHIP_ADJ_ACK_TIME,
A.SHIP_ADJ_ACK_CODE,
A.ADJ_IN_SAME_PROD_CD_FLAG
A.LONG_PROD_CD,
A.ORD_CURR_ITEM_QTY,
A.ORD_ORIG_ITEM_QTY,
A.SHIP_RECV_ITEM_QTY
FROM (((((TBL_ADJUSTMENT_DETAIL
LEFT JOIN TBL_PIC_DATA AS B
ON (A.ORD_DOC_NBR = B.ORDRID)
AND (A.LONG_PROD_CD = B.PRODID))
LEFT JOIN TBL_DEMAND_AGG_CD AS C
ON A.DMD_AGGREGATE_IND = C.DEMD_AGG_CD)
LEFT JOIN TBL_IIM_SKU_DATA AS D
ON A.LONG_PROD_CD = D.LONG_PROD_CODE)
LEFT JOIN TBL_PIC_SKU_DATA AS E
ON E.PROD_CD = A.LONG_PROD_CD)
LEFT JOIN TBL_MOD_MAPPING AS G
ON A.LONG_PROD_CD = G.PRODID)
LEFT JOIN TBL_FULL_CASE_PICKER AS H
ON A.ORD_DOC_NBR = H.ORDRID;