Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: Stopping duplication in a query help

    I am using access 2007
    and want to filter a name ie

    joe bloggs 6 toffee lane picture
    joe bloggs 6 toffee lane tree
    joe bloggs 6 toffee lane dog
    joe bloggs 6 toffee lane homecar

    When i enter a query for joe bloggs 6 toffee lane i would like him to appear only once
    instead of 4 times
    how do i do this?
    thanks paul

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what query are you suing so far
    where are the duplicates coming from
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2009
    Posts
    6

    i WOULD LIKE PRODUCE a list of people in one town

    together with there name and address. Some of those people will have several cars
    ie
    john hide ford
    john hide vauxhall

    but in the query i only want john hide to appear once only
    not john hide
    john hide

    the query is coming from the main table/database

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and what is the query
    what is the SQL
    what is the table(s) design/s
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make a query that has the detail that you want (name and town) and use the grouping option (Group By) on them.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Feb 2009
    Posts
    6

    some told me how to do thjis using unique

    Quote Originally Posted by StarTrekker
    Make a query that has the detail that you want (name and town) and use the grouping option (Group By) on them.
    records for acess 2003 but ive forgotten how to do this and we have now upgraded to access 2007 and everything has now changed - bugger

    I am building a query using design view from a table (the main database) .
    help

    The table will show

    john hide ford
    john hide vauxhall
    John ford citroen
    Paul Jones seat
    paul jones bmw
    paul jones mini

    when i set the query up and it lists all the data i would like the name john hide and paul jones to appear only once and not 3 times in the query
    please note i am not familier with sql and i am not a database writer, so my knowledge is very limited.
    Last edited by snakeeyes; 02-12-09 at 13:18.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    open your query in design view
    switch to SQL view
    copy/paste the SQL here

    without your SQL, i can only guess - here's my guess.
    you currently have:
    SELECT blah blah...
    edit in SQL view to:
    SELECT DISTINCT blah blah...

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Feb 2009
    Posts
    6

    sql attached

    SELECT DISTINCTROW DBb.Surname, DBb.[Surname 2], DBb.RI, DBb.[No], DBb.Street, DBb.Town, DBb.Company, DBb.[Prod / Mort Deal], DBb.Code, DBb.Prem
    FROM DBb
    WHERE (((DBb.RI) Like "pe") AND ((DBb.[Prod / Mort Deal]) Like "isa" Or (DBb.[Prod / Mort Deal])="pep" Or (DBb.[Prod / Mort Deal])="bond" Or (DBb.[Prod / Mort Deal])="ut"));

    I would like the unique record to be based on the 'code' or the 'code' and 'surname' is that possible

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Unique/distinct won't work if you include the additional car field. Just add the personally identifiable fields -- name and surname. Then you can use either DISTINCT or apply grouping to the query as I suggested in post 5.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Feb 2009
    Posts
    6

    how do i apply grouping?

    Quote Originally Posted by StarTrekker
    Unique/distinct won't work if you include the additional car field. Just add the personally identifiable fields -- name and surname. Then you can use either DISTINCT or apply grouping to the query as I suggested in post 5.
    how do i apply grouping?

  11. #11
    Join Date
    Feb 2009
    Posts
    6

    not sure if grouping will work thats if ive managed to do this correctly

    Lets start again with the actual senario
    from the main table i would like to build a query that will list the following fields
    Surname mr, Surname mrs, House number, street, postcode and investments type

    ie evans evans 1 enfield road sr1 7yt pep

    Mr and mrs evans have several investments so in the main table they have the following
    mr evans 1 enfield road sr1 7yt pep
    mr evans 1 enfield road sr1 7yt Bond
    mr evans 1 enfield road sr1 7yt OEIC
    mrs evans 1 enfield road sr1 7yt ISA

    So in the query they will show up 4 times
    i would like them to only show up once because they live at the same address in the query

    is it also possible to arrange a query so that mr shows up once and mrs shows up once in the query ?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    shouldn't this be handled in the front end?
    is this something you MUST do in the query?

    if you were using it in a report you'd move the address to the group header and report the details as required. you can do the same thing with the form design.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by snakeeyes
    how do i apply grouping?
    Just hit the button with the Greek Sigma on it (Totals I think).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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