Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 97 - Finding & Eliminating Duplicates (97-SR2)

    I have a query that gives us a list of customers who have placed an order during a given time period. Since we have many repeat customers, running that query will give us a lot of duplicate records and we need to eliminate them (just from the query) before running Word merges for letters and envelopes.

    So, my query (ordersbydaterange) includes ShippedDate CustomerLastName, CustomerFirstName, CompanyName, Client Name (the reason for this is that when a client in not a Company, we use the following expression to pull out a client name

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    Exactly what is a duplicate? If the customer has 2 orders shipped during the specified date range, but on different dates, is that a duplicate? If it is, then I'd drop the ShipDate from the query (you can still have it as a selection criteria, just don't show it). Then, use the DISTINCT keyword in your Select query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    We are looking for duplicate clients so we don't send them the same letter twice.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    In the design view of your query, select the menu View, Totals.
    For all the fields enter in the Total Line Group By (the default), except for the field ShippedDate. For this field, enter Where.
    This will give you a list of unique clients with shipped orders between your two dates.
    Francois

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    Then do what I first suggested, use the Distinct keyword and do not show the shipping date in the query. This will then produce 1 record per customer who had any shipments in that period.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    I used SELECT DISTINCT in the Client field and it keeps saying "At most one record can be returned" but doesn't even return the first.

    The Client field, as I believe I mention is a calculation of: Client: IIf(IsNull([companyName]),[ContactfirstName] & " " & [contactlastName],[companyName])

    Could that be causing the problem? In order to get around the expression, I copied the old query and used that as a basis for a new query and put the SELECT DISTINCT in the Client field????

    I also tried 5StartLounger's suggestion of Views, Totals (Total Line Group by) except for Shipped Date where I entered WHERE. That gave me a message saying I "tried to execute a query that doesn't inlcued the specified expression 'Orders.ShippedDate' as part of an aggregate funtion???

    Thanks

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    Assuming you have a Customers table and an Orders table, your query would be this:

    Select Distinct Customers.* FROM Customers, Orders WHERE Customers.CustomerID=Orders.CustomerID AND ShippedDate Between [StartDate] AND [EndDate]
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    I put that statement in the ClientID field of the query and get the following message:
    "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis"

    ??

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    What do you mean by "I put that statement in the ClientID field of the query"? The SQL statement I gave you is the whole query, not a subquery.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    Here is my query:
    SELECT Customers.*
    FROM Customers, Orders
    WHERE (((Customers.CustomerID)=[Orders].[CustomerID]) AND ((Orders.ShippedDate) Between [BeginningDate] And [EndingDate]));

    Access absolutely will not let me use DISTINCT; keeps telling me to take it out. However, when I run the above, it returns all 573 records, not just the unique ones.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    Hi, CD.

    Where are you trying to *put* DISTINCT and where are you trying to put the subquery? The way to use DISTINCT is "SELECT DISTINCT Customers.* FROM ...." I don't quite see what you're trying to do here, though.
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    I have an orders table and a customers table. Most customers have many orders. We are pulling those that have ordered in a range of dates: Between [BeginningDate]AND[EndingDate] using the ShippedDate field.

    To complicate this I have a formula to return [Client] since we have [CompanyName], [ContactFirstName], [ContactLastName] and in the case of private customers (those without a CompanyName), [Client] returns either company name or ContactFirstName+ContactLastName.

    So I tried:

    (SELECT Client
    FROM qryordersbydaterange
    WHERE [ShippedDate]BETWEEN[BeginningDate]AND[EndingDate]) which I placed in the criteria area of the Client column of the query grid - didn't work. I also tried doing that in the ClientID column of the query grid - didn't work.

    Tried using Unique Records property - didn't work.

    I would be willing to send a small compressed version of this thing to anyone who things they may be able to solve the problem.

    Thanks.

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

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    Send it !!

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    If you want you can send it to me. My email is in my profile
    Francois

  15. #15
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 97 - Finding & Eliminating Duplicates (97-SR2)

    You mean Access doesn't like "Select Distinct Customers.* ....etc.)?

    What error message is it giving you? This is very odd.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Page 1 of 2 12 LastLast

Posting Permissions

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