Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Double entries - vendor expenses

Status
Not open for further replies.

lisa13ep

Technical User
Apr 18, 2012
12
US
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.
 
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
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top