Hello all! I have spent the better part of the night battling an inventory issue. I am building a Variance Reporting System for a beverage distributor. The items in the system are stored in both bottle and case quantity. I can convert the items to case or bottle using the bottles per case information from the database. Here is an example:

TOTAL COUNTED CS CALCULATION BY ITEM:
[cs count]+([btl count]/[btl per cs])=[cs count calc]

TOTAL ON HAND CS CALCULATION BY ITEM:
[cs oh]+([btl oh]/[btl per cs])=[cs oh calc]

TOTAL CS VARIANCE BY ITEM:
[cs count calc]-[cs oh calc]=[cs calc var]

Once I have calculated the variance by item, I need to provide a report to the warehouse staff to perform a second count. This report must show the following:

CS VAR:______ BTL VAR:______

Once I have the CS CALC VAR (24.17 for example), I cannot get a bottle count for the report. (.17 bottles would convert to 2 bottles in a 12 bottle case).

Sorry for the lengthy description, but it is late/early and I have been denting the wall with my head for a few hours over this one. Any help would be greatly appreciated.

Thanks in advance,
Cameron

Surely it's a simple task, multiply CS CALC VAR by 12? Am I missing something?

I didn't explain that very well. Sorry about that. The problem is as follows:
The staff counts cases and bottles. The count may be 23 cases and 14 bottles of a particular item id. The conversion formula that I have takes cases+(btls/btls per cs)=cs count calc. In the example above, the btls per case are 12, so 23 cases and 14 bottles = 24.17 cases. We need to send the staff out for a second count after the initial to check the variances. If our computer shows 24.00 cases, we are over .17 cases. I need the formula to break the 24.17 back into 24 cases and 2 bottles. I know it should seem very simple, but everytime I attempt to write the formula, I run straight into problems.

Hopefully I have clarified the problem a bit.

Int() will return the integer part of the number, so if you use
Int([cs count calc])
this will give you the number of cases and
([cs count calc]-Int([cs count calc])) * number in case
will give you the number of bottles.