Stock aging code

[UncleKay]

Dear friends,
Thank you so much for your assistance so far. The software is hereby attached as requested. I am fully available to give further guidance as the need arises.


Thank you.

[orange]

I am traveling at the moment, but will look at your database in the next day or so.
Have you identified in simple terms

--a definition of Stock aging that represents what you are doing
--any formulas for calculations you have done, or need

UPDATE: I did look at the database. I don't use macros. Also, I see you have many tables and relationships, but I honestly don't follow what you are doing. You seem to have done a tremendous amount of work, but I would have expected you to test the algorithms with sample data. It just seems you have done all this work, and yet there are concerns over the underlying arithmetic.

[UncleKay]

Thank you Orange for your efforts so far. As you observed, I have really tried to isolate this issue but there is still a problem somewhere either on the code or my appproach. My previous attachments contain the codes and approach. Surprisingly the report for less than and greater than one year renders accurately but the multiple aging report has the imbalance in the totals. If I can have your Skype id or Whatsapp id, may be verbal explanations will help further. You can also log into my computer remotely through team viewer if you will have the time so that we can explore it together. Thank you for your patience so far

[UncleKay]

I did not use macro for the implementation of the code. I used queries basically. One query for each aging condition as in less than 90 days, 3 months to 6 months, 6 months to 1 year, 1 year to 2 years, 2 years to 3 years and above 3 years. Then I brought all the queries into one master query where the code was implemented. The report was raised with the master query to accommodate all the ranges.

[orange]

Did you look at the materials and databases mentioned in my post #11? That's the best info I can find.

Do you have instructions for opening and running specific things in your database? There are several tables, queries and forms. It isn't something you can guess at what each thing means; is intended to work etc. It would be helpful to any reader if you could give some instructions regarding a product/batch?? and follow it through your processing. It is a complex subject generally and trying to guess(trial and error) your way through your code makes it extremely difficult.

Perhaps there is a reader who works in logistics and inventory management who can shed some light on the subject.

[orange]

I did not use macro for the implementation of the code.

I get an echo macro error immediately on opening??
I also get to an Administrators form with no instructions???
I am willing to help, mainly because I can find no info on stock aging in plain English, but you're going to have to provide some instructions on using your database.

[UncleKay]

Hello friends,
Please find attached the users manual for the UncleKay software that will guide you through understanding the problems I have with the multiple stock aging report.
Thank you for your continued patience.
Kalu

[rpeare]

Never mind post after mine came in

only a word document in the zip, sent PM with my email address if you can send the zipped up 2mb file I will try to pare it down for other folks to look at.

[orange]

Try this adjustment to qryPreviousClosingInventory to get a value of 0 for the StartingPeriod

SELECT qryCalculations.[Item Number]
, qryCalculations.[Week Commencing]
, qryCalculations.ClosingInventory
, Nz((Select qry2.ClosingInventory from qryCalculations as qry2 
where qry2.[Item Number] = qryCalculations.[Item Number] and 
qry2.[Week Commencing] = qryCalculations.[Week Commencing] - 7),0) AS PreviousClosingInventory
FROM qryCalculations;

Note: All tables in a relational database should have a Primary Key.

[rpeare]

Here is a stripped down version with just the report in question at least as far as I can tell.

UncleKayRequiredElements.zip

[UncleKay]

Beloved,
Please can you explain further. Do I create a new query and insert this code or paste it in an existing query for an adjustment as you suggested.
Kindly explain further

[UncleKay]

Dear All,
The stock aging issue I raised is till pending. I have sent the software, users manual and explanation of codes used. Can anyone pleases assist me further to solve the multiple stock aging issue. Please note that the less than greater and greater than one year renders accurately according to the clients requirement while the multiple aging still have issues.
Thank you

[orange]

No attachments????

[UncleKay]

The files are re uploaded as requested.
Thank you

[orange]

The links I provided in post #11 are the best I can offer. The original database is at
http://www.access-programmers.co.uk/...d.php?t=153802
You may get some necessary info by reviewing the database.
You are much closer to the issues and your approach to solution than anyone.

I suggested earlier that you might take 1 or 2 ( a few) products and review your algorithms/calculations for the various time frames. With a few samples you should
be able to isolate any errors in calculations. Since your results match expectations
for certain periods, it would appear that there is something in a calculation related to the
other time periods that is incorrect.

Good luck with your project.