Double entries - vendor expenses

[lisa13ep]

I am trying to create a report to show how much we spent per each vendor during a certain pariod.
I am using tables JrnlHdr, JrnlRow and Vendors. I am pulling fields Name, Reference, TransactionDate and MainAmount.
For some reason it is coming up double - it is showing invoice and payment on that invoice. Also it sometimes duplicates the invoice itself.
How can I fix this?
I was trying to filter by JrnlRow.RowNumber (4 - purchase or 2 -cash disbursements) but it does not work.
Thank you.

 

[crystalkiwibruce]

Duplicate records are usually a table join problem.

Are the amount fields from the JnlRow table? Or should you be ignoring the payments and only select the invoices?

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[lisa13ep]

most of the fields are from Jrnl.Hdr. The connections right now are inner joins. I tried changing it to outer left or right - one of them comes up the same and the other shuts down my Crystal, some database error.
If you know how to select only invoices please let me know. There is no field "invoices" in any of the tables

 

[IanWaterman]

Its difficult to see why you are getting duplicates without knowing the data structure.

As Bruce said it could be that JNLRow table is causing the problem if that has more than one row per invoice.

If you can show some sample data and what you want to see we might be able to offer suggestions. It could be a filter issue. Or you may have to write some bespoke SQL and use a command instead of just joining tables in Crystal

Ian

 

[lisa13ep]

Don't know how to attach files in here.
The problem is the data that comes up is pulling both invoices and payments on them - credit and debit entries for a vendor.
I need to somehow specify just one - either debit or credit in their ledger.