Dcount within SQL Select statement

[ChrisLayfield]

I have a reference database, where the references need to be screened by two independent reviewers. I need the references to load as the rowsource of a combobox only when the user has not already reviewed the reference or the reference has not been reviewed twice already. I thought I could include a DCount statement within the SQL Select statement, but I can't seem to get it to work out.

    Me.cboTitle.RowSource = "SELECT tblStudyReferences.[ID], tblStudyReferences.[StudyTitle], " & _
        "tblStudyReferences.[Abstract], tblScreening.[ArticleID], tblScreening.[ReviewerID] " & _
        "FROM tblStudyReferences, tblScreening " & _
        "WHERE tblScreening.[ReviewerID] <> " & gsUserID & " AND " & _
        "DCOUNT('tblScreening.[ArticleID]', 'qryReferences', 'tblScreening.[ArticleID]' = 'tblStudyReferences.[ID]') <= 2 " & _
        "GROUP BY tblStudyReferences.[ID], tblStudyReferences.[StudyTitle], tblStudyReferences.[Abstract], " & _
        "tblScreening.[ArticleID], tblScreening.[ReviewerID] " & _
        "ORDER BY tblStudyReferences.[StudyTitle];"

Thanks for the help,
Chris

 

[boblarson]

This should get you there:

"DCOUNT('*', 'qryReferences', 'tblScreening.[ArticleID] = tblStudyReferences.[ID]) < 2' " & _


You don't want the quotes in the middle of the criteria because you are comparing fields. Also, you want < 2 and not <=2 as that would also count if there have already been 2 reviews.

 

[ChrisLayfield]

Bob - Thanks, that indeed does work, but unfortunately doesn't solve my problem. It seems I have a flaw in my logic. In this equation, tblStudyReferences.ID will always equal tblScreening.Article ID which means nothing will show up once two references are screened. :banghead:

What I need to determine is a means to count when a single reference has been screened by 2 users. I set tblScreening up so the UserID and ArticleID are the primary key, is there a way to count unique based on that?

 

[eblieb]

Bob - Thanks, that indeed does work, but unfortunately doesn't solve my problem. It seems I have a flaw in my logic. In this equation, tblStudyReferences.ID will always equal tblScreening.Article ID which means nothing will show up once two references are screened. :banghead:

What I need to determine is a means to count when a single reference has been screened by 2 users. I set tblScreening up so the UserID and ArticleID are the primary key, is there a way to count unique based on that?

If you are trying to Count Distinct... You have to do it the long way since Access (atleast access 2003) doesn't support count distinct.

The way I have done it is

 (Select count(1) FROM (Select Distinct t2.exp1, t2.exp2, t2.exp3 
from tableSomething as t2) as t1 
Where t1.exp1 = t.exp1 AND t1.exp2 = t.exp2) 
from tableSomething as t

 

[ChrisLayfield]

I thought I had programmed as you indicated, but I am getting a syntax error (missing operator) in my query expression...any idea?

    Me.cboTitle.RowSource = "SELECT ID, StudyTitle, Abstract, ArticleID, ReviewerID FROM qryReferences " & _
        "WHERE 1 = (SELECT Count(*) FROM (SELECT DISTINCT ReviewerID, ArticleID FROM tblScreening as t2) as t1 " & _
        "WHERE t1.ID = t.ID and t1.ID = t2.ArticleID) FROM tblStudyReferences as t " & _
        "AND ReviewerID <> " & gsUserID & " " & _
        "ORDER BY ID, Abstract, ArticleID, ReviewerID " & _
        "GROUP BY StudyTitle;"

 

[boblarson]

This would be my guess:

[FONT=Calibri]        "WHERE 1 = [/FONT]
[FONT=Calibri]

[/FONT]

Not sure what you are intending there.

 

[ChrisLayfield]

I wish that would have fixed it Bob. I've uploaded the db in case you had time to look, the code is within the Form_frmScreeningTool in Private Sub Form_Load()

 

[eblieb]

der.. nevermind I missed an operator...


Easiest way to debug a sql statement is to go into your database and design a new query...

Then just place your query in that.. It debugs it a lot better than VBA

SELECT ID, StudyTitle, Abstract, ArticleID, ReviewerID FROM qryReferences WHERE 1 = (SELECT Count(*) FROM (SELECT DISTINCT ReviewerID, ArticleID FROM tblScreening as t2) as t1 WHERE t1.ID = t.ID and t1.ID = t2.ArticleID) FROM tblStudyReferences as t AND ReviewerID <> " & gsUserID & " ORDER BY ID, Abstract, ArticleID, ReviewerID GROUP BY StudyTitle;

 

[ChrisLayfield]

OK, so I can't figure out that many selects and keep it straight so I took a new course. I separated out some of the decisions, but now am stuck on this statement:

SELECT tblScreening.ArticleID, Count(tblScreening.ReviewerID) AS NumberofDups, First(tblScreening.ReviewerID) AS [ReviewerID Field], tblStudyReferences.Abstract, tblStudyReferences.StudyTitle FROM tblStudyReferences INNER JOIN tblScreening ON tblStudyReferences.ID = tblScreening.ArticleID HAVING (((Count([tblScreening].[ReviewerID]))<=1)) ORDER BY ArticleID;

And am getting "You tried to execute a query that does not include the specified expression 'ArticleID' as part of an aggregrate function." error...any thoughts

 

[ChrisLayfield]

OK, I managed to get this figured out, though I had to do something a bit different than I had first set out. I created a query that identified the articles which were only screened 1 time (modified the Access provided duplicate query) then used that as the record source for a second query to identify then the remaining records were not screeend by the user, of course with a check to make sure any records would be in available given the specific user.

        With Forms!frmScreeningTool!cboTitle
        Set rsNotUser = db.OpenRecordset("SELECT qrySingleScreen.ArticleID, qrySingleScreen.[ReviewerID Field], " & _
            "tblStudyReferences.ID, tblStudyReferences.Abstract, tblStudyReferences.StudyTitle " & _
            "FROM qrySingleScreen, tblStudyReferences WHERE (((tblStudyReferences.ID)=[qrySingleScreen].[ArticleID]));")
            If rsNotUser.EOF = True And rsNotUser.BOF = True Then
                MsgBox "No single screened records available", vbOK
                .RowSource = "SELECT tblStudyReferences.ID, tblStudyReferences.Abstract, tblStudyReferences.StudyTitle " & _
                "FROM tblStudyReferences LEFT JOIN tblScreening ON tblStudyReferences.ID = tblScreening.ArticleID " & _
                "WHERE (((tblScreening.ArticleID) Is Null));"
                .BoundColumn = 1
                .ColumnCount = 3
                .ColumnWidths = "0;0;2 in"
                Forms!frmScreeningTool.optUnscreened = True
            Else
                .RowSource = "SELECT qrySingleScreen.ArticleID, qrySingleScreen.[ReviewerID Field], " & _
                    "tblStudyReferences.ID, tblStudyReferences.Abstract, tblStudyReferences.StudyTitle " & _
                    "FROM qrySingleScreen, tblStudyReferences " & _
                    "WHERE (((tblStudyReferences.ID)=[qrySingleScreen].[ArticleID]));"
                .BoundColumn = 1
                .ColumnCount = 5
                .ColumnWidths = "0;0;0;0;2 in"
            End If
        End With