9/8/2017 - 4:16 PM

Check Date using Transaction Table


Curious if anyone knows how to fix this formula to pull the check date. I am using the Transaction table but need the check date as a reference.

I am pulling Vendor, Project ID, Cost and Vendor Type as my columns with the formula attempting to pull check date.

Ultimately I need to get data that shows project info, vendor, payment amount, vendor type or activity, and payment date so if there are any suggestions to get this data but from a different table, I'm all ears.

SELECT TOP 1 CONVERT(VARCHAR, beDate, 110) FROM AxTransaction JOIN AxGLDetail ON gldTransaction = tKey JOIN AxBankEntry ON gldBankEntry = beKey WHERE tKey = [Transaction Key] ORDER BY beDate

Thank you in advance!

/* Courtesy MJ Brennan posted to */
/* Transaction Base */
SELECT TOP (100) PERCENT MAX(CONVERT(VARCHAR, dbo.AxBankEntry.beDate, 110)) AS BE_Date, dbo.AxTransaction.tKey
FROM dbo.AxTransaction INNER JOIN
dbo.AxGLDetail ON dbo.AxGLDetail.gldTransaction = dbo.AxTransaction.tKey INNER JOIN
dbo.AxBankEntry ON dbo.AxGLDetail.gldBankEntry = dbo.AxBankEntry.beKey
GROUP BY dbo.AxTransaction.tKey
HAVING (dbo.AxTransaction.tKey = [Transaction Key] )