joining tables question

[GingGangGoo]

I have been successfully building and using select queries for awhile, but now I am stumped. I have a the following query, Capture1. The result is what I want, a record per contact including activity time. In the report I have based on this, I can then see each contact once with all their activity time totaled. So far so good. I have another table where I match up Contacts with surveys they have completed, including the completion date and if a particular survey, also the score. If I query this as I do the contacts/activity hrs, again I would get one record per contact including survey data.
My challenge is, in the report, I need to have each of those reports listed, not summed. So that my report would look something like this:
Column Column ... etc
Name Birthdate
survey 1/ completed / score
survey 2/completed / score

Ok, when I add the related table first query, related by contact ID, (Capture2) I get only contacts who have a survey, but I need to see every contact from the first even if they don't have any surveys. I hope that makes sense. I want the end result to be a table that shows me every contact where some will have surveys and some not.

I have experimented with making the 1st query, Capture1, into a make table query, and then creating a second append query for the survey related data, to append records to the new table. That works, however, I realized that the make table query is now a local table, not a linked one (all my tables are linked to my Access web app), so it won't automatically update and I'm not sure this is the most efficient way to get what I need.

Can someone advise please.

Thanks

 

[boerbende]

Click on the join line between the two tables and select the join type you want
(all the records from table1 and only those for table2 where the records are equal)

 

[GingGangGoo]

Click on the join line between the two tables and select the join type you want
(all the records from table1 and only those for table2 where the records are equal)

Oh boy! That was simple and I feel a big "doh" for not thinking of that.
One thing, now where ever the added columns are null, it says #deleted.

 

[boerbende]

hmm. That does not ring a bell.
I know I see #deleted when I run a query and delete some records from the table directly. Without refreshing the query it shows #deleted
Maybe somebody else knows

 

[GingGangGoo]

Okay, I solved it! I created 4 queries
Q1 - brings together the 3 join tables, contacts, activitycontacts & activity hrs
Q2 - brings together other 3 join tables, contacts, surveys & contactsurveys
Q3- using Q1, and adding a group/sum, I sum the activity hrs, and include all the info I need from Q1
Q4 - the one my report is based on brings together Q2 & Q3 and has the join getting all records from Q1 and only those from Q3 where join fields are equal.

This set up eliminates falsely duplicating activity hrs where ever a contact has more than one survey.