DB Design Based on Report Generation

[dylcon]

Hello,



I am developing a db that will show on hand quantity of raw materials. From this data, I would like to show which parts need to be reordered. What should happen is the user enters when a part comes in and updates the db daily on how much was used. My very broad question is what is the ideal way to store this data so it can be retieved for a report or alert. I was considering having an 'Adjustment Table' which would show every adjustment made (order requested, order received, and depletion quantity) for every single part. From here, I was planning on filtering the data based on part number, making calculations based on type of adjustment, then running this for each part. Once this is complete (on a normal basis), it would send alerts (via email or msgbox) that notifies the user when they are getting to the point of reordering. I could use all the feedback I can get. Thanks!

P.S. I have posted on here about this db recently and you may think this is a repost, but the main objective has altered since my last question.

[June7]

That all sounds reasonable. When you encounter specific issue in development, post a question.

[Dal Jeanis]

From here, I was planning on filtering the data based on part number, making calculations based on type of adjustment, then running this for each part. Once this is complete (on a normal basis), it would send alerts (via email or msgbox) that notifies the user when they are getting to the point of reordering

This clearly wouldn't have to be done one part at a time, though. Once you have a set of queries and code that would do it for one part, you could do it for all parts in one swell foop.

[dylcon]

Dal, how would I go about doing this then? I have the database pretty well built and some data inside, but now I am focused on creating a report that is useable.
What I need it it do is essentially show the on-hand quantity (tblReceived - tblConsumed) for a specific part. Any ideas how to do this? I started another thread where I am trying to use a function within a module, but I am not sure how to pass the value correctly...

[June7]

Do aggregate query for each table that groups on the part ID. Join those two queries to the Parts table. Build report based on that third query.

[dylcon]

I currently have a query that joins all the consumption, received and parts tables, then i build the report off of this, but the part that is tricky is
a) how do i show the records for all the consumption AND received entries, even if one part may only have one or the other. Right now it is only showing the parts that have a record for BOTH received and consumed.
b) how do I get rid of all these records and just show the sums?

[June7]

See my previous post. That's what I was describing.

[dylcon]

Ok, thanks June7. I will try this, and let you know if I need any additional guidance.

[dylcon]

So I am still running into the same problem... the query that was built is still only showing records of part numbers that have a consumed AND received transaction for the same PartID. I need it to show all PartID's, which will be ones with only received OR and consumed. My query looks like:

SELECT tblPartInfo.PartNumber, tblSupplier.SupplierName, ConsumedSumQuery.SumOfConsumedQuantity, ReceivedSumQuery.SumOfReceivedQuantity
FROM tblSupplier INNER JOIN ((tblPartInfo INNER JOIN ConsumedSumQuery ON tblPartInfo.PartID = ConsumedSumQuery.PartID) INNER JOIN ReceivedSumQuery ON tblPartInfo.PartID = ReceivedSumQuery.PartID) ON tblSupplier.SupplierID = tblPartInfo.SupplierID;

If you need a screenshot, my db uploaded or any other SQL, please let me know.

[Dal Jeanis]

Try something like this -

SELECT 
   tP.PartNumber, 
   NZ(tS.SupplierName,""), 
   NZ(QC.SumOfConsumedQuantity,0), 
   NZ(QR.SumOfReceivedQuantity,0)
FROM 
   ( ( (tblPartInfo AS tP 
        LEFT JOIN 
        ConsumedSumQuery AS QC 
        ON tP.PartID = QC.PartID) 
      LEFT JOIN 
      ReceivedSumQuery AS QR 
      ON tP.PartID = QR.PartID) 
    LEFT JOIN 
    tblSupplier AS tS
    ON tS.SupplierID = tP.SupplierID);

[dylcon]

Thanks Dal! I just realized that I needed to use Left Joins and was learning how to do this.

[Dal Jeanis]

Heh, heh. No charge, man.