Solved

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

Posted on 2011-03-11
6
370 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 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
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 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