I'm having a hard time figuring this out, so any help would be appreciated.
I have these tables:
Inventory - Item#, description, category
Inventory_by_location - Item#, DATE (inventoried), Site, Bin, Qty
History - Item#, TranDate, qty change on that date
All tables are linked by the Item#.
Inventory_by_Location.ITEM# --> Inventory.ITEM# (left outer join)
Inventory_by_Location.ITEM# --> History.Item# (left outer join)
The history table includes all in/out transactions for each item in Inventory. Originally, I thought the Inventory_by_Location.DATE was the date any change was last made to that item but it's not. The only way to get the date of the last change is to go to the History table for the TranDate.
The report is grouped and sorted by Bin, then Category, and the Detail is then a list of all Item#s in that category. There's no record selection filter in place (though I've tried a few).
The problem I'm having is getting only the current quantity of each Item# as of the last transaction date.
I've tried suppressing the Detail for
{@Item+TranDate} < Next ({@Item+TranDate})
...which almost gets me there unless there's more than one transaction in a day whereupon it, of course, lists several records for the same item.
For some reason, I just can't quite get my brain around how to get only the current quantity and display the most recent transaction date.
Thanks!
Merle
I have these tables:
Inventory - Item#, description, category
Inventory_by_location - Item#, DATE (inventoried), Site, Bin, Qty
History - Item#, TranDate, qty change on that date
All tables are linked by the Item#.
Inventory_by_Location.ITEM# --> Inventory.ITEM# (left outer join)
Inventory_by_Location.ITEM# --> History.Item# (left outer join)
The history table includes all in/out transactions for each item in Inventory. Originally, I thought the Inventory_by_Location.DATE was the date any change was last made to that item but it's not. The only way to get the date of the last change is to go to the History table for the TranDate.
The report is grouped and sorted by Bin, then Category, and the Detail is then a list of all Item#s in that category. There's no record selection filter in place (though I've tried a few).
The problem I'm having is getting only the current quantity of each Item# as of the last transaction date.
I've tried suppressing the Detail for
{@Item+TranDate} < Next ({@Item+TranDate})
...which almost gets me there unless there's more than one transaction in a day whereupon it, of course, lists several records for the same item.
For some reason, I just can't quite get my brain around how to get only the current quantity and display the most recent transaction date.
Thanks!
Merle