Two tables, many to many relationship, append fields

[Peterz7]

Hi everyone. Relatively new Access user, first time poster.


I have two tables with a many to many common field that I'm linking and want to append two unique fields from the second to the first. However, when create a query, what is happening is that my original table ends up containing duplicate records in the query result set. Obviously my simple JOIN is not doing what I want for it to do.

I've attached a sample database of my issue. TableA contains financial records, TableB contains two fields I'd like to add to my result set. I've included a TableCDesiredResult table to show what I'm expecting to happen. By running Query1 you will see that while the 2 fields and values I'd like to append are added, my expected record count goes from 29 to 43. My production database contains over 500k records so this really becomes a problem.

I believe I need a more complex or proper JOIN to accomplish this. Can anyone provide any suggestions or guidance on how I can move forward?
Thanks so much for any and all suggestions.
Regards,
Peter

[June7]

A many-to-many relationship requires a third table - a 'junction' table - that associates records of the other two tables. Form design options are:

1. single form with comboboxes to select record from each of the other tables

2. main form bound to table 1 and subform bound to junction table with combobox to select record from table 2

3. main form bound to table 2 and subform bound to junction table with combobox to select record from table 1

[aytee111]

Never repeat data and all tables to contain one subject only. There are fields repeated all over the place. You need a table where rx_number is unique, a table of products with the productID only carried to other tables, a delivery method table, a last name table. Then a transaction table(s) to join them all together. See here for some really good advice on learning how to create a database (bottom of post #7): https://www.accessforums.net/showthread.php?t=65906

[Peterz7]

Thanks for your suggestions. I will look into junction tables and how that might solve my issue.

[Peterz7]

Thanks for replying. Unfortunately, these are not tables I created, but rather canned data extracts from a national, off-the-shelf pharmacy software package my firm uses. Their application does not supply all the analytics management would like to see, so I utilize three separate data extracts from the application, that I then import into an MS Access database and run queries and produce reports. I'm limited to these files at this point.
Appreciate your willingness to help.

[aytee111]

The records are duplicated due to the non-unique field rx_number. For instance, tbl A has two records with 2212436. So does tbl B. So there are 4 records:
tblA record 1/tblB record 1
tblA record 1/tblB record 2
tblA record 2/tblB record 1
tblA record 2/tblB record 2

[aytee111]

Add the word DISTINCT to your query:
SELECT DISTINCT ....
Replace the "*" by all the field names.

[Peterz7]

This worked! Thank you so much for your help. It works perfectly in the simple sample I provided and gives me encouragement to put in the work to try it in our much more complex and data-laden production environment.
You're awesome! Thanks.