Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    13

    Unanswered: General question about table relationships

    hey guys, i think i have a gap of understanding when it comes to relationships.

    i have 2 tables and i am working with access2003!

    Salesmen: ID first-name last-name
    ----------------------------------------------
    123 Test Person
    999 Another Tester
    AND


    Sales: Invoice-Nr Date Salesperson Customer SalesPrice Commission
    -------------------------------------------------------------------------
    111111 xx 999 bla 100 3
    222222 xx 123 vbv 200 6


    I created a relationship with access that relates Salesperson.ID to Sales.Salesperson

    Now I want to print a report on Salesperson 999 "Test Person" with following attributes: Sales.Invoice-Nr , Salesmen.first-name, Salesmen.last-name, Sales.Commission !

    How does the SELECT statement has to look like? Whenever i tried sth the report printed crap. thx

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show one of the queries you tried

    thread moved to access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Posts
    13
    SELECT tbl_sales.[invoice-Nr], tbl_Salesmen.[First-Name], tbl_Salesmen.[Last-Name], tbl_sales.[Commission]
    FROM tbl_sales, tbl_Salesmen
    WHERE (((tbl_Salesmen.ID = tbl_sales.Salesperson))));

    RESULT:
    ALL salesperson get printed. not just 999

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT tbl_sales.[invoice-Nr], tbl_Salesmen.[First-Name], tbl_Salesmen.[Last-Name], tbl_sales.[Commission]
    FROM tbl_Salesmen INNER JOIN tbl_sales
    ON tbl_Salesmen.ID = tbl_sales.Salesperson
    WHERE tbl_Salesmen.ID = 999
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2007
    Posts
    13
    thx that worked... never heard of inner join....thank you very much

Posting Permissions

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