Results 1 to 13 of 13
  1. #1
    anasttin is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    37

    joining 2 tables


    Hi,

    I want to join 2 tables so that every record of each table is shown, and I can see which table each record exists in (like in the 3 grids below).

    None of the join types to merge TABLE A and TABLE B seem to helpful with this and drop records.

    Can anyone provide any advice on how to achieve the query result I need?

    Many thanks in advance.



    TABLE A
    PRODUCT IN TABLE A
    Widget#1
    FOUND
    Widget#2
    FOUND

    TABLE B
    PRODUCT IN TABLE B
    Widget#1
    FOUND
    Widget#3
    FOUND

    QUERY RESULT
    PRODUCT IN TABLE A IN TABLE B
    Widget#1 FOUND FOUND
    Widget#2 FOUND
    Widget#3 FOUND

  2. #2
    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
    Tell us what each of these tables represents in clear, simple terms.
    People are here and will help. But you have to clearly communicate the problem/opportunity such that readers understand.
    We know little about you, your business and environment. So describe the what you are trying to do clearly. Readers will assist you to find options for how to accomplish it, once they understand what you are trying to do.

    Good luck.

  3. #3
    anasttin is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    37
    Well basically, I have 2 tables.

    Some product numbers appear in both tables and other product numbers appear in one of the tables but not the other table.

    I need a query that shows me every product that appears in either table, along with some indication that tells me which tables it is in - the first table, the second table, or both tables.

    Thanks for your help.

  4. #4
    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
    Hmmm?

    So you have some Products.

    What exactly are the Tables? If you can't describe clearly what you are trying to accomplish, readers will have to guess at what you are doing. I suggest you try again to describe your issue/opportunity.

    Pretend you are dealing with an 8 year old -- in simple terms what are you trying to accomplish?

  5. #5
    anasttin is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    37

    Red face Sorry guys

    Quote Originally Posted by orange View Post
    Hmmm?

    So you have some Products.

    What exactly are the Tables? If you can't describe clearly what you are trying to accomplish, readers will have to guess at what you are doing. I suggest you try again to describe your issue/opportunity.

    Pretend you are dealing with an 8 year old -- in simple terms what are you trying to accomplish?
    I'm really not sure how I can explain it any clearer than in my first post, but I'll give it another go. (Unfortunately, I'm dealing with confidential info, so can't be absolutely specific), but here's a real world, example...

    -----------------------------------------------------------------------------
    Table A lists products that were in our range at the start of the year.
    Table B lists products that were in our range at the end of the year.

    Some products were kept from the start to the end of the year.
    Some products were dropped from our range mid-year.
    Some new products were added to our range mid-year.

    I need a query that will give me a list of every product that we had in our range at either the start or end of the year, and whether it was in there at the start or end of the year, or both the start and end of the year.

    So the query will give me a unique list of products with 2 columns next to it called, "in at start of year" and "in at end of year". In these 2 columns, next to each product, it will say "yes" or "no", to indicate if it was in at that point or not.
    -----------------------------------------------------------------------------

    Hope that's a better example and a bit clearer.

    Thanks for your patience.

    Example of query output.

    PRODUCT NUMBER IN AT START OF YEAR IN AT END OF YEAR
    PRODUCT#1 YES YES
    PRODUCT#2 YES NO
    PRODUCT#3 NO YES

  6. #6
    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
    So if a Product is in TableA and TableB, it's Yes/Yes
    If a Product is in TableA and not in Table B, it is Yes/No
    If a Product is not in TableA and is in Table B, it is No/Yes

    Review Left Join, Right Join and Inner Join .

    Good luck.

  7. #7
    anasttin is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    37
    Yes, that's exactly right.

    I know how to do a left join, right join and inner join, but this is kind of all three, right?

    How do I do that?

  8. #8
    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
    Is there a reason you can't use 3 separate queries? That would be straight forward in my view.

    For a different approach, consider a table (anasttin with fields Product, InPlaceYearStart, InPlaceYearEnd, YearOfData.
    And sample data
    Code:
    Product, InPlaceYearStart, InPlaceYearEnd, YearOfData
    P1,yes,yes, 2015
    p2,yes,yes,2015
    p3,no,yes,2015
    p4,yes,no, 2015
    Query:
    Code:
    SELECT Anasttin.Product, Anasttin.InPlaceYearStart, Anasttin.InPlaceYearEnd
    FROM Anasttin
    WHERE (((Anasttin.InPlaceYearStart)="yes") AND ((Anasttin.InPlaceYearEnd)="yes"))
    Union
    SELECT Anasttin.Product, Anasttin.InPlaceYearStart, Anasttin.InPlaceYearEnd
    FROM Anasttin
    WHERE (((Anasttin.InPlaceYearStart)="yes") AND ((Anasttin.InPlaceYearEnd)="no"))
    union 
    SELECT Anasttin.Product, Anasttin.InPlaceYearStart, Anasttin.InPlaceYearEnd
    FROM Anasttin
    WHERE (((Anasttin.InPlaceYearStart)="no") AND ((Anasttin.InPlaceYearEnd)="yes"))
    Results:
    Code:
    Product InPlaceYearStart InPlaceYearEnd
    P1 yes yes
    p2 yes yes
    p3 no yes
    p4 yes no

    it might be a better set up, but you know your environment and readers don't.

  9. #9
    anasttin is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    37
    Well no, but its just less clutter and I'll have to replicate what I do 5 times to suit my purposes, so I'd be looking at 15 queries in total... messy.

    Anyway, I appreciate your help orange. Have a good weekend.

  10. #10
    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
    I was updating my post when you responded.
    But here's another

    2 tables
    anasttinYearStart and anasttinYearEnd with only 1 field ProductID
    where
    anasttinYearStart
    Code:
    Productid
    a
    b
    d
    e
    anasttinYearEnd
    Code:
    Productid
    a
    c
    e
    another union query

    Code:
    SELECT anasttinYearStart.Productid AS StartOnly, "" as EndOnly,"" as AllYear
    FROM anasttinYearStart LEFT JOIN anasttinYearEnd ON anasttinYearStart.Productid = anasttinYearEnd.Productid
    WHERE (((anasttinYearEnd.Productid) Is Null))
    
     union
    SELECT "" as startOnly,anasttinYearEnd.Productid AS EndOnly,"" as allYear
    FROM anasttinYearStart RIGHT JOIN anasttinYearEnd ON anasttinYearStart.Productid = anasttinYearEnd.Productid
    WHERE (((anasttinYearStart.Productid) Is Null))
    
    Union
    SELECT "" as startOnly,"" as EndOnly, anasttinYearStart.Productid as AllYear
    FROM anasttinYearStart INNER JOIN anasttinYearEnd ON anasttinYearStart.Productid = anasttinYearEnd.Productid

    with results
    Code:
    StartOnly EndOnly AllYear
    a
    e
    c
    b
    d

  11. #11
    anasttin is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    37
    Your first solution is exactly what I'm after.

    Thanks very much for your time and effort Orange - it will make things much cleaner for me.

    I'll implement it Monday.

    Thanks again.

  12. #12
    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

  13. #13
    anasttin is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    37

    Hmmm...

    Quote Originally Posted by orange View Post
    You are welcome. Happy to help.
    On closer inspection, I don't think those 2 solutions are what I'm after but I came up with another idea in a moment of clarity (maybe).

    I'm going to append the start and end lists, group them to make them unique, and then join to that shorter list, the start and end lists separately to indicate where they appear and where they don't.

    I'll see how I go with that anyway.

    Thanks

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

Similar Threads

  1. Joining tables help
    By grewpar in forum Access
    Replies: 7
    Last Post: 10-07-2014, 12:11 PM
  2. Joining two tables
    By rspai in forum Queries
    Replies: 7
    Last Post: 05-22-2012, 12:21 AM
  3. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 01:45 PM
  4. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 04:37 AM
  5. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 PM

Tags for this Thread

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