Solved

Counting records that meet multiple string and date requirements across linked tables.

Posted on 2011-03-11
6
369 Views
Last Modified: 2012-05-11
I have three, inner joined tables in a single database from which I would like to count records from a single column that meet string and date conditions. I have attached an image of the linked database tables with which I am working.

I assume, given the size of the database, that SQL Select statements would be the most effective way to go about this, but I have not been successful in writing a working query. I am using a MDB database connected as an ODBC data source.

I have attempted variations of the following, which appears to contain multiple syntax errors.

(
    SELECT COUNT (Gf.Gf_Amount)
    FROM Gf
    JOIN GfApls_1 ON Gf.GfApls_1_LINK=GfApls_1.GfApls_1_LINK
    JOIN GfApls_1Ap ON GfApls_1.GfApls_1Ap_LINK=GfApls_1Ap.GfApls_1Ap_LINK
    WHERE Gf.Gf_Date>{?FirstStart} AND Gf.Gf_Date<{?FirstEnd} AND GfApls_1Ap.GfApls_1Ap_Appeal_category='Print_AL'
)

Am I at all on the right track? Any help would be much appreciated?
db-link-view.jpg
0
Comment
Question by:thaumaturgist
  • 2
  • 2
  • 2
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35113409
Where are you adding the SQL?

You could do this in a formula as

If {Gf.Gf_Date} >{?FirstStart} AND {Gf.Gf_Date} <{?FirstEnd} AND {GfApls_1Ap.GfApls_1Ap_Appeal_category}='Print_AL'  then
    1
Else
    0

You could then use a summary on the field to sum the 1's and get the count.

mlmcc
0
 

Author Comment

by:thaumaturgist
ID: 35113694
I added the SQL in the SQL Expression Editor in the Formula Workshop.

Your formula gives me a consistent result, which is definitely a step forward for me. However, a count of this formula yields a number approximately eighty-five times higher than expected. Can you think of any reason why this might be the case?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35113891
Mine should just be a regular formula and dragged to the report.

Do you have groups on the report?

Are you trying to count the number in a group?

mlmcc
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 34

Expert Comment

by:James0628
ID: 35115058
FWIW, your SQL Expression looks OK for the most part, but I'm guessing that there's a problem with {?FirstStart} and {?FirstEnd}.  If those are date or datetime parameters, I'm not sure exactly what CR is going to plug in there, but it would probably be something like 03/12/2011 (for a date parameter), so you'd end up with SQL like

Gf.Gf_Date>03/05/2011 AND Gf.Gf_Date<03/12/2011

 which would probably give you errors, unless the db happens to recognize date values.  You may be able to handle that by simply putting the parameter names in single quotes:

Gf.Gf_Date>'{?FirstStart}' AND Gf.Gf_Date<'{?FirstEnd}'


 If you can get the value that you need from the report data using a formula like the one that mlmcc posted, that would probably be more efficient than having a SQL Expression go out and read the data again, but it still might be useful at this point, just to compare the results that you get.

 As for your inflated count, assuming that it's not just because this is a grand total and you're looking for a group sub-total, then the obvious guess is that you're looking for a count of the Gf records, but there are multiple records in GfApls_1 and/or GfApls_1Ap for some Gf records, so you get an inflated count.  If I'm right, the SQL Expression would also return inflated results.

 If I'm right, there are ways to get around that and only count each Gf record once.

 James
0
 

Author Comment

by:thaumaturgist
ID: 35141973
I was able to get Mlmcc's formula to display the correct results. There appears to have been something wrong with the data source. In the process of creating a new MDB to work from, I introduced new errors that were finally resolved with left outer joins and the inclusion of the dates with >= <=.

Clearly, I still have much to learn regarding the procedure of report creation, but I am much further along now thanks to both of you.

James, I think you are correct about the formatting of the date parameters, but my select query fails with syntax errors even with the date where clauses removed. In any case, as formulas seem to be the recommended method here, I think I will chalk it up to an unfamiliarity with CR's handling of SQL Expression Fields and move on for the time being.
0
 
LVL 34

Expert Comment

by:James0628
ID: 35146332
SQL Expressions definitely have their uses, but the syntax can be tricky.  Apart from the parameters, I don't see anything wrong with yours, but I'm not really an expert on them.  I don't use them, because almost all of my reports use stored procedures, and you can't use SQL Expressions with stored procedures.

 FWIW, for a seemingly simple operation like this, the formula is probably more efficient, because CR is just generating the result from the data that the report is already reading, while a SQL Expression would mean a separate query to the db to get the count (and possibly a separate query for each group).

 Anyway, I'm glad you found a solution.

 James
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Builder on BOXIR2 7 63
Pagination Difference  in crystal report 7 50
Crystal reports vb.net 2 40
Crystal Reports 2013 25 34
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question