Is it possible to change duplicate value to zero in specific column by query? (1 Viewer)

[atzdgreat]

OBJECTIVE: To create a Result making duplicate value as zero in PO Amount

AVAILABLE TABLES:
PO Table

Suppliers Table

This is what my SQL query so far:

SELECT t1.*, t2.* FROM PO_Table t1 LEFT JOIN SI_Table t2 ONE t1.[PO Number] = t2.[PO Number]

`

 

[The_Doc_Man]

I think we might need to know WHY you want this result, which is more like an Excel setup than an Access setup. Are you trying to make some kind of report?

 

[atzdgreat]

I think we might need to know WHY you want this result, which is more like an Excel setup than an Access setup. Are you trying to make some kind of report?

Yes Sir. so whenever this would be extracted to excel and user will do a pivot. it's easy for them

 

[arnelgp]

you add Autonumber field (ID) to SI_Table table, see Query1.

 

[June7]

I tested an Excel pivot table with this joined data. Having zeros instead of duplicates of PO Amount does not entirely solve issue. The PO Amount is replicated in the pivot table on the same SI Number that has a PO Amount value in its data row.

 

[atzdgreat]

I tested an Excel pivot table with this joined data. Having zeros instead of duplicates of PO Amount does not entirely solve issue. The PO Amount is replicated in the pivot table on the same SI Number that has a PO Amount value in its data row.

Hi @June7 kindly check my attached image. if PO Amount are redundant, when you pivot the data, it will sumup.

 

[Pat Hartman]

The problem is the way you are creating the invoice. Use a main report for the PO table and a subreport for the suppliers table. Then everything sums and prints correctly.