Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116

    Unanswered: One to many (duplicates)

    I have a query with two tables that ends up with duplicates. I'm not sure how to explain, but one table will have multiple types of service for each account, but the other table only has one line for each account. How can I get the data to display all the data but only leave the duplicate lines with blanks on those fields? My tables are too big to attach, but hopefully someone will understand and be able to help.

    Thanks . . . texasalynn

  2. #2
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    OK - I tried to pull a sample.

    texasalynn
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    In your query, change to SQL view and change it
    from
    Select Mid(.....

    to

    Select DISTINCT Mid(...

    That will eliminate all non-unique rows from the query.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: One to many (duplicates)

    could not read your mdb file (all i have is access 97)

    however, from the description of your problem, you want to display

    svc23 acct1
    svc31 acct1
    svc37 acct2
    svc55 acct2
    svc72 acct2
    svc25 acct3
    svc51 acct3

    like this

    svc23 acct1
    svc31
    svc37 acct2
    svc55
    svc72
    svc25 acct3
    svc51

    you cannot do this with just sql in a query

    in sql server, you would use a cursor and next/prev logic

    perhaps access has some way to mimic cursors and execute some logic on the result set? i'm sorry, i don't know, but i do know you can't do this (easily) with sql

    rudy
    http://r937.com/

  5. #5
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Well you could do it in sql but it would be pretty preverse stuff

    first work out the first account for each service:

    "qryFirstAc"
    SELECT Min(tblMyTable.Account) AS MinOfAccount, tblMyTable.Service
    FROM tblMyTable
    GROUP BY tblMyTable.Service;

    Then LEFT JOIN from tblMyTable to qryFirstAc and only show the Service where if there is a corresponding record in the query:

    SELECT IIf(IsNull([MinOfAccount]),"",[tblMyTable]![Service]) AS Service, tblMyTable.Account
    FROM tblMyTable LEFT JOIN qryFirstAc ON (tblMyTable.Service = qryFirstAc.Service) AND (tblMyTable.Account = qryFirstAc.MinOfAccount)
    ORDER BY tblMyTable.Service, tblMyTable.Account;

    EDIT: Well acually I did it to show the accounts for each service rather than the other way round but the logic is identical

Posting Permissions

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