Should I be using a subreport? 1

[AlanJordan]

I have a data entry form Companies, with a subform Contacts.
In the subform I have a place for each contact name. I also have a field MayCallBack, into which a date is entered.

I am able to generate a report with this SQL:

SELECT Contacts.LastName, Contacts.FirstName, Contacts.Phone, Contacts.MayCallBack, Contacts.HowUseHS
FROM Contacts
WHERE (((Contacts.MayCallBack) Is Not Null))
ORDER BY Contacts.MayCallBack DESC;

The problem is that I would also like to include the CompanyName. I'm not sure how to do this because when I create this SQL, I get an empty dataset.

SELECT Companies.Company, Companies.MayCallBack, Contacts.LastName, Contacts.FirstName, Contacts.Phone, Contacts.HowUseHS
FROM Companies INNER JOIN Contacts ON Companies.CompanyID = Contacts.CompanyID
WHERE (((Companies.MayCallBack) Is Not Null))
ORDER BY Companies.MayCallBack DESC;

I have a feeling that I need to build a subquery, but I'm not sure how to proceed.

Any suggestions?

 

[Lilliabeth]

How many records in the Companies table have the same CompanyID as records in the Contacts table?

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[AlanJordan]

Hi Lilliabeth,

Thanks for your interest.

Three.

Alan

 

[Lilliabeth]

Of those three, how many have a value present in the MayCallBack field?

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[AlanJordan]

Correction:

There are three entries in the Contacts table that are populated with a WillCallBackDate.

When I do a manual check to find the entry in the Companies table, there are not there.

That is, of course, why the query returns no results. It turns out that this database links CompaniesID to SortID, to establish a many-to-many relationship since data was imported from different places.

I made a query, and funded the report:

SELECT Companies.Company, Contacts.FirstName, Contacts.LastName, Contacts.MayCallBack, Companies.HowUseHS, Contacts.Phone AS DirectPhone, Companies.Phone AS CompanyPhone
FROM Companies INNER JOIN Contacts ON Companies.CompanyID = Contacts.SortID
WHERE (((Contacts.MayCallBack) Is Not Null));

Thank you for helping me to find this.


Alan