Help with understanding "SELECT DISTINCTROW"

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

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;