Create 1 query from two data sets (1 Viewer)

[Geomg]

I am starting working with access and while I know a bit how it works, this new task I am planning to perform is not getting any desired results.


Summary:


The two main data sets have Users ID, Dates, and Product Name which I need to combine. Considering Product Names from Both data sets contains some products the other Data doesn't. that's the reason why I need to combine then together.


Along with those columns, There are other columns That I need to include in the final report. Those fields are independent meaning, they are not related to each other but, they are related to the Product Columns/ID/Date. That means I need to match the remaining columns based on the Product names, User ID, and Dates so I can have one report with 1 field with dates, 1 field with user ID, and 1 field with products Names from both data sets but including all remaining columns.


I have tried creating union with product names, Index with ID - Date - Product name, different joins without results, maybe I am missing something simple.

Data 1 example:

ID	Date	Product	Calls	DC
1	8/31	Poroduct#3	5	5
1	8/28	Product #2	3	6
2	8/27	Product #2	2	8

Data 2 example:

ID	Date	Product	Call Int	PA
1	8/31	Poroduct#4	5	3
1	8/31	Product #3	3	7
2	8/27	Product #2	5	2

Desired Result:

ID	Date	Product	Calls	DC	Call Int	PA
1	8/31	Product#3	5	5	3	7
1	8/28	Product#2	3	6
2	8/27	Product#2	2	8	5	2
1	8/31	Product#4			5	3

 

[theDBguy]

Hi. Welcome to AWF!

Quick question: In Data 1 example, you had two records for Product #2, but in the Data 2 example, you only had one. Can we assume Data 2 will only have unique products, always?

 

[Geomg]

In most of the cases, If the ID have data in the Product in one day from one of the data sets, it will also show data in the second table in the same product. That's the reason why ID 2 from 8/27 and ID 1 from 8/31 have assigned the data from both tables.

But there are some cases where an ID will have data in a product that is no available on the other table.

To make it simple, there are some products that are only available in one of the data sets.

 

[theDBguy]

In most of the cases, If the ID have data in the Product in one day from one of the data sets, it will also show data in the second table in the same product. That's the reason why ID 2 from 8/27 and ID 1 from 8/31 have assigned the data from both tables.

But there are some cases where an ID will have data in a product that is no available on the other table.

To make it simple, there are some products that are only available in one of the data sets.

Hi. Thanks for the additional information; but unfortunately, that didn't answer my question. I get it that Data1 might not contain matching products from Data2 and vice versa, but I wanted to ascertain if one of them (Data1 or Data2) will "always" only have a unique list of products. If so, which one? If not, then we (or at least me) might have a problem.

 

[Geomg]

Not sure if I get sir. But there are not unique values since multiple IDs will have data in each of the products at a certain point. Meaning, We will have duplicated product names in each of the tables but some of them only available in each of the Data set.

As example, Product #4 is only available in Data#2 but multiple Ids will have data related to that product name within that table.