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

advertisements

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


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