Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-11
6
Medium Priority
?
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 101

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 101

Accepted Solution

by:
mlmcc earned 2000 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 35

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 35

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

636 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