Complex "Report"

[dylcon]

Hello, I currently have a database Attachment 12769 that is meant to manage inventory. The main point of my database is to accomplish two things:

a) keep a record for each individual part upon user's request (solved already)
b) be able to view a table/report (possibly a pivot table in excel) that will be able to view all "active parts" (quantity > 0). I will refer to this as the schedule from here on out.

I need the schedule to only have one record for PartNumber and PONumber combo, which is the PartPOID in my db. Once the quantity is equal to zero, which will be calculated by the user entering incoming, outgoing, and adjustment quantites via forms, that record needs to be deleted from the table.

I was planning on creating a new table (not 100% sure how to relate it) that would allow for records to be deleted, without the records being deleted altogether so part a of the database would still work fine. A possible way to do this would be running a query that adds the necessary fields of the record added to tblTransaction once the user completes a form, but I am not sure if this is the best way to go about doing this.

If anyone has any suggestions on how to go about doing this, I would really appreciate it. Thank you.

[June7]

Your attachment link does not work.

Not really understanding why records need to be deleted. Record deletion in a relational db should seldom be necessary. Sounds like db is not properly structured.

[dylcon]

AccessForumFolder.zip
This should work

It needs to be deleted so that only parts with a quantity (meaning it is in our warehouse) will show up. It is deleting a record, but all of the information about that record will be stored somewhere else. If there is a better way, I am all ears.

[June7]

The conventional approach is to use all the transaction records to calculate quantity net balance. http://allenbrowne.com/AppInventory.html

Have you looked at the MS Desktop Product Inventory template database? Rats! I just downloaded it. It is a web database. And it doesn't seem to have a report that calcs product available. Sorry, not the best example after all.

[dylcon]

I understand that this is the conventional approach, but this is not exactly the most conventional task, from what I have been told. My company currently runs the schedule in an excel spreadsheet because of all the exceptions between the different customers, different part numbers, and different PO scenarios.

I am not saying that I won't be able to do it with the transaction records, but I don't know how to do it this way. Everyone on this forum has been nothing but a TREMENDOUS help and I trust what people have to say, but I will need some more direction as how to do it this way...

[June7]

I built and manage a laboratory database to track lab test data. The only deletion allowed is if a test associated with a logged in sample is no longer desired. And that is not even permitted after the sample cost accounting has been reported out to finance department (I work for state government).

I have built other dbs where the users aren't prevented from deleting records but the design is such that it is not necessary. I have never had to build any kind of inventory or accounting db but I am familiar with the concepts. I don't know your business model so can't comprehend what you mean by 'exceptions'. What I do know is that a properly structured db should probably not need records deleted. However, build what suits you.

[dylcon]

Ok. Then maybe items need to be deleted from a report or something along those lines. I just don't know how else to do it, so I thought that creating a table that would hold records that were not vital to the rest of the database's operation would be a reasonable approach. If I am able to do this via tblAdjustment, I would like to know where I could get started.

What I mean by special exceptions is that sometimes the incoming quantity is different than the needed quantity. Sometimes the raw part number is different than the incoming part number and it depends which one shows up on the schedule. Sometimes there is a "blanket PO" which means it will cover a part forever and sometimes each indiviual shipment of a part number will have a new PO.

Like I said, if it is possible through my Adjustment table then that would be great, but I do not know how to start from there, which is where I hope you or someone else could offer me advice.

[June7]

If you don't want records to show on a report then apply filter criteria to exclude records. This is what queries are for - to manipulate data with calculations, filtering, and sorting.

The only experience I have with something like 'blanket PO' is with government. Agency sets up a contract ('open-ended' purchase order) with a business effective for a period, usually annual. Delivery orders can be placed under terms of the established contract.

I expect db will be complex but doable. A lot depends on what you want to document. The simplest approach is to base inventory calc on items ordered less items sold. But simple doesn't always fit reality. Say order is for 14 widgets but only 12 are received, 2 are backordered. Probably should base inventory calc on received. So I guess one approach is the adjusting record to reduce the order quantity by 2 then when the backordered items are received another adjusting entry to increase the order quantity. Another approach is presented in this old mdb template that I think demonstrates calculating inventory balance http://office.microsoft.com/en-us/ct...420.0&Result=8# Look at the Product Summary report. The InventoryTransactions table isn't fully normalized structure but it does seem to work.

I can't really tell you what to build for your situation, can only offer ideas. You will have to identify the data entities and relationships for your business model and build to handle them. You also have to learn the tools to build with: relational database principles, Access functionality, programming concepts, programming language (VBA or macro).