Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unhappy Unanswered: DISTINCT Clause Failed

    Hi All,
    I have a big and strange problem.
    I have two table (Orders and Details) in my Access DataBase with a relation One to many on a NumeroOrdineAcquisto Field (One Order, Many Details)
    I use this String as a Recordsource of a form:

    "SELECT Orders.*, Details.*, * FROM Orders LEFT JOIN Details ON Orders.NumeroOrdineAcquisto=Details.NumeroOrdineAc quisto WHERE Orders.PM <> ' '"

    In the output form I put only Fields of Orders Table but I need to select also Details because I can use a filter on Details Fields.
    So, my problem is:
    If I have some Orders with 2 or plus line details, in my form I found 2 (or plus) Equal Rows!!
    For Example:

    Orders Table
    NR Nome Cognome
    1 Davide Galloni

    Details Table
    Nr Documento importo
    1 Invoice 100
    1 Invoice 150


    When I Open my Order Header Form I Found:

    NR Nome Cognome
    1 Davide Galloni
    1 Davide Galloni

    I've tried to Use distinct clause in my select but it doesn't work or I don't use it correctly.

    Have you Idea How can solve this problem???
    Very Thanks for your help.
    Davide Galloni from Italy

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You should set up a subform to display the details.
    Link the main form and subform using the NumeroOrdineAcquisto field
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Distinct will return rows where all of the results from the recordset are the same. ie applying a 'Distinct' clause on a table that contains the data:

    1 Paul Hardy 100
    1 Paul Hardy 100

    Will return :

    1 Paul Hardy 100

    Because ALL of the data is the same. Whereas a table that contains the data:

    1 Paul Hardy 100
    1 Paul Hardy 100
    1 Paul Hardy 150

    Will return:

    1 Paul Hardy 100
    1 Paul Hardy 150

    Because the data in the 3rd record is different to the first 2.


    Probably the quickest way around this for you is to use the 'First' function. You can access this by pressing the 'Totals' icon in the query window. The default value is 'Group By', which will render the need for using 'Distinct' redundant, just change 'Group By' to 'First'

    Should work

Posting Permissions

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