I've got a transactions tables. With data that look like this
id order_number date, qty, uom, item_number
'1', '1', '2013-05-16', '1', 'bottle', '4'
'2', '1', '2013-05-16', '1', 'can', '5'
'3', '2', '2013-05-20', '-2', 'oz', '4'
'4', '2', '2013-05-20', '-6', 'oz', '5'
'5', '3', '2013-05-20', '1', 'can', '7'
I want the report to display inventory, I have it grouped by item_number
in the detail section I have the transactions
in the footer I have a running total for qty.
I want the transactions to convert to all the same uom(The item_number 's) UOM stored in another table.
how do I access the data in the detail section before it's displayed so that I can do a lookup to see if it's in the correct uom and convert it and display it in the correct qty before it displays?(I would not store the conversion) I'm assuming this would be done via VBA but i really don't know....