General Report Question 1

[BFClem]

As a matter of concept, if you were given this assignment, how would you do it (in general):

Given 3 related tables:
inventory,
purchase orders,
vendor bids,

Identify inventory shortages, and given recent purchases and bid data, make an auto-buy report stating what inventory you would purchase from which vendor at what cost, assuming you want the best deal, by vendor, by item.

Thanks

 

[synapsevampire]

Assuming that Inventory always maintains all open PO and BID items, left outer it to PO by part number, and left outer PO to BIDs.

As for auto buy, that's dependent upon the fields available, which might include a minimum on hand quantity to enforce an auto buy.

So for each part, check open PO's (probably a purchased qty vs. ordered), and then take the minimum $ from BIDs (if minimum alone drives a purchase).

-k

 

[BFClem]

I thank you for your answer and trying to help me.

I have tried as you suggested- inventory left joined out to purchases and bids by item number. I limited the inventory selection to those items needing purchase (quantities on hand less than minimum). From there I am able to see purchases made on the item (I limited it by recent dates) and bids for the item (ditto the dates).

What I am shooting for (maybe a pipe dream) is for the report to help me sort out which item(s) to purchase from what vendors, given the recent purchase and vendor bid history, so that we are:

1. grouping buys by vendor
2. we are getting the best deal

Maybe there are too many passes in one report to do this.

Thanks

 

[synapsevampire]

So a PO isn't opened at order point, they are simply historical rows?

I would redesign this process to automatrically generate the PO (not using CR) at order point. But you can still use your existing tables to help for now.

Eliminate the PO table, it's meaningless as it doesn't exist yet.

In the inventory system I wrote years ago, a trigger would fire on the database to create a PO for anything falling below minimum on hand qty, which also sent an email to the buyer.

Determining the lowest cost is simple, and your report would do this by demonstrating the minimum price for each part. However if you have multiple bids at the samne price, you'll want some other factors, I used a weighting system for the vendors, with no 2 vendors having the same weight for a same part number.

-k