Results 1 to 11 of 11
  1. #1
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33

    Report returning duplicates when titles match

    Hello,


    I run a travel database and am having a problem with my report (and essentially the query). When I have to trips with the same names, it appears twice in my report. For example, our trip titled "Paris Immersion" has two departure dates, on 7/11 and again on 8/4. However, when I run the query to get the traveler counts for these trips, the results appear as:

    Title | Dates | Counts
    Paris Immersion | 7/11/18-7/18/18 | 4
    Paris Immersion | 7/11/18-7/18/18 | 13
    Paris Immersion | 8/4/18-8/12/18 | 4
    Paris Immersion | 8/4/18-8/12/18 | 13

    When I change the titles slightly (by adding a number or removing a space), the problem doesn't occur. Each trip is listed only once in the Trips table, but I can't figure out why they are appearing twice here. I don't necessarily want to keep the changed titles since this report goes to my boss and they just need to trip title (having the travel ID instead wouldn't make sense to them)

    Any help would be great.

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Post your query that you used for the report.

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I assume you have at least two tables and you only join by Trip Name (or Description). Can you add a second join between the start dates in the two tables and see what you get?

    Cheers,
    Vlad

  4. #4
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33

    Desktop image of query

    Quote Originally Posted by alansidman View Post
    Post your query that you used for the report.
    Here is the query:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	87.6 KB 
ID:	32931

  5. #5
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    I've tried a few different joins between the start dates, but keep getting the response when I run the query that they are ambiguous. What's strange is that neither trip is listed twice in the Passenger Counts table NOR the Trips table.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You have a calculated field in your query which we can't see all of. It looks like it involves starting price and may be the cause of duplication. You could try using the Unique Rows and Unique Values property (see query property sheet when in design view) but you can use only one or the other. If neither helps, suggest you copy this query and in the copy, start with 1 or 2 simple fields and build from there. When you get duplicates again, you will know which field is causing the issue. That's not always 100% the culprit as it could be working in tandem with some other field, meaning that if you remove prior added fields, the issue could go away again.

    EDIT
    it could be that the right outer join is creating multiple combinations because of duplicates of the HostId.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you should use TripId in your query. It seems to be unique whereas TripName seems duplicated.
    Just a thought to consider.

  8. #8
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    Quote Originally Posted by orange View Post
    Perhaps you should use TripId in your query. It seems to be unique whereas TripName seems duplicated.
    Just a thought to consider.
    Thanks for the recommendation. When I added the TripID it was also duplicated. Must be in the tables.

  9. #9
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    I do have a calculated field, which multiplies the number of passengers by the cost of the trip, by the percentage of commission we get:

    Total: [TripsTbl]![Starting Price]*[TripsTbl]![Commission Rate]*[Trip Counts]![Passenger Count]

    Unfortunately there is no Host yet on this trip (we send a rep from our organization occasionally to evaluate the tour). I tried setting either unique values or unique records, but to no avail. I'll try starting over and see where I get...

  10. #10
    bridgeo730 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Jan 2018
    Posts
    33
    yes that was it! There was something going on with the start dates. Many thanks!

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'll try starting over and see where I get...
    Quote Originally Posted by bridgeo730 View Post
    yes that was it! There was something going on with the start dates. Many thanks!
    Sounds like you found it by eliminating fields as suggested. Good that you solved it. Good luck with your project.

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

Similar Threads

  1. query returning duplicates
    By hak in forum Queries
    Replies: 17
    Last Post: 09-28-2017, 02:48 PM
  2. help with query returning duplicates
    By BrockWade in forum Queries
    Replies: 7
    Last Post: 12-05-2013, 02:23 PM
  3. Query Returning Duplicates
    By rlsublime in forum Queries
    Replies: 14
    Last Post: 03-25-2013, 11:26 AM
  4. Querying multiple queries, returning duplicates
    By Gabriel2012 in forum Queries
    Replies: 3
    Last Post: 12-04-2012, 12:39 PM
  5. Replies: 12
    Last Post: 10-15-2010, 10:03 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