Having trouble with several issues, can't seem to figure out what is going on

[Dave14867]

Hello all,

I am having some issues and I am not sure what is going on and why I can't figure out what is happening and why. I started with a template I found and am trying to make it work for me, maybe that was the start of the problem. It is intended to act as a small MRP system which I understand is not the easiest thing to work on for a beginner, but I find it challenging which I like. The file is attached.

Things I am having problems/issues with.


1. I can't seem to figure out how to create a report that will give me a Quantity on Hand for all parts. (tblPartMain) & (Inventory Transactions)
2. Purchase Orders Table gets bloated, every time an entry that is made when a part is recorded as received in the Inventory Transactions table, an entry is made in the Purchase Orders table, I don't understand why this is necessary and I prefer it not happen, I just want the table to have info pertaining to that PO only. Does that make sense?

I am by no means proficient at Access but I am trying to learn and hope that some of you will be able to assist.

Dave -In Process - 9-1-18 - Working on.zip

Thanks in advance
Dave

[June7]

See if this helps with balance calc http://allenbrowne.com/AppInventory.html

Parts have to be purchased. A purchase order supports the financial commitment to acquire parts. Parts are used to fulfill work order. Transactions table documents the quantities received and used. Allowing transactions for receipt of parts without connection to PO would break 'paper trail'.

[Dave14867]

June7

So the Purchase Order table needs the line for each Inventory Transaction entry, OK.

I understand that it is preferable for a number of reasons to calculate the Quantity on Hand and that is a big issue for me. I am not that fluent in VBA or SQL and I can't seem to put together the code to make it happen. I know that I should take the "UnitsReceived" - "UnitsSold"-"UnitsShrinkage"-"UnitsUsed"+"CycleCountCorrection" in the "Inventory Transactions" table for each "MfrPartNumber" in the tblPartMain. One thing I am hung up on is that I need to step thru the "Inventory Transactions" table on each entry correct, for each part number? Do I do this with a Loop?

If you look at the "Inventory Quantities" Report in the attached file, you see what I am trying to do.

I should also add, I want to reuse this functionality on other reports, so I would want to create this as a function, correct?

Thanks

Dave



Dave -In Process - 9-4-18 - Working on.zip

[ArviLaanemets]

PurchaseOrders table needs a row per purchase order (the purchase order has to be for single supplier, and usually does have certain sign date).
Purhased parts/items/articles are described in separate table (e.g. PurchaseOrderRows). This table has a Foreign Key field where value of primary key from according PurchaseOrders row is stored. This table has a row for every purchased part associated with purchase order.

To have an overview of storage, you need Storage/Transactions table, where all incoming and outgoing articles and their quantities are registered (you may have there a field where you register transaction type, like purchase, sale, outgoing into production, incoming from production, scraping, etc.). Transaction table may have the value(s) of PurchaseOrders PK and/or PurhcaseOrderRows PK as Foreign Keys. Summarizing (transaction quantities being positive or negative does depend on transaction type) transactions over certain period gives part saldo over this period.

[Super Mario]

Dave14867,

To get the Inventory of all (received) parts, you can try this query:



Then the query can be an input to a report.

[Dave14867]

Super Mario,

That is extremely close, But I need the other fields Also. When I try to set it up, I get duplicates which I don't want in the report.



What am I missing?

[June7]

Is QtyOnHandReport your attempt or did that come with the template? Do aggregate calcs in the part number header or footer section. Why is PartDescriptipn in a separate group from MfrPartNumber? Those two fields should be together in same group.

[Dave14867]

Super Mario,

I had made a mistake on the query, I hadn't set up the Grouping like you had. I have now fixed that and getting better results but I cannot seem to get the expression correct at the end to sume everythin up, I get no results.





June7,

That report is one of my attempts, yes I agree it should be with MfrPartNumber, "Inventory Quantities" is really the report I want to use, I should have deleted the other report.

[June7]

Either will probably get you to the same place. However, if you want to view detail data as will as summary calcs, go with the first report and do grouping and aggregating in report, not query. Consider expression in group header:

=Sum([UnitsReceived]) + Sum(Nz([CycleCountCorrection],0)) - Sum(Nz([UnitsSold],0)) - Sum(Nz([UnitsShrinkage],0)) - Sum(Nz([UnitsUsed],0))

If you decide not to output the raw details, then set the Detail Section as not visible.

[Super Mario]

Dave,

1. Use create and design query to create a Select Query.
2. Add the tblPartMain and Inventory Transactions into the query (the linking of tables is done automatically)
3. Click on the Query Type "Crosstab" and then click on "Select" query type again.
4. On the Total row of the query design set it (from left to right) Group By, First, First, Sum, Sum, Sum, Sum, Sum

And that is all you need...

[Dave14867]

Super Mario,

I need a QtyOn Hand Calc on the end of the query don't I? I am not sure where your getting 30 in the SumOfUnitsReceived for PN 19800?

June7,

I prefer a single line for each Part Number, easier to read that way for its purpose. Does it create an issue, and can you explain more of what you are suggesting as I not familiar with aggregating. Are there pitfalls or issues with doing it like Super Mario suggests?

Thanks
Dave

[Super Mario]

Dave,

SumOfUnitsReceived for PN 19800 = 30 (is the sum of transactions 2042 and 2043 in the Inventory Transactions table) so the query result is correct

Question: What is the expression for QTyOn Hand Calc? QTyOn Hand Calc = Units Received - Units Sold - Units Shrinkage - Units Used (+ or -) Clyde Count Correction ????

[Dave14867]

Super Mario,

I am not getting 30 for PN 19800, I'll look into your findings in lines 2042 & 2043 in the Inventory Transaction Table, not that I am doubting you, but that part should be just 35 as I was using it for a test, It should really be 0 for QtyReceived

The expression for QTyOn Hand Calc = Units Received - Units Sold - Units Shrinkage - Units Used + Clyde Count Correction

I have tried it in the query, on the form, I just can't get it correct, I actually get no results at all.

Thanks

Dave

[Dave14867]

Super Mario,

Line 2026 of the Inventory Transaction Table is for PN 19800 (ProductID=1) which shows the test entry I made to add the 35 pcs to the "CycleCountCorrection", Line 2042 is for (ProductID=813, PN M22555), and Line 2043 is for (ProductID=815, PN M22556)

If I get what looks like a realistic report, I'll confirm the results with a spreadsheet we use (very awkwardly)

Thanks

Dave

[June7]

You are familiar with aggregating. That's what aggregate functions Sum(), First(), etc are doing.

Did you try my expression which uses Nz() function? Did you try my suggestions for report design which will provide 1 line for each part?