Query Returns no results

[rendrag]

Hi smarter people,

I'm working on a light CRM database for work. I've set up the relationships and imported his data from an old DB we were using before and I have an issue that I've posed at a couple other places with some help, but the more ideas the better. I've attached the relationship report from access2007.

1 of the reports I'm trying to assemble is an activity list. It lists the Account the activity was performed on, the contact person at the account, as well as a description and a couple other pieces of information.

Unfortunately the query that Access wrote (via my input through the Query Designer) is not returning any results. I'm hoping someone might be able to understand why.

SELECT DISTINCTROW tblAccount.AccountName, [tblContact.FirstName] & " " & [tblContact.LastName] AS Name, tblActivity.ActivityDate, tblActivity.ActivityTime, tblActivityType.ActivityType, tblActivity.Description, tblContact.Phone
FROM tblActivityType INNER JOIN ((tblAccount INNER JOIN tblActivity ON tblAccount.AccountID = tblActivity.ContactID) INNER JOIN tblContact ON (tblAccount.AccountID = tblContact.AccountID) AND (tblActivity.ContactID = tblContact.ContactID)) ON tblActivityType.ActivityTypeID = tblActivity.ActivityTypeID;

 

[SOS]

You need an OUTER JOIN instead of the INNER JOIN. That way it will select the Accounts and any with Activity.

 

[rendrag]

When I change all the joins to outer joins, it gives me a syntax error in the FROM clause on the Join type

just as an edit, my goal is to have the report group on activity date and only return accounts for which some activity was performed.

 

[MSAccessRookie]

When I change all the joins to outer joins, it gives me a syntax error in the FROM clause on the Join type

just as an edit, my goal is to have the report group on activity date and only return accounts for which some activity was performed.

That is possibly because unlike an Inner Join, an Outer Join must also include a direction specification (Left or Right). In an Outer Join, all records from one side of the Join are selected, and only the matching records from the other side are selected. For Example:

With a Left Outer Join, all records from the Table on the Left side of the Join are selected, and only the matching records from the Right side are selected. A Right Outer Join would be the reverse.
​

Note that you can also shorten the expression to not include the word Outer (Left Join being the same as Left Outer Join, etc.)

 

[rendrag]

well, it looks like the join type is a moot point. when I was rebuilding the query piece by piece, i happened upon the fact that the ContactID in tblActivity is blank. I thought I had mapped that already, but unfortunately I had missed that step.

Sorry for wasting people's time, but I appreciate the education nonetheless.