Results 1 to 8 of 8
  1. #1
    Edros is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    5

    Query join

    I have a table (1) that has 30 products.



    I have another table (2) that has 5 fields. The first field is an autonumber, the second field has a lookup to the Customers table and the 3rd field has a look up to the other table to look up the product. The other two fields are entered.

    At the moment, I have a query that joins table (1) and table (2) and pulls in information from both tables, however, it will only pull in the products that the customer has selected in table (2).

    I need to create a query, that pulls in all 30 products for each customer, whether they have quantity of the product or not.

    Is this possible?

    I am using Access 2010. The database is a mix of web tables, queries and reports. the two tables I am trying to work with are Web tables.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You want an outer join. Dbl-click the join to set all records in products,some in person.

  3. #3
    Edros is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    5
    Hi, I have done that but it still does not allow me to get all the products, only those products that have the matching customer. Basically, what I want is 30 records for each customer with quantity appearing next to the product that they have and the other products to be empty.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    INNER join shows only matching products
    OUTER join shows all.

  5. #5
    Edros is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    5
    Yes, I know. Unfortunately, the outer join is not working. It will only pull in the records from the customer with those products they have quantity for but not pull in all prodcuts.

    For example,
    Customer
    Joe Blow Microwave 6

  6. #6
    Edros is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    5
    Sorry, was trying to give an example.

    Customer Product Qty
    Joe Blow Microwave 6

    However, the products table has other products, so I want to see from the query:

    Customer Product Qty
    Joe Blow Microwave 6
    Joe Blow Oven 0
    Joe Blow Fridge 0

    I can't seem to get this, no matter how I do the queries. I have tried a Union query using both the left and right joins but it still will not work.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It sounds like you have /should have 3 tables
    Code:
      Customer                                           Product
         CustID                                                   ProdID
         otherCustInfo                                         OtherProdInfo
           
                                       CustomerHasProduct
                                            CustId
                                             ProdID
                                              Qty

  8. #8
    Edros is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    5
    Yes, that is what I have. I am trying to link Product table back to CustomerHasProduct table and have it show each of the 30 products for each customer with the quantity only for those products that are in the CustomerHasProduct table and 0 for the other products per customer. The CustomerHasProduct table is a standing order that customers have. What we are trying to achieve is to send this out to customers showing the products that they have but also show the products that they could also order.

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

Similar Threads

  1. Replies: 7
    Last Post: 03-23-2017, 10:26 AM
  2. Need a query with join
    By DavidWrightSr in forum Queries
    Replies: 3
    Last Post: 01-18-2017, 07:24 PM
  3. Replies: 6
    Last Post: 11-19-2013, 01:38 AM
  4. Inner join query not quite right
    By coach32 in forum Queries
    Replies: 6
    Last Post: 07-07-2012, 03:13 AM
  5. Join Query
    By hithere in forum Queries
    Replies: 4
    Last Post: 02-17-2012, 06:18 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