if last record isnull then previous record

[modglin]

I have a report that has several tables joined
The first table supplies a location, date, product
the second table provides # units @ end of day - (only has
this info if any were received or sold)
the third table provides # unit sold
the fourth provides # new units put into stock
The report has a group for each store then each product.

I am trying to show a monthly total for November for each product:
Product, BeginningUnits, RcdUnits, SoldUnits, EndingUnits
A 3 5 6 2
B 5 0 4 1
C 2 0 2 0

The Monthly Sold and Recd are no problem - my problem is getting the BeginningUnits if they did not sell or purchase one of the products on the last day of the previous month. I originally thought I could use a formula something like
if isnull{ONHAND} then previousrecord, however, if the product has not been sold or received since the 15th the last date that has information is the 15th and the detail isnull for the 16, 17, 18 and etc.
I am certain that I need to do a whileprintingrecords and use a loop, but I am not sure how to write this.
the detail information would look something like this
Date Prod Sales Purch EndofDay
10/31 A 0 2 10
11/1 A 1 9
11/2 A
11/3 A 5 14
11/4 A 7 7
11/5 A
11/6 A
etc . . .
Total A 8 7 (This is my problem)

 

[synapsevampire]

How is ONHAND determined?

Why would you sometimes show a zero, but blank in other areas? Note 10/31 Sales and 11/2 Sales???

A good way to convey requirements is to show sample data and expected output. You seemingly did this until you get to a new column called onhand which wasn't previously referenced.

Is it derived by taking the previous daysendofday and subtracting the shipped, and adding the new units?

I would suggest placing an onhand column on the first table which would be updated by using triggers on the other tables. Having an end of day might be useful for some things, but it's clumsy for getting current quantities.

To address your concern, you might use a group on the PART field and then the date.

Now in the group header of the part use a formula to maintain the last known on hand qty:
whileprintingrecords;
numbervar Qty:=0; //reset it

In the date group header update it:
whileprintingrecords;
numbervar Qty;
if not(isnull({second.endofdayqty})) then
Qty:={second.endofdayqty}; //redundant

Now use the following in whatever calculations you need in the details, such as the following to adjust the qty for the trnsactions:

whileprintingrecords;
numbervar Qty;
Qty:=(Qty-{third.unitsold})+(fourth.newunits};

In the group footer for date you can display the end of date quantities using:
whileprintingrecords;
numbervar Qty

As for the ending units for the month, use the same theory but add in another variable to the end of month formula which sums the QTY at the date group footer level, as in:

whileprintingrecords;
numbervar Qty;
Numbervar PartQty:=Qty

Since PartQty won't get reset, it will know the last value at the part group footer.

Anyway, this should get you closer. If not, try posting example data.

The last thing you should do is take a cherry pit extractor to your dba's lips and use the punch outs in a slingshot to chase them out the door.

That way the rest of us will know 'em on sight

-k

 

[modglin]

While I was writing up how & why I was using the tables mentioned above, I realized that I did not need the All_Store_Dates. The ON_HAND_BY_DATE table has information anytime there is a sale, invoice or adjustment so I dropped that table which resolved the previous ending qty problem. Now I have the problem of getting the last amount from the previous month to show in the current month starting qty.
Hope the following gives the detail to help figure out what I need to do.
ON_HAND_BY_DATE(Table only has information if there was a sale, order recd, manual adjustment. This table has the avg cost of the item figured by the actual program when there is a change in qty – based on first in first out and etc)
DATE
STORE
PART
QTY_ON_HAND_END_OF_DAY
AVG_COST_END_OF_DAY

ORDER_DETAIL(Table has all invoices for all products for the stores)
DATE_RECEIVED
DELIVERY_STORE
PART
RETAIL_VALUE
QTY_RECD
UNIT_COST

SALES_DETAIL(Table has all sales made by each store for each product)
DATE_SOLD
STORE
PART
QTY_SOLD

ADJUSTMENTS
AJUSTMENT_DATE
STORE
PART
QTY

PRODUCTS(Table that has all products with categories and descriptions)
PART
UOM (unit of measure)
DESCRIPTION

I currently have the ON_HAND_BY_DATE as the left outerjoin. It links the STORE, PART and DATE fields to the other tables. (except the product table and then it is only the PART fields). I am using the ON_HAND_BY_DATE as the main table since the other tables do not list all dates and only linked if they shared a date.
The report is needed each month
The report that I am creating needs to reflect the following fields
Store (ON_HAND_BY_DATE)
Part(ON_HAND_BY_DATE)
Description(PRODUCTS)
Beginning Qty (ON_HAND_BY_DATE.QTY_ON_HAND_END_OF_DAY – this needs to come from the table rather than a formula – should be the qty from the previous month. The date may not be the last day of the month. If they did not have activity since the 15th - the 15th will be the last time the part will show ending qty)
Purchases(ORDER_DETAIL.QTY_RECD)(using a formula that says if field isnull then 0 else qty purchased)
Sales(SALES_DETAIL.QTY_SOLD)(using formula that says if field isnull then 0 else qty sold)
Adjustments(ADJUSTMENTS.QTY)(using formula that if field isnull then 0 else qty adjusted)
Ending Qty(ON_HAND_BY_DATE.QTY_ON_HAND_END_OF_DAY – this needs to be the ending qty for the current month. Again the ending qty may not show on the last day of the month, it will be the last day that there was activity for this product)
Beginning Cost – Formula taking cost from On_Hand_End_Of_Day and multiplying by the Beginning Qty.
Retail - formula - Sales Qty_Sold * Retail_Value
There are more formulas that are taking cost or retail * various qty amounts but this is the jist of what I need.







Currently the detail information is looking like this

Date Store Part Adj_Qty Purch_Qty Sales_Qty On_Hand AVG_Cost RetailValue
9/28 040 A 0 0 2 10 1.19 1.50
10/1 040 A 0 0 4 6 1.19 1.50
10/2 040 A 0 10 0 16 1.19 1.50
10/7 040 A 0 0 3 13 1.19 1.50
10/28 040 A -1 0 0 12 1.19 1.50

9/20 040 B 0 0 1 16 1.19 1.50
10/1 040 B 0 5 0 19 1.19 1.50
10/1 040 B 0 0 2 19 1.19 1.50
10/2 040 B 0 0 10 9 1.19 1.50
10/28 040 B 0 0 3 6 1.19 1.50
10/31 040 B 0 25 0 31 1.19 1.50
(The Table with the on hand for 10/1 product B shows the amount of 19 at the end of the day on both lines)



The report would need to show
Month Store Part Beg_Qty Adj_Qty Purch_Qty Sales_Qty End_Qty Cost Retail
10 040 A 12 -1 10 9 12 14.28 13.50
10 040 B 17 0 30 16 31 20.23 24.00
I am having problems figuring out how to get the Beg Qty to appear in the report. If I add a formula PREVIOUS({QTY_ON_HAND}) it give the 12 and 17 on the detail line, but I am not able to get it to show in the Group Field. I have a group on date, month, part, store.
Hopefully I covered all the details this time!