Trying to retreive Unique Values in a report. (1 Viewer)

[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" field comes from a separate (related) table from the Plant Infomation Plant There may be more than one photopath for each plant. I really only want and need to have just 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?

PS. Can we do this without using VB?

Thanks Mike

 

[Hayley Baxter]

Just to clarify what you are asking do you want a filter where you can search which plant it is firstly then search for the 1 or more related items for the first selection?

Let me know If I have picked you up wrong.

Hayley

 

[plantfinder]

Thanks Haley

No. I am looking to retreive records that are not duplicated because one Plant Record has more than one Photo Record related to it. The two tables are related and are part of the same query. However, I need at least one instance of the plant record to show up even though there may be several other photo records that are associated to it. I was thinking about whether and how I could determine which of the records in the photo table to bring up (not that it will matter which one does but so that at least one does show up in the results).

i.e one plant record may have 5 photo records associates so that when I run the report I get 5 entries for the same plant record because the photo record has to show up 5 times.

I hope I have clarified, I know it's confusing.

Mike

 

[Hayley Baxter]

Ok I think I understand what you are after. Right click the properties of the plant name that is appearing multiple times in the design of the report and go to the format tab - change the hide duplicates to yes. This will then give you one plant name and show 5 related records.

Hope that is what you are after.
Hay

 

[plantfinder]

Thanks again Hay,
I think we're getting closer but what I want to do is to just have just 1 of those 5 related records showing so that even though there are 5 records (photos) associated with that particular plant i only want one (any one) to show up.


Mike

 

[neileg]

I assume your picture table has an ID number for the pictures that you use to link to the plant table. Create a query that selects all of the data that you want and includes the key for the pictures eg PicID. Set the totals line for the plant as Group by, and for PicID to MAX or MIN or whatever you want. This will ensure that only one picture is selected.
Then create another query that links this first query to the actual images in the picture table.
Voila!

 

[plantfinder]

Thanks Neil,
I was able to solve that problem as far as the query was concerned with a similar answer to yours. However, I am now running into a further problem when it comes to the report itself. Please see post entitled "Report based on Totals Query giving Problems"

Thanks

Mike