Hi Experts,
I'm trying out SQL Expression Fields in Crystal Reports 11 but I'm not having any luck.
The report is showing records from a table called Candidates. There is also table Education. One record in Candidates has many records in Education.
The tables link on Candidates.CandidateID = Education.CandidateID
I simply want to show in the report, for each record in Candidates table, how many education records are there. The SQL Query would look like this:
SELECT COUNT(Education.CandidateI
FROM Education
WHERE Education.CandidateID = X
Where X is the CandidateID of the record in the details section. I just cannot get this query converted to syntax accepted by the SQL Expression Field in Crystal! I keep getting the same error message in Crystal when saving that states there is incorrect syntax near the keyword select.
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The syntax depends on a combination of two things:
1) Your database
2) Your method of connectivity to your databaes (ODBC Vs. OLEDB Vs. JDBC etc...)
Here's an example of Syntax I would use with SQL Server via ODBC (the parentheses are required, btw):
(
SELECT
COUNT(CandidateID)
FROM
EDUCATION
WHERE
CandidateID = "Candidates"."CandidatesID
)
There's no reason to alias the field since the SQL Expression field name is what will be used on the report (just like a formula).
Also, notice the difference between the fields in the WHERE clause. CandidateID comes from the Education table. There's no reason to preface it with the table, since only one table is used. "Candidates"."CandidatesID
I have a .ppt presentation about SQL Expression fields on the files page of my website:
http://acuityreporting.com
~Kurt
Business Accounts
Answer for Membership
by: mlmccPosted on 2007-05-24 at 10:50:06ID: 19151278
Do you have the table Education linked in the main report?
Can you group on Candidates.CandidateID and use a summary (count for Education.CandidateID) to get the count you want?
WHat have you tried for X
SELECT COUNT(Education.CandidateI
FROM Education
WHERE Education.CandidateID = {Candidates.CandidateID}
mlmcc