Who's On?

	Membership:
	Latest: monte1989
	Past 24 Hours: 0
	Prev. 24 Hours: 2
	Overall: 5324

	People Online:
	Visitors: 342
	Members: 1
	Total: 343

	Online Now:
	Sheltons4fun

New Topics

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3293
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244

AP vendors report

4 Replies

1 Subscribed to this topic

43 Subscribed to this forum

Sort:

Author

Messages

Chesca

Veteran Member

Posts: 490

6/30/2015 6:52 PM

I am trying to create a list of vendors including name, tax id, diverse code, phone number, class, and address. I joined the APVENMAST AND APVENADDR but I am getting multiple address per vendor. I think I'd like to get the maximum date or most current vendor's address from the file and I don't know how to. Any help would be greatly appreciated.
Example:
Vendor 1 MED PO box 1235 1/18/11
Vendor 1 MED PO box 3953 3/26/12

TracyO

Veteran Member

Posts: 97

6/30/2015 7:26 PM

I have a similar report but also included the APAUDIT table. I then added a group on APVENMAST.VENDOR and did a secondary sort on APAUDIT.TRANS_DATE. Then suppress the detail section and print the Group header or footer whichever on you put your fields on.

Chesca

Veteran Member

Posts: 490

6/30/2015 8:25 PM

Would you be willing to share your SQL or report via email? We have to provide the state with this report by end of day tomorrow and my users did not let me know they needed this until this morning.

TracyO

Veteran Member

Posts: 97

7/1/2015 11:23 AM

Here is the sql

SELECT "apvenmast"."VENDOR", "apvenmast"."VEN_CLASS", "apvenmast"."VENDOR_VNAME", "apvenaddr"."ADDR1", "apvenaddr"."ADDR2", "apvenaddr"."CITY_ADDR5", "apvenaddr"."STATE_PROV", "apvenaddr"."POSTAL_CODE", "apvenmast"."TAX_ID", "APAUDIT"."FIELD_ID", "APAUDIT"."TRANS_DATE", "apvenmast"."VENDOR_STATUS"
FROM ("LSFPROD_PROD9"."APVENMAST" "apvenmast" INNER JOIN "LSFPROD_PROD9"."APVENADDR" "apvenaddr" ON ("apvenmast"."VENDOR_GROUP"="apvenaddr"."VENDOR_GROUP") AND ("apvenmast"."VENDOR"="apvenaddr"."VENDOR")) LEFT OUTER JOIN "LSFPROD_PROD9"."APAUDIT" "APAUDIT" ON ("apvenmast"."VENDOR_GROUP"="APAUDIT"."VENDOR_GROUP") AND ("apvenmast"."VENDOR"="APAUDIT"."VENDOR")
WHERE "apvenmast"."VENDOR_STATUS"='A'
ORDER BY "apvenmast"."VENDOR", "APAUDIT"."TRANS_DATE"

Chesca

Veteran Member

Posts: 490

7/3/2015 5:45 PM

Thank you soooo much TracyO!!