show records based on calculated field

[Shawna]

I'm not sure if this would be done on report or the query to get the records on the report? Or if it can be done at all
I have a report which shows results based on a query. It lets me know if items of a sale are in stock, that I can 'issue' the sale (function of a command button the user has to press).
ie. ItemNumber WIDGET has qty on hand of 50
Sale #1 wants 45 WIDGET
So based on the above criteria, the sales requirment has been met so this record would show on my report has being able to issue
Using the data from above, along with Sale #2 wants 25 WIDGETS I'd like the report to not show that sale #2 is ready for issue. - the way it is set up now, it does meet the criteria because again there is 50 on hand so 'legally' those 50 could be applied to Sale #1 OR Sale #2.

Currently the client would have to go to perform the 'issue' command button to deplete the inventory for Sale #1 then Sale# 2 requirements wouldn't be met so wouldn't show on the report, I'd like the client not have to go through this step and be able to have him just run the report

So can I do something like this on the report or query using a calculated field to pick up the first record (already established that enough is on hand for it to show) but then each subsequent record for this part # only show if
QtyOnHand - QtyNeeded (from previous sale(s)) is greater then Qtyneeded (for this sale) show on the report

Trust me I've been trying to work this out but I don't know how - anyone with some ideas...
Thank you in advance for any help/suggestions on this

 

[SA]

Shawna:

Yes you can do it. Its not necessarily straight forward. You'd want to do
it in the query, not the report and you'd do it using subqueries.

Concept:

1.) You need look at potential sale numbers, these normally would be ordered
by a primary key value. So you'd sort your sales order part requests by
order number based on the PK.

2.) Then you need to create a query that looks at the cumulative total of
orders pending and unfilled on a roll up basis.

3.) Last you'd compare stock - the cumulative order quanity and if its less
than 0, have a "fillable" field that returns false.

The key issue is the subquery that is used in item 2 above. You could look
at our web in the Code and Design tips area under queries to see how to use
subqueries to auto number and to do other things. But (and this is air
code, probably not exactly right in terms of SQL), the subquery might look
like this:

CumOrderQuanity: (Select Sum(OrderQuanity) from tblOrders as Temp WHERE
Temp.OrderID < tblOrders.OrderID AND temp.ProductID = tblOrders.ProductID
AND tbltemp.OrderFilled = False ORDER BY temp.OrderID)

This assumes that you'd have table orders as part of the main query and this
subquery is simply a field. So it compares for each row that represents a
product id and where its unfilled in the main query, with all preceeding
orders (i.e. temp.orderID is less than the current row in the main query
which has a tblOrders.OrderID). So you'd have results that are cumulative.

Then as noted above, simply compare the OrderQuantity field in the main
query with the CumOrderQuantity of the subquery in another aliased field as
in: Fillable: IIF(([Stock] - [CumOrderQuanity])>=[OrderQuantity]),True,
False). You can then simply set the condition on this column to be True.

Should result in a query which only shows those items that are fillable.
On the other hand you might want to show those items that are non-fillable
and set them to back order or to update the db's data that shows the client
what they've got to order themselves.....

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

I'm not sure if this would be done on report or the query to get the

records on the report? Or if it can be done at all?

I have a report which shows results based on a query. It lets me know if

items of a sale are in stock, that I can 'issue' the sale (function of a
command button the user has to press).

ie. ItemNumber WIDGET has qty on hand of 50.
Sale #1 wants 45 WIDGETS
So based on the above criteria, the sales requirment has been met so this

record would show on my report has being able to issue.

Using the data from above, along with Sale #2 wants 25 WIDGETS I'd like

the report to not show that sale #2 is ready for issue. - the way it is set
up now, it does meet the criteria because again there is 50 on hand so
'legally' those 50 could be applied to Sale #1 OR Sale #2.

Currently the client would have to go to perform the 'issue' command

button to deplete the inventory for Sale #1 then Sale# 2 requirements
wouldn't be met so wouldn't show on the report, I'd like the client not have
to go through this step and be able to have him just run the report.

So can I do something like this on the report or query using a calculated

field to pick up the first record (already established that enough is on
hand for it to show) but then each subsequent record for this part # only
show if

QtyOnHand - QtyNeeded (from previous sale(s)) is greater then Qtyneeded

(for this sale) show on the report.

 

[Shawna]

Thanks for your help on this, I'm going to try it today! I couldn't figure out how to do the cumulitive qty.