Results 1 to 7 of 7
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    Join multiple Queries without unique ID - to show side by side view without duplicating the info

    I have two tables.



    Table A has 4 important fields (1, 2, 3, 4)
    field1 together with field 2 are unique ID, with field3 is even more unique, field 4 is just important information we want in the result of the query.

    Table B with 8 important fields - 1, 2, 3, 4, 5, 6, 7, 8
    1, 2, and 3 is unique ....... that matches to 1, 2, and 3 in table A
    1, 2, and 4 is unique ....... that matches to 1, 2, and 3 in table A
    1, 2, and 5 is unique ....... that matches to 1, 2, and 3 in table A
    1, 2, and 6 is unique ....... that matches to 1, 2, and 3 in table A
    1, 2, and 7 is unique ....... that matches to 1, 2, and 3 in table A
    1, 2, and 8 is unique ....... that matches to 1, 2, and 3 in table A

    fields 6,7,8 have some missing data.

    I want to join table A with table B to show table A's field 4 for each of table B's field 3, 4, 5, 6, 7, and 8 side by side.

    Example:
    I want to show table B's field 3 matching table A's field 4 side by side with table B's field 4's matching table A's field 4.
    It's hard because there is no join to do this, as there is no same id key from the result of

    table B's field 3 matching table A's field 4
    to
    table B's field 4 matching table A's field 4.

    I don't know if this is possible.
    Hope this makes sense. If not, I will clear it up.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    1, 2, and 3 is unique ....... that matches to 1, 2, and 3 in table A
    1, 2, and 4 is unique ....... that matches to 1, 2, and 3 in table A
    Given these two for example - do table B fields 1-2-3 and table B fields 1-2-4 match to the SAME record in A, or DIFFERENT ones?

    If they are different, then you cannot easily do what you want; you would need multiple simple queries together with a UNION query.

    I suspect you have a problem with your data structure - it may not be properly normalized. Can you show us the two tables with real field names?

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Sorry for the delay.
    Yes table B field 1,2,3 and table B field 1,2,4 match to different records to table A 1,2,3.

    I also noticed something I didn't realize before, table a and table b the third field that are just names
    they aren't an exact match because of slight spelling difference.

    So now table A 1,2 is unique, and
    table B 1,3 is unique that matches to table A 1,2
    table B 1,4 is unique that matches to table A 1,2
    table B 1,5 is unique that matches to table A 1,2
    table B 1,6 is unique that matches to table A 1,2
    table B 1,7 is unique that matches to table A 1,2
    table B 1,8 is unique that matches to table A 1,2

    I don't think there is other way to structure the table given with what we have instead I think we
    need to make up one more field to identify table b 3, 4, 5, 6, 7 as one and that are identical to each of field 3,4,5,6,7 etc ?
    Maybe I am not right and you have a better solution, please do tell.

    I want to let you know the field name but I think it can be done without knowing the field name ?
    I don't even know how the field names are related, I just know the data structure and tried to figure it out from that.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    I don't even know how the field names are related, I just know the data structure and tried to figure it out from that.
    If you don't know how the field names are related, then this exercise could easily give you completely wrong results. Now, if the field names in A are the same as the field names in B (or are very close), then you might be safe in assuming they are related, but you can't be sure. If the field names are not the same, then all you can do is guess - the order the fields occur in the table structure means very little. You would be far better off getting clarification from whoever gave you this project.

    Once you get that sorted out, you cannot do what you want to do in one query, because one record in B could match up to 7 records in A, but each of those 7 matches would use a different combination of fields from B (1-3, 1-4, 1-5,...) to math to the same record in A (1-2). You will need a separate query for each of the 7, and then a UNION query to put all the results together.


    I want to join table A with table B to show table A's field 4 for each of table B's field 3, 4, 5, 6, 7, and 8 side by side.
    I don't understand quite what you are looking for here - can you post an example of what you want the query result to look like?

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    yup - ohh I meant table A field 1 & 2 is same as table B field (1, 3), (1,4), (1,5), (1,6) etc
    I just don't know in the business sense how table A 1 is related to 2.
    Like a person name john and how he has two sneakers and 5 pants and why ?

    Is a long story. I actually setup table B this way. I set it up this way to mirrored the result we want from the query.
    If you have another way that works.

    Here is a sample of table A
    Click image for larger version. 

Name:	tabA.PNG 
Views:	17 
Size:	12.5 KB 
ID:	30183
    Here is a sample of table B
    Click image for larger version. 

Name:	tabB.PNG 
Views:	17 
Size:	8.0 KB 
ID:	30184

    Here is a sample result we like.
    Click image for larger version. 

Name:	result.PNG 
Views:	17 
Size:	27.9 KB 
ID:	30185

    c1, c2, c3, c5 etc from the result is really the same as table 2, field 3,4,5,6 etc.
    table A of 1 & 2 matches to table b of 1,3 or 1,4, or 1,5 or 1,6 to get the result of c1, c2, c3, c4, c5 etc
    Hope that is clear.

  6. #6
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Just want to say that table B is created from table A but organized it differently and without table A's field 4.
    It is organized in that way so the output looks like the sample result.

    2&3 of table A and result and 1&2 of table B is name and title.
    1 of table a, and 3,4,5,6 of table B is code.
    c1,c2,c3, and c5 of result code of of table A field4.
    Last edited by johnseito; 09-03-2017 at 06:13 AM.

  7. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Sorry just confidential info, not bc I don't want to share.
    But 2&3 of table A and result and 1&2 of table B is name and title.
    1 tble a, 3,4,5,6 of tble B is code.
    c1,c2,c3, c5 is result of code of tble A field 4. Thx

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

Similar Threads

  1. Replies: 11
    Last Post: 06-23-2017, 10:25 AM
  2. split table and join it again side by side
    By andifi in forum Queries
    Replies: 3
    Last Post: 02-02-2017, 03:24 AM
  3. Replies: 2
    Last Post: 07-25-2016, 01:12 PM
  4. Showing parameter fields side by side
    By sebgrb in forum Queries
    Replies: 2
    Last Post: 03-07-2014, 02:25 AM
  5. Multiple records side by side
    By Patience in forum Reports
    Replies: 8
    Last Post: 09-01-2010, 09:17 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 - Senior Forums