Retreiving single records in query/report

[plantfinder]

I have a report based on a query that includes 3 tables. "Plant" is one table and "Photos" are another. In some cases, there can be multiple photos per plant record.In the query that runs this report I have some records that are duplicated in that there may in some cases be mutiple photos for the same record (which is basically of a plant). The photos path comes from a separate (related) table from the Plant information. There may be more than one photopath for each plant. I really only want and need to have one of the records that relates to a specific plant ID#s show in the report with just one of the photos. It could be the first photo listed for that plant or any one. Is there some way I can enter some criteria in the query to keep only one record i.e 1 (unique) plant ID # showing up in the results and to pull out one photopath record that relates to it? I'm guessing this may need some clarification. Would "Dfirst" do anything? I'm guessing or is there a better way?

Thanks
Mike

 

[PWise]

SELECT Cites.id, Cites.city, First(Reps.name) AS FirstOfname, First(Reps.address) AS FirstOfaddress
FROM Cites LEFT JOIN Reps ON Cites.id = Reps.cityid
GROUP BY Cites.id, Cites.city;
this is the sql that i use to find the first rep in each city
hope it helps

 

[RickSpr]

In terms of the query grid, you click the toolbar button to make it a Totals query, then set all the fields from the Plant table to Group By, and set all the fields from the Photos table to First.

BTW, NEVER use DFirst(), DSum(), DCount(), or any other domain aggregate function in a query. They are very inefficient when used that way. When you think you need one of those, it's a sure bet that what you really need is a Totals query with First, Sum, Count, etc. for those columns. The domain aggregate functions are only meant for use in code, on one (or at most a few) records at a time. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[plantfinder]

Rick,

That was beautiful

Thanks

Mike

 

[plantfinder]

Sorry Rick,
I spoke to soon.

After making the changes you suggested, I went to Datasheet view and to my releif I was getting exactly the records I wanted. However, when I went to run the report that was based on the same query, I was being prompted for to enter parameters for the two fields in the photo table that I changed the total to "First". Not sure where to go from here.

Not sure why it works in the query but not in the report. Mybe it has something to do with the grouping?

Thanks in advance for further help.

Mike