combine two datasets by ID and Date

Question

i want to combine two datasets that look like this:

ID	Date	X
1234	01.01.2022	15
1235	02.01.2022	244

ID	Date	Y
1234	01.01.2022	7
1235	04.01.2022	18

into one that looks like this

ID	Date	X	Y
Number	dd.MM.YYYY	Number	Number
1234	01.01.2022	15	7
1235	02.01.2022	244	0
1235	04.01.2022	0	18

Sometimes for a certain date, there might not be an X and a Y value

in the end, I need a table/chart that allows me to filter for one ID and gives me all the X and Y data for said ID depending on their dates (months).

I am really new to the whole Access thing and am learning by doing. Couldn't find a way to do this by Googling, so any help is appreciated.

Answer 1

First, Use a union query to combine multiple queries into a single result:

Select ID, [Date], X As X1, 0 As Y2 From Dataset1
Union All 
Select ID, [Date], 0 As X1, Y As Y2 From Dataset2

Save it as DatasetU.

Next, use this in an aggregating query:

Select 
    DatasetU.ID, 
    DatasetU.[Date], 
    Sum(DatasetU.X1) AS X, 
    Sum(DatasetU.Y2) AS Y
From 
    DatasetU
Group By 
    DatasetU.ID, 
    DatasetU.[Date]

Output: