Duplicate Values
I have two tables similar to this:
This is causing a problem in Crystal (where I use this) as it's adding 123.45 twice.
Is there any way that I can, using the tables provided (that I can't modify) produce results something like this:
TIA
-Invoice_Table-
Customer Job_No Sale_Amt
12345678 123456 123.45
87654321 246800 645.23
-Vendor_Table-
Customer Job_No Vendor Vendor_Cost
12345678 123456 ABCDEF 10.50
12345678 123456 UVWXYZ 20.92
87654321 246800 LMNOPQ 12.45
SELECT
Inv.Customer,
Inv.Job_No,
Ven.Vendor,
Ven.Cendor_Cost,
Inv.Sale_Amt
FROM
Invoice_Table Inv
LEFT OUTER JOIN Vendor_Table Ven
ON Inv.Customer = Ven.Customer
AND Inv.Job_No = Ven.Job_No
Customer Job_No Vendor Vendor_Cost Sale_Amt
12345678 123456 ABCDEF 10.50 123.45
12345678 123456 UVWXYZ 20.92 123.45
87654321 246800 LMNOPQ 12.45 645.23
This is causing a problem in Crystal (where I use this) as it's adding 123.45 twice.
Is there any way that I can, using the tables provided (that I can't modify) produce results something like this:
Customer Job_No Vendor Vendor_Cost Sale_Amt
12345678 123456 ABCDEF 10.50 123.45
12345678 123456 UVWXYZ 20.92 0 (or NULL)
87654321 246800 LMNOPQ 12.45 645.23
TIA
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
In crystal report set Supress IF duplicate to TRue.
ASKER
PatelAlpesh,
Setting a field (value) to "Supress If Duplicated" doesn't remove the value from cummary calculations. All it does is hide the field from the display/print out.
Setting a field (value) to "Supress If Duplicated" doesn't remove the value from cummary calculations. All it does is hide the field from the display/print out.
ASKER
Sorry, that should have read "Setting a field (value) to "Supress If Duplicated" doesn't remove the value from summary calculations."
Did you ever try http:#a35757303 < That is exactly what I meant as it uses row numbering to establish a row 1 and thus can use that in case statement to display sales amount on that row and 0 on all others for the same customer and job combination.
ASKER
Yes, and it works perfectly.
I did have to test it this morning, but had a couple of unrelated issues that kept interrupting me.
Grade/points coming forthwith.
I did have to test it this morning, but had a couple of unrelated issues that kept interrupting me.
Grade/points coming forthwith.
ASKER
Works perfectly.
Thanks.
Thanks.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
The report is supposed to show the lines like the sample data above. Basically the Customer, Job_No, Vendor and Vendor_Cost. Although it's prefered to have the Sales_Amt as part of the line, it's confusing in this particular context. The Sales_Amt is, ultimately, being summed as a grand total. In Crystal I can "hide" the duplicate Sales_Amt. However they do still exist and are added into the grand totals.
When the report was originally created, we only used one Vendor per job. In that case, everything was hunky-dory. However, this month it was decided to parcel out work to multiple vendors, which created the problem I have today.