Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Records in a Query (Access 2002)

    Another question <img src=/S/eargear.gif border=0 alt=eargear width=20 height=20>
    I have a database I'm trying to set up with a query (3 tables) that will give me unique names. The Select statement below of course shows duplicates as a lot of people can be on more than 1 committee but we only want to send them 1 e-mail. I read all the current posts on No Duplicates etc and none of them had 3 tables. Can anyone give me some suggestions. Thanks again. This is a great forum. Robin

    SELECT ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm
    FROM (ATA INNER JOIN ATA_Committee ON ATA.ID = ATA_Committee.ID) INNER JOIN Committee ON ATA_Committee.Lcomm = Committee.Code;

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Unique Records in a Query (Access 2002)

    Change your select from:
    SELECT ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm
    FROM (ATA INNER JOIN ATA_Committee ON ATA.ID = ATA_Committee.ID) INNER JOIN Committee ON ATA_Committee.Lcomm = Committee.Code;
    to
    SELECT DISTINCT ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm
    FROM (ATA INNER JOIN ATA_Committee ON ATA.ID = ATA_Committee.ID) INNER JOIN Committee ON ATA_Committee.Lcomm = Committee.Code;

    I don't know what the fields ATA_Committee.Lcomm & Committee.TabComm are, these may not give you distinct values, if not then delete these 2 fields from the query.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records in a Query (Access 2002)

    Hi Pat - no they aren't unique - they give the name of the committees that the people belong to (which can be more than one). Thank you very much that works for me.
    How are things in Melbourne today (or tomorrow) for me.
    Robin

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Unique Records in a Query (Access 2002)

    Hi Robin

    The weather is overcast and drizzly, not too good for being outside, how about your side of town.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Pat

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Records in a Query (Access 2002)

    About the same here. Temp is 7 cel. Have you ever been to Canada. I thought Melbourne was always very warm. This is your summer isn't it?

    Pat I posted that other problem with a table that has gotten very large - any solutions that you can think of?
    Robin

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Unique Records in a Query (Access 2002)

    7 cel is not too bad as long as there is no wind.

    Well it's still our Spring, the temp is about 15 cel. No Melbourne gets very cold in the Winter months (and other times) when the wind blows off the Southern Ocean, then it gets bitterly cold (what they don't tell you here is the real temp after the Wind Chill factor is taken into account).

    I did visit Toronto, Ontario (or is it the other way around) quite a while back. It was delightful, it was nowhere near long enough.

    I have already responded to that post.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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