Missing information from Report

[angloman]

Hi everyone,



I can't figure out how to get my report to display the right information.

My report is driven by a query. It usually works, but when I have the same raw material from the same lot being used in the same recipe, it only displays one result. This would happen when, during a production, we have 2 steps.

In the example below, it should show me that in production 041167, I used 5.5 units and 80 units of CSOR. It's only showing me the top result which is 5.5 units.

Here's the query results:



Here's the report layout:


and this is how my report prints:

[June7]

That's because GROUPING is an aggregate operation. Only unique values can be displayed and only the first record is output in the GROUP. Do a Sum() calc on the Quantity. Or put Quantity field in Detail section if you must see raw data.

Data structure appears not normalized as indicated by multiple fields for same type data (products). Not sure you will get the report output the way you want with this structure.

[angloman]

Thanks June7. Adding a sum to quantity fixed the problem. As long as my report gives the total quantity of the raw material with the specific lot used for that production it's fine with me.

[angloman]

I spoke too soon. It doesn't work.

Is there a better way to report on this structure?

I'm trying to display a hierarchy as follows:

• Raw almonds LOT 5151451
  
  • Production 041188 transformed raw almonds into dry roasted almonds DRAL4
    
    • Production 041182 transformed DRAL4 into finished product 23046 - packaged dry roasted almonds

Here's an example when it works:

[June7]

Sorry, not understanding data structure and form design. Why are there multiple sets of 'product' fields?

How does 041167 return 165 for 2 records (5.5 + 80 should return 93.5)?

[angloman]

There are multiple 'product' fields because we can have up to four levels of sub-productions within our production process. The purpose of this exercise is to be able to track a raw material to finished product, which could happen after 1-3 productions. Each production has a number (ProNum)

Here's some examples:
Raw material --> finished product (1 production)
Raw material --> recipe --> finished product (2 productions)
Raw material --> intermediate recipe --> recipe --> finished product (3 productions)
Raw material --> intermediate recipe --> recipe --> intermediate finishing --> finished product (4 productions)

All of our recipes can become intermediate recipes, and they can be used in multiple other recipes or finished products.

To answer about the quantities, it's because we're looking at the quantities of the parent material used in each production.

If we look at the second example I gave, we follow ALCAR (almonds) with LOT #5151451

041167 used 165 units of ALCAR (almonds) to produce PAV1 (vanilla praline almonds recipe).

041164 used 56 units of PAV1 to produce 3837 (packaged vanilla praline almonds).

Sorry I guess that's confusing. The top example was tracing CSOR (sea salt). So CSOR with LOT number 514545 was used twice in production 041167. 5.5 units in step 1, and 80 units in step 2.

[June7]

Sounds like manufacturing/production type database. Not an easy one to design and I never have.

Seems to me normalized structure would be a separate record for each level.

[angloman]

I could probably build a different structure to make the report work. I used groups as a way to link child and parent. Is there a better way to achieve that?

[June7]

Not sure, maybe related dependent tables - normalized data structure. I just do not understand the business process. You have two records (5.5, 80) for two 'steps' for ProNum1 (first 'level'?) 041167. Perhaps need an intermediate table for the 'levels' and then another table for the 'steps' which would link to 'levels' which would link to 'lot'.

[angloman]

This is where I've been getting confused. I have it in a normalized structure to start but I can't figure out how to report with it, so I turned it into what it is now with a query.

This is the data structure for the almonds



and here it is for the sea salt

[June7]

I presume the FK fields in the dependent tables are not visible.

I think this may call for a recursive database design. Search forum and web on the topic. Start with https://www.accessforums.net/databas...ign-38357.html

[angloman]

I think you're correct.

To be honest, everything in my DB works right now except that one issue in the report.