How do I add records to a table using data from a query and a form?

[gneiss]

Hello! I am working on a database that keeps track of the issue and return of tools. I currently have an issue table and a return table (as well as some other tables...).&nbsp;&nbsp;We (the user and I) want to make the return segment of the process a no-brainer for the user. <br><br>The user enters return information on a form (part id, return date, quantity returned). A query is then submitted&nbsp;&nbsp;that finds the outstanding issues for the indicated tool (there will&nbsp;&nbsp;usually be more than one issue).&nbsp;&nbsp;Right now the information from the query is displayed on a subform.<br><br>I need to take the information from the form (quantity returned, date returned) and the results of the query and use this information to add records to the return table. <br><br>The new query will have to go first to the oldest record with outstanding issues and copy the issue id. The return date will come from the form. The quantity entered on the form will be compared to the quantity issued. If the quantity entered on the form is less than or equal to the quantity issued then the quantity returned will equal the quantity entered on the form, and the process will stop there (i.e, one record will be added to the return table).<br><br>&nbsp;If the quantity entered on the form is greater than the quantity issued then the quantity returned will equal the quantity issued. The quantity returned from the first issue will have to be subtracted from the quantity entered on the form.&nbsp;&nbsp;The result of this calculation equals the quantity remaining (that needs to be returned).<br><br>The query will then go to the second oldest record and proceed as above.&nbsp;&nbsp;This process will continue until the quantity remaining equals zero.<br>&nbsp;<br>Any ideas on what path I might take?&nbsp;&nbsp;Thanks. <br><br>

 

[DougP]

First I would just have one table that keeps track of the items. The table would have an On_Hand_qty which they are added to or taken away from. Then you don't have to find the oldest. Make second table if you want History.<br><br>And then use barcode's so thy don't have to key anything in. Just scan the item and a badge and and have a sheet with Qty's on it. We are doing this very thing and it works fantastic it's fast and Barcodes are never wrong.<br><br>BTW Barcode Wands are $199 and barcode fonts are $49.00<br>Which I'm sure is peanuts campared to your items you are tracking or you would'nt be tracking them in the first place. Can't stick a barcode on a something then put it in a book. Many retail stores do that already.<br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[gneiss]

Thank you for your reply. We need to find the oldest record for billing purposes. We are billed for the number of days the tools are checked out (several hundred tools might be issued at the same time but they are not always returned together).&nbsp;&nbsp;Bar coding is currently not an option. Any other ideas? I appreciate your help.