Getting similar data from two tables

[wheddingsjr]

Hi all



I have two tables with the same columns. I want to create a new table with all of the columns, as long as data in three of the columns are the same in both tables (i.e., same member #, same date, and same code). Any assistance would be appreciated. Thanks

[ranman256]

design the table with the fields, (copy and paste from existing tables) , save
run append query 1 to add table1
run append query 2 to add table2

[wheddingsjr]

I dont think I am explaining myself correctly because that doesnt seem to be what I am looking for. I will set up an example and paste

[Bob Fitz]

Hi all

I have two tables with the same columns. I want to create a new table with all of the columns, as long as data in three of the columns are the same in both tables (i.e., same member #, same date, and same code). Any assistance would be appreciated. Thanks

Do you actually need a new table? You could create a query to return matching records

[Gicu]

As Bob said, simply create a query (add both tables in Query design and join the three fields together with equijoins then add the rest of the columns. If you really need a third table simply convert the new query from a Select into a Make-Table query.

Cheers,
Vlad

[wheddingsjr]

Hi Bob

You're right, I dont really need to create a table just the dataset. Here is an example. In the Prof tab there are records that actually have the same data as surgical tab. The results tab is my desired results. Mind you there are another 15 columns, but as long as the MEMNO, the BEGDATE, and the SERVCODN match, I want those to be in the new dataset/table. Ugghh, I dont see where to attach my example

[wheddingsjr]

Thanks Vlad

I tried that, but dont I have to add the rest of the columns using one of the tables? When I do that I just get double the data of which ever table I use

[orange]

Perhaps you should post a copy of the database along with some descriptive text to tell what the database is about; the business this database is intended to support, and the rationale for the tables you have designed.

Good luck. Sounds interesting.

[wheddingsjr]

Hi Orange...I dont know how to attach...but Bob and Vlad are right on the head. Thats exactly what I want, however, when I put both tables into the design view, make my joins, when I hit RUN I get the message that "Query must have at least one destination field", and if I pull the fields down from either of the two tables, I dont get the mix that I am looking for, it just appears that whatever table I use is in the data. I know this because there is another field that differentiates the two tables from one another.

[orange]

Re Attaching to a post
https://www.accessforums.net/faq.php...b3_attachments

[Gicu]

Hi there,

In the query design interface you can double click the fields listed in both tables and they will go down as columns in your query. You can choose fields from both tables. The only way you would get duplicates is if the three field combination is not unique (meaning the same member number and code have multiple records in either table with the same date.

Cheers,
Vlad

[wheddingsjr]

It wont let me attach...but its ok..I will do it manually. I know to set up the two tables and join them...all I need to know is how do I run the query when I have no fields beneath the two tables.

[wheddingsjr]

Thanks again Vlad..But I give up..I have done everything you suggested..I have the 2 tables...I joined the three fields...I highlighted all the other fields and moved them down to the query and ran it...One table has a field called Surgical and the other has a field called Prof. If I use the Prof table to add my fields to the query, all of the reults will be PROF, if I use the Surgical table to bring down the fields into the query, the results all have Surgical lines...there should be one of each...One from both tables

[Gicu]

Not sure I follow you about the Prof and the Surgical.... Could you try to create a new Access file with just the two tables in question (please "sanitize" the data if it contains any important, confidential or otherwise sensitive info, only few records with dummy info would do) and upload that file so we could have a look?

Cheers,
Vlad

[wheddingsjr]

The site wont allow me to attach anything. It keeps crashing on me. Here are the two tables and the expected results.

MEMNO	BEGDATE	SERVCODN	TYPE
146500	05/12/17	45380	PROF
147800	09/26/17	45378	PROF
148000	05/03/17	29826	PROF
148000	05/03/17	29826	PROF
148200	06/06/17	25000	PROF
148300	02/21/17	45385	PROF
148300	02/21/17	45385	PROF
149400	05/26/17	45385	PROF
149400	05/26/17	45385	PROF
149500	06/16/17	29826	PROF
170201	05/01/17	45378	PROF
170700	03/10/17	45380	PROF
170700	03/10/17	45380	PROF
171500	03/06/17	45380	PROF
171500	03/06/17	45380	PROF
996601	02/23/17	45378	PROF
997001	11/07/17	45378	PROF
997001	11/07/17	45378	PROF
997100	11/20/17	45380	PROF
MEMNO	BEGDATE	SERVCODN	TYPE
148200	06/06/17	25000	SURGICAL
1111111	04/21/17	22222	SURGICAL
2222222	04/28/17	33333	SURGICAL
3333333	05/15/17	44444	SURGICAL
4444444	07/17/17	55555	SURGICAL
5555555	08/08/17	66666	SURGICAL
170201	05/01/17	45378	SURGICAL
666666	03/27/17	77777	SURGICAL
777777	02/02/17	88888	SURGICAL
888888	09/18/17	99999	SURGICAL
996601	02/23/17	45378	SURGICAL
Expected Result
MEMNO	BEGDATE	SERVCODN	TYPE
148200	06/06/17	25000	SURGICAL
148200	06/06/17	25000	PROF
170201	05/01/17	45378	SURGICAL
170201	05/01/17	45378	PROF
996601	02/23/17	45378	SURGICAL
996601	02/23/17	45378	PROF