• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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

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
thaumaturgist
Asked:
thaumaturgist
  • 2
  • 2
  • 2
1 Solution
 
mlmccCommented:
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
 
thaumaturgistAuthor Commented:
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
 
mlmccCommented:
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
James0628Commented:
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
 
thaumaturgistAuthor Commented:
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
 
James0628Commented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now