Create 1 query from two data sets

[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

Your Help will be much appreciated!

[ranman256]

make a union query to combine them:
select * from dataset1
union
select * from dataset2


then run the 1 query to pull both sets. NOTE: the fields from both MUST be in the same order with the same data types.

[Micron]

You may not be able to get satisfactory results even with a Union query - depends on what the last shown field is for. If you're using field names to compartmentalize like data (e.g. DC is Washington DC and PA is Pennsylvania, which are both states) then your design is likely all wrong. People who need Union queries are often in that boat. They design their tables like spreadsheets instead of like relational tables should be. If you want to investigate this possibility, research database normalization and see if you have a lack of it.

[orange]

In addition to the advice and comments provided so far, I suggest you tell us in simple, plain English:
-what the application is about ( the business thos database is intended to support)
-what each of the fields means Calls, Call Int, Date, DC, PA...

Access has some reserved words - Date is one- change it to something more meaningful to your business.
Access does not work well for names with embedded spaces or special characters (such as s#,$,-...)
Database design always starts with an overview of the
issue/problem/opportunity to be supported with automation.

[Geomg]

Thanks for the responses!

Hi Orange, sure will!

I was just trying to provide a simple explanation of what I structure of the data and final result should looks like.

Since both Data sets provides performance information from agents( calls, NPS, Counts etc) I need to have one master data set including both performance information based on the Product names, ID and Date to have one single dashboard.

PA or DC is just the name of ones of the existing columns form both data sets that provides counts whenever an Agent or ID - DC=Disconnect call - PA=Program Advised. So this counts the times the agents does it. So far, there are more columns providing simple counts and %(not needed as I will do manual calculations to get those percentages with excel.)

Calls are inbound calls and Calls Int is also capturing inbound calls.


Hope This helps! Thanks again for your support!

[orange]

Helps a little.
Why?? Why 2 fields collecting same info?? ---Calls are inbound calls and Calls Int is also capturing inbound calls.

Please describe in 2 lines simple terms --what this application is about?
It's not - Inventory control, not Human Resources, not a Grocery Store, not a Auto Repair Shop-- so what is it?

You have Agents, and Products and they are related by ??? And the Agent does ??? And a Call may become a Sale???

Look at the topics in the Database Planning and Design link in my signature.
I recommend you work through 1 or 2 of the tutorials(45-60 minutes) from RogersAccessLibrary mentioned in the link. You'll experience a process you can use with your database or any other.

[Geomg]

This is going to be used to track performance, pretty much "Products" are the line of business or LOB.

Data source #1 includes telephony information. Calls field does not includes or removes shorts calls. This database also includes AHT, NPS information, an other telephony KPIs.
Data source #2 comes from voice analytics which includes short calls, this also includes other types of KPIs calculated based on the amount of calls and counts of specific metrics which are analyzed and evaluated by the voice analytics program.

For example: if the agent Greets the customer accordingly. then "Greeting column" will have a count of 1. This specific, data source doesn't care about short calls since we are looking to know if the agent is doing or not doing certain process evaluated by the voice analytics.

This means from all the KPIs which are going to be included in the Master Data set (from both sources) there are some KPIs as AHT that will be calculated based column Calls from Data source #1 and other KPIs from the voice analytics are going to be calculated based on the Call int or Calls count from the Data #2.

So when everything is put together, i'd like to provide the option to filter the performance based on the LOB so I can review the voice analytic and telephony performance of an agent in the specific LOB. This will be done with an excel pivot table for a quicker handling once the data is processed.

Thanks Orange, will review the tutorials!

[June7]

Looks more like a JOIN on ID fields to me instead of UNION. This assumes the corresponding product and date pairs have same ID, otherwise compound join on product and date. Requires one table to serve as "master" that the other table joins too. Master must have all possible ID or combinations of product/date pairs. If not, a UNION then a CROSSTAB might get the desired output.

Query1:
SELECT ID, [Date], Product, Calls AS Data, "Calls" AS Cat FROM Data1
UNION ALL SELECT ID, [Date], Product, DC, "DC" FROM Data1
UNION ALL SELECT ID, [Date], Product, [Call Int], "Call Int" FROM Data2
UNION ALL SELECT ID, [Date], Product, PA, "PA" FROM Data2;

Query2:
TRANSFORM First(DataUNION.Data) AS FirstOfData
SELECT DataUNION.ID, DataUNION.Date, DataUNION.Product
FROM DataUNION
GROUP BY DataUNION.ID, DataUNION.Date, DataUNION.Product
PIVOT DataUNION.Cat;


Why does Date not have year? Date is a reserved word and should not be used as field name.

[ArviLaanemets]

Rename field [Call Int] without space in it, like CallInt.

On fly:

SELECT
   d0.ID, d0.Date, d0.Product, d1.Calls, d1.DC, d2.CallInt, d2.PA
FROM
    (((SELECT DISTINCT * FROM (SELECT d01.ID, d01.Date, d01.Product FROM Data1 d01 UNION SELECT d02.ID, d02.Date, d02.Product FROM Data2 d02) d0)
      LEFT OUTER JOIN Data1 d1 ON d1.ID = d0.ID AND d1.Date = d0.Date AND d1.Product = d0.Product)
      LEFT OUTER JOIN Data2 d2 ON d2.ID = d0.ID AND d2.Date = d0.Date AND d2.Product = d0.Product)

It's assumed, the combination of ID, Date and Product is unique for both source tables. At same time, the combination may be present only in one table, or in both of them.