Is it possible to change duplicate value to zero in specific column by query?

atzdgreat

Member
Local time
Today, 07:29
Joined
Sep 5, 2019
Messages
42
OBJECTIVE: To create a Result making duplicate value as zero in PO Amount
Result Table.png


AVAILABLE TABLES:
PO Table

Screenshot 2024-02-15 133722.png


Suppliers Table
SI Table.png



This is what my SQL query so far:
Code:
SELECT t1.*, t2.* FROM PO_Table t1 LEFT JOIN SI_Table t2 ONE t1.[PO Number] = t2.[PO Number]
`
 
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?
 
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
 
you add Autonumber field (ID) to SI_Table table, see Query1.
 

Attachments

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.
 
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.
TestResult.jpg
 
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.
 

Users who are viewing this thread

Back
Top Bottom