Results 1 to 6 of 6
  1. #1
    jonesy29847 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Location
    Detroit area
    Posts
    43

    Suppressing duplicate results

    Hello



    I am working on a Invoice database for my business, I am a service provider and charge time and expenses.

    I have query that returns tasks and expenses where the dates match, or if there is no expense return only the task.

    I have a main Invoice report and an Expense report.

    When I run the report and if there are multiple expenses for a given day the task portion of the report will print a task for each expense. I tried setting the Hide Duplicates property, but then the main task portion prints empty rows.

    Thanks in advance for your help.

  2. #2
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    Try putting DISTINCT in the query. For instance--

    SELECT DISTINCT LastName FROM tblEmployees

    --will only return SMITH once, no matter how many Smiths are in the table.

  3. #3
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    To add to CGM3's reply, if you have a specific task that you'd prefer to see (I think the DISTINCT keyword will pick the first one), throw a WHERE clause at the end to chose it. Also, the GROUP BY clause can come in handy here as well if you have other criteria.

  4. #4
    jonesy29847 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Location
    Detroit area
    Posts
    43
    Thanks for suggestions, the DISTINCT clause did not help.
    The query has grown somewhat, as I have continued development.

    My question becomes where to place DISTINCT

    SELECT ([Test Task]![JobNo]) & Left(#10/15/2010#,2) & Mid$(#10/25/2010#,4,2) AS InvoiceNo, Task.*, [Project Expenses].*, PrimaryCustomer.CustName, PrimaryCustomer.StreetAddress, PrimaryCustomer.City, PrimaryCustomer.StateProvince, PrimaryCustomer.Phone, PrimaryCustomer.Fax, PrimaryCustomer.JobNo, PrimaryCustomer.PONumber, [PrimaryCustomer]![City] & " " & [PrimaryCustomer]![StateProvince] & ". " & [PrimaryCustomer]![ZipPostalCode] AS CustCityStZip, [PrimaryCustomer]![Phone] AS CustPhone, [PrimaryCustomer]![Fax] AS CustFax, [PrimaryCustomer]![ContactFirstName] & " " & [PrimaryCustomer]![ContactLastName] AS CustContact
    FROM ((Task LEFT JOIN [Project Expenses] ON Task.TaskDate = [Project Expenses].[Expense Date]) INNER JOIN PrimaryCustomer ON Task.JobNo = PrimaryCustomer.JobNo) INNER JOIN SecondaryCustomer ON PrimaryCustomer.JobNo = SecondaryCustomer.JobNo;

    Thanks again

  5. #5
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I think your issue is at:
    (Task LEFT JOIN [Project Expenses] ON Task.TaskDate = [Project Expenses].[Expense Date])

    Is there no other field (preferable a unique identifier) you can join on? The thing is that they are not truly duplicates. That join will give you a matrix of every task to every expense for that day. It is also the reason you are seeing the blanks. As a quick fix, try using a GROUP BY at the end. Decide which field will be your unique. After your WHERE, use

    GROUP BY first(table.unique)

    I'm not 100% sure that will end up as a valid SQL statement but give it a go. I would then go back and look at the design of your database and try to find better ways to join your tables. Do a Google search on Normalization and go from there.

  6. #6
    jonesy29847 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Location
    Detroit area
    Posts
    43
    Thanks for comment on normalization, you sent me to the books.

    My thinking is that I can add a Lookup field to Task and Expense, then group by my lookup field in each report.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Suppressing #Name? Error
    By Jess_Wundring in forum Programming
    Replies: 2
    Last Post: 04-15-2010, 12:43 AM
  2. Replies: 3
    Last Post: 10-15-2009, 12:24 PM
  3. duplicate problem
    By hokie in forum Access
    Replies: 27
    Last Post: 07-13-2009, 01:43 PM
  4. Duplicate first row
    By kruai in forum Access
    Replies: 1
    Last Post: 06-22-2009, 02:06 PM
  5. Which duplicate?
    By grgerhard in forum Import/Export Data
    Replies: 1
    Last Post: 05-27-2006, 06:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums