inventory

[Ahrek]

I am trying to run a back inventory query and I am hoping someone can help.

I know the amount on-site for every product 1/1/03. and I know the date and amount of outgoings and the date and amount of incomings.

I am trying to run a report that will tell me the maximum amount on site at any time in 2003.

I need a way to have a running sum that will add incomings and subtract outgoings. this really only needs to happen on any day that a new order of the product arrived on site since the day with the max amount would have to be one of those dates.

 

[dcx693]

I'd suggest a custom function for this. I'd use ADO or DAO to go through all the records, keeping a running sum, and finding the max of incomings subtracting the outgoings. You can pass the productID to the function to tell it which to search.

 

[Ahrek]

Sounds like a good idea, but I have no idea how to do it.

 

[dcx693]

Have you ever programmed in VBA?

 

[Ahrek]

Not really, I am just getting started in VB.

Working with some VB.Net

 

[dcx693]

Are you looking to display the running sum on a report? That would produce the easiest solution, no coding required, but depending on how many incoming/outgoings you've got, it could make the report really long.

If you just want to display the max number, that will require the custom function.

 

[Ahrek]

I only need the max amount we had on-site for the year. I am fine with writing a module to do the function. I need to tell it to give me the number for every change of date and/or change in product