Access Query Help?

Question

I have a table that contains sales order info. A sales order could have multiple records based on the different items ordered. Some items in a sales order could get delivered if available while other items in the same sales order could not be delivered. For those line items delivered in a sales order, there is a status column that shows "C" (for closed) and for those line items undelivered shows nothing. Ex:

Sales Order Item Status
908111 Cable C
908111 Transreceiver C
908111 Coaxial Cable
908112 Cable C
908112 Transreceiver C
908112 Coaxial Cable C

As you see above, sales order 908111 is not 100% delivered since the third line has no status but 908112 is 100% delivered. I've been trying to take the entire 908111 order and place it in a different table that shows partially delivered but haven't had luck. Can anyone help?

Answer 1

I would suggest using 2 tables, one for delivered items and one for outstanding items. The method to programatically move the existing records will be done by writing 2 queries. In the first Query you will specify all records, with a filter for "C" on the status column. For the other one you will filter for "Null". Do a make table for each one and then go and create your relationships from There.

Answer 2

Based on the first paragraph, you should have:
1- Item table (item id, names, extra info)
2- Sales order table (sale order id, date, extra info)
3- Items/Sale order table (sale order id, item id, delivery status)
4- Delivery Status (status id, status name)

This is the best structure to start with. If you have trouble with SQL, this means your structure might be redesigned.
I hope this helps

Answer 3

you could assign incomplete orders with an 'I' and then create a query that searches for all the records with 'I' (criteria:: status=I) then generate a report using that query.. this might work.. you could use a pull down menu allowing user to choose from i or c for status.. hope that works!