Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Should I be using a subreport? 1

Status
Not open for further replies.

AlanJordan

Programmer
Sep 15, 2005
139
US
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:
Code:
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.

Code:
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?

 
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?-
 
Hi Lilliabeth,

Thanks for your interest.

Three.

Alan
 

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?-
 
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:

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top