Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2001
    Location
    Lewisville, NC
    Posts
    7

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi DashDanger

    Welcome to the forum.

    TOP 3 sounds like your solution to me too. I would have thought something like:

    Code:
    SELECT TOP 3 I.Col1, I.Col2, I.ColEtc, C.Colour
    FROM MyItemT I LEFT OUTER JOIN MyColourT C ON 
    I.ItemID = C.ItemID
    ??? no ???
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2001
    Location
    Lewisville, NC
    Posts
    7
    WOW that sort of worked. Although I don't have a clue why. I have a couple of questions.

    your code:

    "SELECT TOP 3 I.Col1, I.Col2, I.ColEtc, C.Colour
    FROM MyItemT I LEFT OUTER JOIN MyColourT C ON
    I.ItemID = C.ItemID"

    in the FROM part, after MyItemT (which is my ITEM table) what is the I just before LEFT. Same question on the C just after MyColourT (my color table)?

    I tested this:

    SELECT TOP 3 tblItem.FinishedName, tblCoreColor.CoreColorName
    FROM tblItem LEFT JOIN tblCoreColor ON tblItem.ItemID = tblCoreColor.fkItemID

    this isn't all of the data I need of course but I can see where I can build on this.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DashDanger
    I have a couple of questions.

    your code:

    "SELECT TOP 3 I.Col1, I.Col2, I.ColEtc, C.Colour
    FROM MyItemT I LEFT OUTER JOIN MyColourT C ON
    I.ItemID = C.ItemID"

    in the FROM part, after MyItemT (which is my ITEM table) what is the I just before LEFT. Same question on the C just after MyColourT (my color table)?
    Glad you are on the right road.

    I and C are aliases. I was a bit lazy - I could have written more clearly as:
    Code:
    SELECT TOP 3 I.Col1, I.Col2, I.ColEtc, C.Colour
    FROM MyItemT AS I LEFT OUTER JOIN MyColourT AS C ON 
    I.ItemID = C.ItemID
    All an alias is saying to Access is "ok - i will refer to MyItemT AS I in the query. Treat I as MyItemT.". Hence I.Col1 instead of MyItemT.Col1. The same, of course, applies to the C.

    Reason - it saves keystrokes (I is quicker to type than MyItemT) and makes updating queries after scema changes (rare but sometimes required) a teeny bit easier to implement.

    Get yourself some resources on SQL - these will be invaluable. Here is one (part 2 of 3) and it refers to the other two parts.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2001
    Location
    Lewisville, NC
    Posts
    7
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My pleasure DD

    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •