Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Query Relationship

    Hi all I have a question, In my view I have two tables linked together for the users, my issue is that I want them to see one record so they can get the proper total amount of records. The main table is reports and the child table is subjects. How do I make it so that they see one record from the reports table, because if there is more then one subject linked to a reports number which is the primary key, they will see more then one report number. Does that make sense??

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    So you want to display the header information with a summary of the detail information? Sounds like you will be looking for aggregate functions and the group by clause. Something like

    Code:
    select o.*, sum(d.value)
    from orders o join details d on o.id = d.orderid
    group by (all the fields of the orders table)

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Heres my query

    Code:
    SELECT     TOP 100 PERCENT dbo.Report.[Incident Report No], dbo.Report.Date, dbo.Report.TypeOfIncident, dbo.Subjects.SubjFirstName, 
                 dbo.Subjects.SubjLastName
    FROM         dbo.Report INNER JOIN
                 dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
    WHERE     (dbo.Report.Date BETWEEN CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-12-31 00:00:00', 102))
    it will show me two of the same ID numbers because of the subjects table which has two entries

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    See the part where he said "GROUP BY" and you didn't? You might want to start there...

    SideNote: Is there a reason you're using TOP 100%? I generally see that as an order by hack, but you don't have an order by clause...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    sorry

    Missed that part sorry

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Hows this??

    Ok hows this
    Code:
    SELECT     TOP 100 PERCENT dbo.Report.[Incident Report No], MIN(dbo.Report.Date) AS Date, MIN(dbo.Report.TypeOfIncident) AS Type, 
                          MIN(dbo.Subjects.SubjLastName) AS [Last Name], MIN(dbo.Subjects.SubjFirstName) AS [First Name]
    FROM         dbo.Report LEFT OUTER JOIN
                          dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
    GROUP BY dbo.Report.[Incident Report No]
    HAVING      (MIN(dbo.Report.Date) BETWEEN CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-12-31 00:00:00', 102))

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Its Ma Birthday

    Oh Yeah It Works Its Ma Birthday Shake Ma Booty!!!!!!!!!!!!!!!!!!!!!

  8. #8
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by Teddy
    Is there a reason you're using TOP 100%?
    He's probably using a (graphical) query builder like the one in EM. Those things always put these unuseful things in there

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    often the top 100 percent thing is a hack so that you can use "order by" in a view definition. although even if you use it there, results aren't guaranteed to come back ordered.

  10. #10
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Mmmm, didn't know that one. Seems logical it should not work. The ORDER BY is used to get the TOP x%, how they will be delivered to you is unknown.

    Sounds like something a developer I know would do. He used a clustered index so he though he would get his data always in the right order without having to use ORDER BY. Ofcourse he was sadly mistaken

Posting Permissions

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