SageForum for UK versions of Sage 50- Help and support.

I want to produce a report that shows:
Stock item No..... Qty in stock
PO No...Due in date
Sales Order....Due out date
Sales Order...Due out date
PO No....Due in.
Po No..... Due in
etc,
Free Stock after all activities

showing the expected due in/out activities in date order so we can see expected movements...

I appreciate that this means each PO & SO will need to have the due dates entered per line item, in order for this report to make sense but I can't see a way of checking when we can fulfil a new sales enq/order without checking all activities in turn to see when we will have free stock.

Any thoughts would be welcome.

What you want is not possible, you want data from 2 different areas, the SOP, the POP and they cannot be joined in a meaningful way.

You need to merge the data from Sops and Pops together into one table to do it.

It could be done in Access or Excel but it is not a basic job.

In essence you need to extract the data from the POPs with MS QUERY using the ODBC drivers to give you a sheet with
Due Date, Stock Code and Qty
the Qty is goign to the the outstanding on the order as it could be part delivered.
Entry the Quantity as 0-QUANTITY in MSQUERY so as to have negative quantities.
You only need active orders so filte rout all the inactive stuff

Do the same thing for SOPs only leave the Qty positive

The Qty here would be the qty ordered as you want to see missed shipments as shortages ie negative balances in the report.

Create a third sheet with the stock linked to give you the same columns, only set the due date to "today" or some date long time ago so that it gets listed first

Then write a macro to copy and past the data into one sheet to give you all 3 sets of data in one page sort by product code then balance, this will give you a list of everything. Add a balance column to the right that is the value of the qty column, if the current A cell doe snot match the one on the previous row, other wise it is the qty column added to the balance column in the previous row.

Bar the formatting you are there.

In MS Access, you would copy append all three into one table and then write a report against that table.

Hi Bruce,
Thank you for the feedback and I have to confess that I'm surprised at the complexity involved to get, what I would consider to be, essential information. One key question our salesmen ask is 'when do we have free stock of xyz...'
Am I looking at this the wrong way as I would have thought this would be information that anyone would want from a sales order processing system?

Most people keep stock levels at a level where they can supply most orders from on hand stock, selling future stock is not that common a requirement.

The question isn't when a product will be available, because you might only have 1 available at a date and you need 12 for this order so you really want to know when a qty of a product will be available

You could extend the sheet I describes so you enter the product code, the quantity and it find out and return the date that qty is going to be available.