Solved Adding a Qty Completed Calculation for Sales Report (2 Viewers)

[slharman1]

I have a DB that has an OrderDetails table with Qty, Description, Yes/No Complete and Price fields along with some other fields. Currently I have a form showing an order list and when clicking on one of the orders, the subform will populate with all of the order details records. I can check each line item using the yes/no CheckBox in order to mark the order detail record completed so a sales report can total up all of the order detail records completed for a specific period of time, daily, weekly, monthly or any dates selected in a from/to parameter form.

I have created a related table called tblOrderDetailsQTYComp with the following fields: tblOrderDetailsQTYCompID, QtyComp, DateComp, and OrderDetailID

Using the QTY field to keep track of the total on the order and by extension the total amount available for completion, I would like to be able to select a QtyCompleted TextBox and put in the Qty completed for the moment at that time and leave the balance of Qty remaining on my form.

That way when running the sales report I can show the values for the Qty completed during the specified report period to list the amount of value realized at that period of time.

Any help would be appreciated as I am having a hard time wrapping my head around it.
Thanks

 

[The_Doc_Man]

I will simply suggest that what you need is a query that counts the number of records with the Complete Y/N field equal to "Y" - and then filtered or grouped according to the Order Number. Given that you seem to want totals for some of the other fields, you could probably do a SELECT that contained either SUM( Qty ) or COUNT( * ). You want to look up Summation queries to see what they look like.