I'm creating a consumption and costing application for our company to track component use and ultimately inventory. In this first phase, I'm simply looking to have reports that give our overall component use based off of sales for a given period.
Example: On 2/1/19 we sold 1,200 radios, I need a system to tell me the theoretical quantities (and ultimately costs) of the components used for the radios sold.
My initial table structure is as follows (arrows indicate the 1 to many relationship):
Time-period --> Sales <-- Product --> Build Master --> Build-Components <-- Components
Think of the Build ingredients as the ingredients for a recipe. A product may have a ver1 build spec then later updated to a ver 2. Right now, there is no complexity here; simply 1 Build Master for every product. Relationships are based on primary key fields except for Time period to Sales; that is based on date ranges.
I built a report using "Build-Ingredients" with sales restricted to a time period (week numbers) and sub summary for that time period and then a sub summary for the build-components. The results are rather odd to say the least. I was expecting the sub summaries to not tabulate correctly (there are a number of levels between that and sales). What is odd, however, is I'm getting the incorrect ingredients listed under the time frame.
Query weeks 2 - 4. Report based on "Build Components". Sales data is coming in already summarized to a week level, so no math here. Sub summary on week (I've tried using both the Time Period table and the Sales table for this). Sub summary on the items of Build-Components. The results almost look like whatever was sold first shows up, but I haven't quite figured out the logic behind this.
As a workaround, I created components-used usage table where I go through every the sales for every product and create entries into a new table for the components used for those sales (i.e. # of radios sold x build components = components used for those sales). This will work for historical information, and indeed performance may be better if the data is already summarized. For futures sales / component estimates however, I was hoping for something more dynamic.