SQL SELECT not recognizing table

[ChrisLayfield]

I am working on a project where two independent reviews screen literature (study) within a form. I created a combobox to allow the user to select the study, but can only select a study which they have not already reviewed and where the study was not already reviewed by two other people. For some reason, this is no longer pulling the data from the tables and the combobox is just showing empty columns and a box appears to request data entry for each field.

If rsEmptyTable.EOF = True And rsEmptyTable.BOF = True Then
    Me.cboTitle.RowSourceType = "Table/Query"
    Me.cboTitle.RowSource = "SELECT tblReferences.[ID], tblReferences.[StudyTitle], tblReferences.[Abstract] " & _
        "FROM tblReferences;"
    Me.cboTitle.BoundColumn = 1
    Me.cboTitle.ColumnCount = 3
    Me.cboTitle.ColumnWidths = "0;2 in;0"
Else
    Me.cboTitle.RowSourceType = "Table/Query"
    Me.cboTitle.RowSource = "SELECT tblReferences.[ID], tblReferences.[StudyTitle], tblReferences.[Abstract], " & _
        "tblScreening.[ArticleID], tblScreening.[ReviewerID], Count(tblScreening.[ArticleID]) as Tally " & _
        "FROM tblReferences as p " & _
        "LEFT JOIN tblScreening as i ON " & _
        "p.[ID] = i.[ArticleID] " & _
        "WHERE tblScreening.[ReviewerID] <> " & gsUserID & " AND " & _
        "Tally < 2;"
    Me.cboTitle.BoundColumn = 1
    Me.cboTitle.ColumnCount = 6
    Me.cboTitle.ColumnWidths = "0;2 in;0;0;0"
End If

 

[pbaldy]

Because you've aliased the table name, you have to use the alias in the SELECT clause.

 

[ChrisLayfield]

I updated the SELECT statment and replaced tblReferences with p and tblScreening with i and now I get a "You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function." I have no idea what that eve means:banghead:

 

[pbaldy]

The Count function is making the query a totals query. In a totals query, each field in the SELECT clause must either be in a GROUP BY clause or be part of an aggregate function (Count, Sum, etc). A very simple totals query looks like:

SELECT ID, Sum(Amount) AS TotalAmount
FROM TableName
GROUP BY ID

Note because the ID field isn't in an aggregate function, it's in the GROUP BY clause. You might try building your query in the design editor and then copy the syntax.