Unanswered: get only 3 detail records for a report?????
This is my first post. Thank you for any help you can provide.
I have an existing report that is based on a Make query. All of the information that prints is available on the single record in this table. Very easy and works great. It's a report about an ITEM in the Item Master table. My user has asked me to include the first 3 "colors" on the report. This info is stored in the COLOR table which is linked by ITEMID. A given ITEM might have only one color or it might have 20 or more. But all I need to show is the first 3 if they are there. I can't for the life of me figure out how to do this. Do I modify the Make Query to somehow incorporate this info at that time? Is this a subquery within the original query? Or do I somehow add a second make query and somehow link the two tables together for the report? Or is there an event within the Report that could run a query to get this info? I've tried playing around with the TOP predicate but that looks like I have to drive things from the COLOR table to get that to work. I'm not that good with SQL to figure out how to construct this. If someone could just point me in the right direction and/or approach that would be a big help. Thanks
OH MY GOD that is a huge time saver. I didn't know you could do that. Not only have you solved my problem but this alias thing is a big help as well. Thank you so very much.
I just got finished adding in all of the additional data that I'll need for the report and this looks like it's going to work great. I have been struggling with this issue for some time now. This forum is great. HAIL dbforums! May your hits be many and your membership sore high.
And thank you pootle flump. It's people like you that are willing to help others that make the internet what it was dreamed to be.
It may not actually be the time saver you think. You only need to qualify your fields where the names are common between 2+ tables (e.g. in the example I provided you would only need to qualify the linked fields). Access tends to qualify EVERYTHING if you use the designer but it doesn't have to. But - I am waffling - not all that important...
EDIT - I fully qualified in the example to make it explicit what I was trying to do