Adding two Fields to Credit Card Transaction Report - Unable to Join or Union

[kgruenberg]

I am building a a customer report based on the Credit Card Transaction standard report.  I can pull in all but two fields from the Credit Card table - but have failed at creating a Join or Union to bring in the Approval Status and Account Code from the expense table.  Everything works well when i run the queries independently - But Things blow up with a Union or Join.  Thinking the join is issue is in the Keys I am using and/or the cardinality i am selecting.  The Join just fails to run even though i followed the rules for creating a union - Same data fields in the same order.  Would greatly appreciate help to quickly resolve the block and allow me to move forward with creating the report.  I have watched training the training videos - attended a webinar - but still am unable to resolve.  Would love to hear from a Reporting Guru who can help me add two fields to a standard report.  

[KevinD]

@kgruenberg I'm wasn't clear on one thing you said. You mentioned the Join fails even though following the rules for creating a Union. Joins and Unions are not quite the same thing. So, were you creating a Join or a Union.

Also, for Account Codes, you won't see an account code for an expense type until that expense has been Sent for Payment.

Maybe there is another way to get what you are looking for...what exactly are you trying to accomplish as the overall goal of the report?

[kgruenberg]

@KevinD - Thank you for responding.  I am trying to build a custom report based on the Credit Card Transaction Standard report.  I can get most of the fields from the Credit card data table - But have two Fields associated with the expense report - Approval Status and Account Code 1.  I attempted to add the credit card table fields into a query - the two expense report related fields into a separate query - and use a Join to populate the report.  I either get duplicate entries or - missing Approval Status and Account Code 1.  THese are the fields i am trying to populate in the report.  Totally open to any approach to get these fields in a report - thinking I am grabbing the wrong key to complete the join and incorrectly assigning cardinality.  Any insights would be greatly appreciated 

CC

x

X

x

x

x

x

x

x

x

x

x

x

x

x

Employee

Payment Type

Transaction Status Value

Approval Status

Merchant

Expense Type

Amount

Company

Cost Center

Account Code

Posted Date

Transaction Date

Name on Card

Last Four Account Digits

Default Approver

Expense

x

x

[KevinD]

@kgruenberg have you considered using a Union instead of a Join? Also, remember that Account Code will not populate for transactions that are not yet fully approved. 

[kgruenberg]

@KevinD  - Thank you for the guidance/feedback.  I was able to create the join and generate the custom report.  All fields are populating as expected - but I'm now getting some duplicate transactions in the report so unable to reconcile with the Corporate card statement.  Is there a way to block duplicates in the report?  Any ideas on what would create the duplicate transactions?  No specific pattern - Meals - Hotel - Tolls - Thank you for the support.  

[KevinD]

@kgruenberg could you post a screenshot of the duplicates, please? Ideally I'd like to see the data column titles and some duplicates if possible.