Select distinct values from two different columns of two tables with other non-distinct columns

Question

I'm trying to eliminate duplicate rows from an Access dataset made up of two tables. I want to retrieve the distinct values from one column of two tables, but also retrieve the values of other columns of duplicates and unique values.

The field I want unique values for is [PART-SN] from table2. I want to select all other fields from table1 and [PART-SN] from table 2, of which all rows should be returned for all distinct rows of [PART-SN]. [PART FIND NO] and [PART-ATA-NO] have equivalent values and has duplicates. I've seen posts on how to get values from two tables of the same column. Is there a way to join the two tables to get this result?

Sample data (not actual data):

Table 1:

ID  BOM_PART_NAME  PART FIND NO  POS  LCN  POS_CT   
1       E              0001       1    P0    1
2       A              0002       1    P1    1
3       C              0003       1    P2    1
4       D              0004       1    P3    1
5       F              0005       1    P4    1

Table 2:

ID  PART-ATA-NO    PART-SN     PART-NAME
1      001                         A
2      002                         B
3      003                         C
4      004          1100           D
5      005          1101           E

ID  BOM_PART_NAME  PART FIND NO  POS  LCN  POS_CT   
1       E              0001       1    P0    1
2       A              0002       1    P1    1
3       C              0003       1    P2    1
4       D              0004       1    P3    1
5       F              0005       1    P4    1

Table 2:

ID  PART-ATA-NO    PART-SN     PART-NAME
1      001                         A
2      002                         B
3      003                         C
4      004          1100           D
5      005          1101           E

What I'm getting:

ID ... PART FIND NO       POS       PART-ATA-NO   PART-SN
1        001              1           001          1369
2        002              1           002          1444
3        003              1           003          1100
3        003              1           003          1101  
3        003              1           003          1102
4        003              2           003          1101
4        003              2           003          1102
5        004              1           004          1101
5        004              1           004          1102

Desired Result:

ID    PART FIND NO       POS      PART-ATA-NO     PART-SN
1        001              1           001          1369
2        002              1           002          1444
3        003              1           003          1100
4        003              2           003          1101  
5        003              3           003          1102
6        003              4           003          1103
7        003              5           003          1104
8        004              1           004          1105
9        004              2           004          1106

Answer 1

Open up Access and click on create new query It will prompt you to select the tables you want to include Once you have them selected, if they dont already have relationship lines between them, drag/drop the matching fields to create the relationships Then click on the Sigma symbol to turn on groupings option Drag the fields you want included down to the area where you set up the output for the query Then select which fields you want to group by and which fields are the expressions you are trying to get the values for Run the query and see if you get the results you want If you dont, fiddle with the options until you get the output you want Then click the dropdown for the Design/Run button and there will be a SQL option Click that and it will show you the select statement you want to use

Answer 2

If you want unique values for [PART FIND NO] and [PART-ATA-NO] you can try to do an UNION.

SELECT Table1.[PART FIND NO]
FROM Table1 UNION SELECT Table2.[PART-ATA-NO] As [PART FIND NO] From 
Table2;

It's a bit weird, but I have done a test in Access 2016 with values repeated in Table1 and Table2 and even values appearing twice in a Table and the Union only returns the values once.

|Table1|
|AAA|
|BBB|
|CCC|

|Table2|
|AAA|
|BBB|
|AAA|
|DDD|

|Query 1|
|AAA|
|BBB|
|CCC|
|DDD|

Hope it helps

Answer 3

I think the overall question is a duplicate of How do I merge two tables in Access while removing duplicates?

But here is a query specific to your needs.

EDIT: Updated second query to remove Table2.[PART-SN] to eliminate duplicates of merged tables after more information from original poster.

SELECT Table1.*, Table2.[PART-ATA-NO], Table2.[PART-SN]
FROM Table1 INNER JOIN Table2 ON Table1.[PART FIND NO] = Table2.[PART-ATA-NO]

UNION

SELECT Table1.*, Table2.[PART-ATA-NO]
FROM Table1 LEFT JOIN Table2 ON Table1.[PART FIND NO] = Table2.[PART-ATA-NO]
WHERE (((Table2.[PART-ATA-NO]) Is Null))

UNION

SELECT Table1.*, Table2.[PART-ATA-NO], Table2.[PART-SN]
FROM Table1 RIGHT JOIN Table2 ON Table1.[PART FIND NO] = Table2.[PART-ATA-NO]
WHERE (((Table1.[PART FIND NO]) Is Null));

It is likely not possible to fullfil all requirements in one query. The results of one query can feed another query. It is possible to use a saved (i.e. named) queries in the FROM clause of another query (or if in design mode it means you can show and join to other queries just like tables). If eliminating duplicates is the primary concern, then save your modified UNION query (like above), for example named as [Merge1and2].

Assuming that [Merge1And2] now contains only unique [PART FIND NO], you can do something like

SELECT [Merge1And2].[PART FIND NO], Table2.[PART-SN]
FROM [Merge1And2] INNER JOIN Table2 ON [Merge1And2].[PART FIND NO] = Table2.[PART-ATA-NO]

This will give you all the [PART-SN] values for each unique [PART FIND NO]. Of course the results of this second query will no longer show unique [PART FIND NO] values, since the same number can potentially be listed for each [PART-SN] -- at least according to what you commented about [PART-SN] having multiple values per [PART-ATA-NO] in Table2.

If your ultimate goal is to merge the two tables, removing duplicates, BUT you have multiple related values like [PART-SN], then from the information I have you will need more than one table: First a primary table to store the unique part numbers, then a one-to-many relationship on a second table for storing duplicate [PART-SN] (and possibly other) values.