Hi all :-)
In 3.71 I need to create a report that combines the GL5 and GL8 files together. Ultimately I want it so that each Transaction record of the GL5 is matched up with the coresponding GL Account and Year record in the GL8. In the visual linking expert, all I can chose to link on is GL account, I cannot link on the year because I cannot setup a link between the YEAR value in the GL8 to the year that is nested in the TRANSACTION DATE field of the GL5. I know I can do a select within crystal(after the join has been created and run) to only select the records where the GL8 year matches the
(year(GL5_DetailPosting."TransactionDate"); Howeve
The problem is that my GL5 records are repeated for each fiscal year that is out there because I cannot do my table join based on the YEAR also. If I Join on only the GL Account, then my detail record will join to EVERY Fiscal year record, not just the fiscal year that transaction is in. I was trying to modify the SQL statement that Crystal creates so that I can have the initial table join match up transaction Date Year to the Year stored in the GL8. The below SQL syntax in my crystal report does NOT work because I dont have the syntax correct on pulling the year out of the GL5_DetailPosting."TransactionDate".
The questions I am hoping someone knows (answering any ONE of the three will help)
1. Has anyone been able to come up with a way in crystal reports to Join the GL5 and GL8 records together and do your table link so the GL5 record only matches up to its associated GL8 fiscal year record? If so, how did you do it so it has decent speed?
2. Does anyone know the proper syntax in SQL that crystal uses to match the Year from the TransactionDate field to the FISCALYR field.
3. Does anyone know where there is some documentation that shows the SQL syntax that crystal reports uses. I have a SQL book, and have looked in microsoft access, and have looked around the web. I see different variations of syntax, but have not found a variation of syntax that works for the SQL that crystal uses.
Thank-you to anyone for any information 
SELECT
GL5_DetailPosting."AccountNumber", GL5_DetailPosting."TransactionDate",
GL8_BudgetAndHistory."FiscalYr", GL8_BudgetAndHistory."RecordTypeActualBudgets"
FROM
"GL5_DetailPosting" GL5_DetailPosting,
"GL8_BudgetAndHistory" GL8_BudgetAndHistory
WHERE
GL5_DetailPosting."AccountNumber" = GL8_BudgetAndHistory."AccountNumber" AND
GL8_BudgetAndHistory."RecordTypeActualBudgets" = 'A' AND
datepart(GL5_DetailPosting."TransactionDate","yyyy
