Solved

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

Posted on 2011-03-11
6
368 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now