Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Question Unanswered: Repeating Rows - Excluding repeating fields

    Hi,

    I've got a problem for which I've been trying to fix for a little while but kept putting it off. I don't know how hard (or simple) it is to solve but slapping a "SELECT DISTINCT ..." at the start of my query doesn't work.

    I'm using ASP .NET which is accessing a Microsoft Access database. Imagine I'm using this table below (not actual table, simplified version) with the following fields;

    ContactId, Address and Comments.

    I'm trying to create a query, which selects every row (and every column) in the database, but does not return a dataset containing rows that have repeated data in the Comments field only.

    Can anyone help? Thanks.

    Goran

  2. #2
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    8
    I am not sure about what you are saying, but the only way to avoid selecting many records that are the same, is by using "SELECT DISTINCT(comments), ..........."

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    Well, to clear things up, basically I want to get rid of rows where there is repeating 'comments' but not rows where there are repeating 'contactid's and 'address's.

    Remember, I want to select all columns

    Thanks

    Goran

  4. #4
    Join Date
    Nov 2003
    Location
    Surrey, UK
    Posts
    2
    You could try a two-query approach:

    SELECT DISTINCT ID, Address
    in the first query (qryContactsA), then do a join on the results of that query and the original table to get just one instance of the comments - something like:

    SELECT qryContactsA.ID, qryContactsA.Address, First(tblContacts.Comments) AS FirstOfComments
    FROM tblContacts INNER JOIN qryContactsA ON (tblContacts.Address = qryContactsA.Address) AND (tblContacts.ID = qryContactsA.ID)
    GROUP BY qryContactsA.ID, qryContactsA.Address;

    depending on what criteria you want for selecting which matching comments field is shown.

    Regards

    Craig

  5. #5
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    8

    Re: Repeating Rows

    Well, I think u must do the following code:

    "SELECT DISTINCT Comments,Address, ContactID FROM table"

    But if different people sends the same comments, the select statement I dave you will take one of those different peoples. For more information, Please call me at (083) 669 9480

    Please do not mind my English, I am a South African.

  6. #6
    Join Date
    Nov 2003
    Location
    Makati City, Philippines
    Posts
    14

    Re: Repeating Rows - Excluding repeating fields

    switch to the SQL window from the QBE (query by example) window and try this

    SELECT Table1.ContactID, Table1.Address, First(Table1.Comments) AS [Sample Comments]
    FROM Table1
    GROUP BY Table1.Comments
    HAVING (((Count(Table1.Comments))>1));

    i guess the key here is the HAVING Count(Comment) > 1. hope i was able to help you in my own little way.

    cheers,
    ReggieSter


    Originally posted by GoMo
    Hi,

    I've got a problem for which I've been trying to fix for a little while but kept putting it off. I don't know how hard (or simple) it is to solve but slapping a "SELECT DISTINCT ..." at the start of my query doesn't work.

    I'm using ASP .NET which is accessing a Microsoft Access database. Imagine I'm using this table below (not actual table, simplified version) with the following fields;

    ContactId, Address and Comments.

    I'm trying to create a query, which selects every row (and every column) in the database, but does not return a dataset containing rows that have repeated data in the Comments field only.

    Can anyone help? Thanks.

    Goran

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    GoMo, please show example result rows


    rudy

  8. #8
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    TABLE Contacts

    ContactId, Address, Comments
    =============================================
    1, goran@creatingit.com, can you help me out?
    2, goran@creatingit.com, can you help me out?
    3, goran@creatingit.com, i haven't solved it yet?
    =============================================

    basically, what i'm trying to do is view all the contacts that have been received through the site and logged in the database, but in some cases people have clicked the submit button twice (damn them!).

    note that in the actual table, i have about 10 columns and still need to select all of them (including the 'ContactId', which i guess, doesn't make them repeating rows).

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select * from Contacts
    where comments in
    ( select comments from Contacts
    group by comments
    having count(*) > 1 )


    this will return "dupe" rows, i.e. rows with comments that have been duplicated


    rudy

  10. #10
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    sorry, i may have explained it wrong.

    i'm trying to eliminate duplicate rows from the results. so it will only display one occurance of each contact received.

    note that the field 'Comments' is of datatype 'Memo'. i'm not sure if that has any effect on how the sql query is constructed, since the last query that you provided returned the following error:

    "Invalid Memo, OLE, or Hyperlink Object in subquery 'Comments'."

    thanks

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gee, i didn't know that about memo items

    i guess you cannot run the sql on that table then

    okay, another approach:

    Code:
    select id, address
      , left(comments,100) as comments100
    into Contacts100
     from Contacts
    this will create a table with a Text instead of memo field

    then run this:

    Code:
    select * from Contacts100
    where comments100 in 
    ( select comments100 from Contacts100
    group by comments100
    having count(*) > 1 )
    this will identify the dupe rows, and you can then use the ID numbers to go back into the original table and delete the ones you want


    rudy

  12. #12
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    Don't worry about it, i've found a compromise by selecting all the columns in the tables besides the ContactId and sticking a DISTINCT at the front.

    It seems to have solved the problem, but now it's cutting off longer comments after 255 chars. (it didn't do that with the original "SELECT * FROM ...." query) I guess thats because i'm using memo or something. But I'm not so worried now as we've just had a heated arguement with the client and we just wanna get the site out the door.

    Thanks for all your help Rudy, and the other guys of course.

    Goran (GoMo)

Posting Permissions

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