Current quantity for last transaction date? 1

[hallmg]

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

 

[Madawc]

Create a summary total for date, using 'maximum', which will be the last date.

Create a running total. Use it to 'sum' the quantity, but only when the date matches the maximum.

The use of Crystal's automated totals is outlined at FAQ767-6524.

Alternatively, group by item and sort the group by date. Show the quantity in the group footer.




Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Do you want quantity per the most recent transaction date per Item within each category? You should insert a group #3 on Item then and go to report->selection formula->GROUP and enter:

{table.transdate} = maximum({table.transdate},{table.item#})

-LB

 

[hallmg]

Thanks, both of you! I went with lbass' solution, realizing I had actually tried grouping on Item# at some point, but couldn't come up with the right Group formula.

It works great except for when there are two transactions on the same date, whereupon it displays both records for that date instead of just one. (I assume it would be the same where there were more than two as well, but I don't think there are any in the data.)

Anything I can think of to suppress the detail when there's more than one removes both records instead of just one of them.

I appreciate your responses!

Merle

 

[hallmg]

Duh. Never mind. I put the detail fields in the Item# group header and suppressed the detail.

Thanks for your help!

Merle