Share via

Help with understanding "SELECT DISTINCTROW"

Anonymous
2013-04-10T14:57:37+00:00

I have inherited a database at my job and am trying to enter data into a table for the new Fiscal Year.  This table is called 'Budget Materials'.  There is a column (Materials) which uses the "SELECT DISTINCROW" statement.  This column is linked to the 'Materials' table and what I was hoping would happen was that when clicking on the down arrow, I would get the entire materials list to choose from instead of having to type in the material for each record.  But that does not happen.

Here is the statement:

SELECT DISTINCTROW Material.Name, Material.Name, Material.Supplier, Material.[Unit/cost] FROM Material INNER JOIN [Fiscal Year] ON Material.Year_ID = [Fiscal Year].Year_ID WHERE (((Material.Year_ID)=[Forms]![Budget Materials]![Text15]))<br> ORDER BY Material.Name;<br><br><br> <br><br><br>Can someone tell me if I am expecting it to do something that it can't do?  Thanks!
Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

3 answers

Sort by: Most helpful
  1. Anonymous
    2013-04-10T18:46:54+00:00

    Patti

    You could join my Yahoo group (http://tech.groups.yahoo.com/group/MS_Access_Professionals/) and upload a zipped sample. Then post a question asking our members to take a look at the issue. Many of the members would be happy to help. And it's more private than here.

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2013-04-10T16:46:13+00:00

    Bill - thanks for responding to my question.  I tried changing the statement to reflect your suggestion but it didn't make a difference in the outcome.  I think this database is way to complicated for my beginner skills!  Is there any place which I could have someone 'analyze' the database and let me know what it is supposed to be doing?  Maybe I'm expecting a result that was not originally intended.

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2013-04-10T16:16:40+00:00

    Patti

    DISTINCTROW is like DISTINCT in that it pulls unique records when multiple tables are used in a query. Rob Farley gives a good explanation here.

    I don't see a need for the Fiscal Year table here since you are not selecting any fields nor are you using any of its fields in the WHERE clause.

    I would do this:

    SELECT DISTINCT Material.YourPrimaryKeyGoesHere

    , Material.Name

    , Material.Supplier

    , Material.[Unit/cost]

    FROM            Material

    WHERE           Material.Year_ID =[Forms]![Budget Materials]![Text15]

    ORDER BY        Material.Name;

    Was this answer helpful?

    0 comments No comments