Joining data from 2 tables

[janvk]

Dear,

I have created a tables users with userID and have 2 tables with 1-to-many relations to userID:
tblAvailableHours (Fields userid - date - availablehours)
tblWorkedHours (fields userid-date-workinghours)

In both tables a have a date field. I would like to create a query to get as a result per row :
UserID - Date - AvailableHours - Workedhours

So eventually i can perform differenced between availablehours and workinghours.

I made 2 querys to join users to tblavailablehours and 1 query to join users to tblworkedhours.
I then created a new query in wich i put the 2 earlier made query linked the userid fields set relation type to 1 then added a relation type to dates with the take al the hours of tblavilablehours but it states i have faulte join.

Could someone point me to the solution or what i am missing ??

 

[sneuberg]

I tried to simulate your problem in the attached database and I'm not getting this error. Please look at this database and tell us how you are doing this differently. Or you could put your database in a zip file and upload it and I'll see if I can figure out what your problem is.

 

[janvk]

Thx snueberg,

I have looked at your DB and my problem is that i have more dates in table availablehours than in workedhours.

Users are available but they don't work every available date.
So when i add the date of today 9-6-2016 it doesn't show up in the combine query. When i change the second relation (between the dates) to on-to-many i get the same error i'm getting.

Hope this helps other wise i have to rebuild my database because it's in an other language.

 

[sneuberg]

When i change the second relation (between the dates) to on-to-many i get the same error i'm getting.

If you mean an outer join where it would show all records in from the available hours query both joins have to be the same. So I suggest trying it with Option 2 in the Join Properties "Include ALL records from ...." It should look like:

 

[janvk]

Haven't had much time tested in your example and it does exactly what i want. Tonight i will test it in my envorinment i'll post back as soon as possible