Results 1 to 2 of 2
  1. #1
    Dega is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Calgary, AB
    Posts
    73

    Duplicates in Query

    I am have difficulty with duplicates with a two table query. One table is Customers and the othe table is Guests as it relates to customers having guests in their room.

    Customers can have a room without guests (single occupancy) but I need to query both tables for a total count (Customers + Guests).

    SELECT tblCustomers.IngCustomerID, tblCustomers.txtCustomerFirstName, tblCustomers.txtCustomerLastName, tblGuests.IngCustomerID, tblGuests.IngGuestID, tblGuests.txtGuestFirstName, tblGuests.txtGuestLastName
    FROM tblCustomers, tblGuests
    WHERE (((tblCustomers.IngCustomerID)=[tblGuests].[IngCustomerID] Or (tblCustomers.IngCustomerID)<>[tblGuests].[IngCustomerID]));


    I have attached a *pdf with the joins and query output.

    Thanks

  2. #2
    Dega is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Calgary, AB
    Posts
    73

    Left Join

    After much trial and error the LEFT JOIN seems to work better at least for reports.


    SELECT tblCustomers.IngCustomerID, tblCustomers.txtCustomerFirstName, tblCustomers.txtCustomerLastName, tblGuests.txtGuestFirstName, tblGuests.txtGuestLastName
    FROM tblCustomers LEFT JOIN tblGuests ON tblCustomers.IngCustomerID=tblGuests.IngCustomerID ;

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

Similar Threads

  1. Find Duplicates Query
    By mulefeathers in forum Queries
    Replies: 13
    Last Post: 04-22-2010, 05:39 PM
  2. combo box - no duplicates
    By pkg206 in forum Access
    Replies: 1
    Last Post: 11-12-2009, 10:41 AM
  3. How to remove duplicates
    By TonyBender in forum Access
    Replies: 0
    Last Post: 10-21-2009, 10:27 PM
  4. Deleting Duplicates
    By TundraMonkey in forum Queries
    Replies: 4
    Last Post: 09-08-2009, 07:13 AM
  5. Pulling out duplicates
    By FREEEEEEDOM in forum Access
    Replies: 1
    Last Post: 04-21-2009, 10:37 PM

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