Aging reports

[mswarner]

Does anyone know how to do aging reports?&nbsp;&nbsp;I want to do an aging of inventory availability.&nbsp;&nbsp;I've got all the manufacturing orders to make the stock and when they're due as well as a bunch of sales orders against the stock.&nbsp;&nbsp;I want to create an aging report that shows when the stock will go negative so I know when to move orders up.<br><br>I can set my tables up any way that's needed.&nbsp;&nbsp;I'm drawing the sales orders from one system and the mfg orders from another and I need to put them together into something that's understandable.<br><br>Any suggestions are much appreciated.

 

[advpay]

Start by using select queries getting the data you want...then you can add on criteria for what groups of past due periods you want 1-3 months 4-6 months, whatever.<br><br>You mean like this? I want to do an aging of inventory availability....&nbsp;&nbsp;<br><br>SELECT DISTINCTROW Inventory.[Item Number], Inventory.Category, Inventory.[Sub Category], Inventory.[Supplier Number], Inventory.Description, Inventory.Manufacturer, Inventory.Model, Inventory.[Last Inventory Date], Inventory.[Quantity in Stock], Inventory.Cost, Inventory.Retail, Inventory.[Special Features], Inventory.[Item Picture], Inventory.[Reorder Point], Inventory.[Reorder Quantity], Inventory.[Quantity on Order], Inventory.[Last Order Date], Inventory.[Amount Ordered], Inventory.[Expected Delivery]<br>FROM Inventory<br>WHERE (((Inventory.[Quantity in Stock])&gt;=0));<br><br>and this???.&nbsp;&nbsp;I want to create an aging report that shows when the stock will go negative so I know when to move orders up.<br><br>SELECT DISTINCTROW Inventory.[Item Number], Inventory.Category, Inventory.[Sub Category], Inventory.[Supplier Number], Inventory.Description, Inventory.Manufacturer, Inventory.Model, Inventory.[Last Inventory Date], Inventory.[Quantity in Stock], Inventory.Cost, Inventory.Retail, Inventory.[Special Features], Inventory.[Item Picture], Inventory.[Reorder Point], Inventory.[Reorder Quantity], Inventory.[Quantity on Order], Inventory.[Last Order Date], Inventory.[Amount Ordered], Inventory.[Expected Delivery]<br>FROM Inventory<br>WHERE (((Inventory.[Quantity in Stock])&lt;=0));<br>

 

[mswarner]

This is what I want to do:
[tt] Trans [tab] Trans [tab] Order [tab] [tab] Change in [tab]
Date [tab] Type [tab] Number [tab] Loc [tab] Balance [tab] Balance
8/14/00 [tab] FROM [tab] [tab] T8C [tab] -36000 [tab] 46600
8/14/00 [tab] R FIN [tab] 824015 [tab] FLOR [tab] 72000 [tab] 82600
8/11/00 [tab] I FIN [tab] 590749 [tab] FLOR [tab] -116000 [tab] 10600
8/11/00 [tab] I FIN [tab] 590716 [tab] FLOR [tab] -120000 [tab] 126600
8/11/00 [tab] I FIN [tab] 590599 [tab] FLOR [tab] 400 [tab] 246600
8/11/00 [tab] R FIN [tab] 824015 [tab] FLOR [tab] 28000 [tab] 246200
8/11/00 [tab] TO [tab] [tab] FLOR [tab] 72000 [tab] 218200

[/tt]
See how the last column is a running total? How do you do that?

 

[advpay]

Where does change in balance come from...what fields what tables...list the tables that you are using with the field names...please.

 

[MLHab306]

Hi

I had the same question. The top solution is what I ended up using with each date range in a different subform, on top of 1 main form. The bottom solution came from Ginger.

Check out the thread181-163638. Creating an aging report.

Mark
*************
Hi

After going thru a lot of posts I found a solution.
it is:

Between Date() And (Date()-30) (in first query)
Between Date()-31 And (Date()-60) (in second query) etc
then on my report I used subforms. Not exactly the way I wanted but it is a good start.

Thanks for your suggestions.



***********

GingerR (MIS) Nov 17, 2001
in addition (instead of subreports) in one single query you can always put this to generate a STATUS, then sort a report based on the STATUS:

STATUS: iif(DueDate between Date()-1 and Date()-15,&quot;1-15 Days&quot;,iif(DueDate between Date()-16 and Date()-30,&quot;16-30 Days&quot;,iif(DueDate between Date()-31 and Date()-60,&quot;31-60 Days&quot;,&quot;Over 60 Days&quot))

then in your report you just base it on this query alone, and in SortingAndGrouping group by STATUS and make a header for it.