I have a problem where I want to use the DISTINCT selection criteria, but only on one field in my query. Here is the query:

This query is being used as the Recordsource for a form which has a number of subforms attached to it. I would like to use the field tblCompany.strCompany as the distinct selection criteria. I have tried many things in order to achieve my goal:

1) Take out tblAccount.strAccountNum of the query (but when I do, and run the query, access pops up with a window when I run a procedure on the form the query is being executed with asking for an "account number" as if it were a variable. I can get the results I want, but not without the popup window)

2) Use a subquery to select a distinct company (this did not work either)

3) Place parenthesis around tblCompany.strCompany (this was unsuccessful)

4) Use a GROUP BY statement (I had to restructure the query to do this, and it would not allow me to use the query without an error occurring)

This is all I could think of to solve my problem. I appreaciate any hints on how to solve my problem.

Thanks.

- Joe

You could try something like this:

SELECT distinctCompanies.strCompany, tblInvoices.*, tblNotices.*, tblAccount.strAccountNum, tblAccount.strMembership, tblAccount.strAgent

FROM (SELECT DISTINCT anmCompanyID, strCompany FROM tblCompany) as distinctCompanies, tblAccount, tblInvoices, tblNotices

WHERE tblAccount.numCompanyID=distinctCompanies.anmCompanyID AND tblInvoices.numCompanyID=tblAccount.numCompanyID AND tblAccount.numCompanyID=tblNotices.numCompanyID

ORDER BY distinctCompanies.strCompany


That was just air coded...but it sould(?) work! Give it a shot (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

I found the solution to my problem. I am still able to use DISTINCT, but had to change the Recordsource of each of the subforms.

Thanks for the help.

- Joe