Form design to show items on an order

[snipe]

Hey guys,

I have a query, pictured below, that shows the items on a few certain orders. I need to create a form that shows all of the items for each del_doc_num (order number). When I go from record to record, I want to to cycle through orders. I have attached a quick sketch I how I want it in case I am not being clear enough. I am a super rookie with form design of this nature. The wizard did not help much with this. I'm on Access 2003 btw.

[snipe]

Also, no idea how I managed to double attach a picture and have one of them be upside down?

[June7]

Did you try editing post to eliminate the extra image?

Set form in Continuous view and arrange the controls as you want. However, if you use wizard to create form, it 'groups' controls. Select all controls, right click > Layout > Remove.

I am not sure you have optimal data structure. Consider:

tblStores
StoreID (PK)
StoreName
StoreAddress
StorePhone

tblOrders
Del_Doc_ID (PK)
StoreID (FK)
SO_WR_DT

tblOrderDetails
Del_Doc_Num (FK)
Itm_Cd
etc.

[orange]

Suggest you post the SQL of your query.
And a jpg of your Tables and relationships window.

[snipe]

I did try editing the post, but it shows the image as an attached image, and I couldn't see a delete/remove option. I dunno. EDIT: "Go Advanced," Fixed.

SQL for the query:

SELECT T_MattContest_MattsBoxes.SO_STORE_CD, T_MattContest_MattsBoxes.SO_WR_DT, T_MattContest_Inspection.DEL_DOC_NUM, T_MattContest_MattsBoxes.[DEL_DOC_LN#], T_MattContest_MattsBoxes.ITM_CD, T_MattContest_MattsBoxes.QTY, T_MattContest_MattsBoxes.UNIT_PRC, T_MattContest_Inspection.BoxPctOfSpiff, T_MattContest_MattsBoxes.SplitPctOfSpiff, T_MattContest_MattsBoxes.Vendor, T_MattContest_MattsBoxes.[100Payout], T_MattContest_MattsBoxes.[75Payout], INV_ITM.DES
FROM (T_MattContest_Inspection INNER JOIN T_MattContest_MattsBoxes ON T_MattContest_Inspection.DEL_DOC_NUM = T_MattContest_MattsBoxes.DEL_DOC_NUM) INNER JOIN INV_ITM ON T_MattContest_MattsBoxes.ITM_CD = INV_ITM.ITM_CD
ORDER BY T_MattContest_MattsBoxes.SO_STORE_CD, T_MattContest_Inspection.DEL_DOC_NUM, T_MattContest_MattsBoxes.[DEL_DOC_LN#];

This form isn't technically going to be for scrolling through orders. Our company does mattress sales contests every few months. The bonus pay for selling a mattress depends on the quality of the mattress and if there was a box spring sold with the mattress. 99% of the sales have their bonuses calculated by a program I wrote. The 1% are the ones that "confuse" the program. A customer buys a king mattress and a queen box, etc. This form will pull up all of the items on that ticket so the user can determine the correct bonus pay. The form needs to be able to write back to a master table (T_MattContest_MattsBoxes) that has every ticket with a mattress on it.and edit the "payout" field. All of this data is pulled from ODBC.

The data structure you suggested exists in the ODBC already. I am having to use ~20 queries to get down to just the data that I want, and have the calculated fields that I need.

[snipe]

Attachment 15228 relationships pic

[snipe]

Went a different, much more simple route that just came to me. Sorry to waste your time guys.