Crosstab Query “Min” Function Not Considering Zero

[RAJESHKUMAR R]

Dear Friends,

SELECT MASTER.finished_part_no, MASTER.terminal_from, MASTER.crimp_type_from, MASTER.crimp_type_to, MASTER.terminal_to, MASTER.cable_part, MASTER.cable_type, MASTER.awg, MASTER.wire_color, MASTER.length, MASTER.qty_pc,

Nz([Database_Crosstab].[Total Of Net Inventory],0) AS [Kitting Inventory], Nz([Total Of Net Inventory]/[qty_pc],0) AS [Min FG Can Feed]
FROM MASTER LEFT JOIN Database_Crosstab ON MASTER.cable_type = Database_Crosstab.cable_type
ORDER BY MASTER.length;

In the Above Query, Red Highlighted portion, I have used Nz Function to Replace black cell as Zero
When I am trying to create the Crosstab Query as below With above query table, Even though I am taking “Min” in crosstab query… Let’s say one of “finished_part_no” contains 5 Records in above Query table out of which one Row contains “zero value”, Ideally my crosstab result for that “finished_part_no” should be “Zero” Whereas crosstab query ignoring Zero value and updating next least value in the Result…

TRANSFORM Min(([Min FG Can Feed])) AS Expr1

SELECT FG_Completeness.finished_part_no, Min(([Min FG Can Feed])) AS [Total Of Min FG Can Feed]
FROM FG_Completeness
GROUP BY FG_Completeness.finished_part_no
PIVOT FG_Completeness.awg

Could you please help me in amending these query to come out of my concern i.e. MIN Should consider Zero too update the crosstab query Result?

Thanks & Regards,


Rajeshkumar R

[June7]

First query references a dataset named Database_Crosstab - this is a crosstab query?
Then second query is a crosstab of the first query (which includes a crosstab)?
Never seen that.

If you want to provide db for analysis, follow instructions at bottom of my post.