using a query to perform complex calculations.

[WillJulian]

I am a student trying to design a simple MRP system using Access<br>I am trying to design a query which will look at quantity of finished product required, net out the lower level assembly quantities and subtract what's onhand. These fields are all on different tables, and I need the answer to the query to be stored on a seperate table.<br>Can anyone help as I really don't know where to start. <br><br><br><br><br>

 

[Cornerstone]

Well I will have to make some assumptions so let's see if this helps.<br>I assume 3 tables:<br><br>Table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Fields<br><br>Orders-&nbsp;&nbsp;&nbsp;OrdID, ProductID, Qty<br>SubParts- ProductID, SubPartID, QtyNeeded<br>PartInven- SubPartID, PartName, QtyonHand<br><br>Create a query on all 3 tables and show the PartName field and a calculated field called TotalParts with the expression: [qty]*[qtyNeeded]<br><br>Create a second query that uses this query and the PartInven table and create a calculated field called Balance that uses this expression: Balance: [QtyonHand]-[totalparts].<br><br>That will show your balance. <br><br>Of course you can throw in whatever other tables/fields you need to see and if you really need to you can make some Append or Make table queries to throw the info into another table.<br><br>I hope some of these ideas give you a starting point for completing your project.<br>

 

[WillJulian]

Thanks for that it certainly has started me on the right step, now I need a way to calculate lead times! God the fun never ends!!<br>I'll have a expected completion date, a production time and a vendor lead time problem is each will be in a different format ie. Short date, Medium Time and Long time.<br>I think I have been approaching it from the wrong angle, I've been taking the expected completion date and working backwards and I haven't gotten anywhere with that due to the fact that queues, downtime etc.(can be assumed a fixed time ie. a safety factor within our production time) wasn't been taking into account. Would it be possible to create the lead time using the production time and the vendor time then compare this to the expected completion date and create a report to say whether it is a feasible date or not?, or have I completely lost the plot?