Data inSubreport only shows first contact 1

[AlanJordan]

I have a database with the tables Companies and Contacts (1 to many relationship between these.)

I have a report that I built that provides all of the information needed, but ONLY for the first contact.

I've tried creating a subreport and linking the two by CompanyID. I still get only one contact.

I also tried this query.

SELECT Contacts.DateUpdated, [b]Contacts.DateUpdated[/b], Companies.CompanyID, Companies.Company, Companies.Category, Companies.Address, Companies.City, Companies.State, Companies.Zip, Companies.Phone, Companies.Fax, Companies.Website, Companies.Comments, Companies.PersonCalling
FROM Companies INNER JOIN Contacts ON Companies.CompanyID = Contacts.CompanyID
WHERE (((Contacts.DateUpdated)>=[EnterFirstDate] And (Contacts.DateUpdated)<=[EnterLastDate]));

Suggestions will be most appreciated.
Thanks,
Alan

 

[dhookom]

Where did you try the query? Is that the record source of the subreport or main report? What is the record source of each report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AlanJordan]

Thank you for your response. Here is the SQL for the record source for Companies, the parent form.

SELECT Contacts.DateUpdated, Contacts.DateUpdated, Companies.CompanyID, Companies.Company, Companies.Category, Companies.Address, Companies.City, Companies.State, Companies.Zip, Companies.Phone, Companies.Fax, Companies.Website, Companies.Comments, Companies.PersonCalling
FROM Companies INNER JOIN Contacts ON Companies.CompanyID = Contacts.CompanyID
WHERE (((Companies.Company) Like [EnterCompanyName] & "*"));

Here is the SQL of the record source for the Contact (child) form:

Contacts table.  (Not based on a query.)

 

[dhookom]

Are you objects Forms or Reports? This is a Reports forum so I assumed "reports". I'm not sure why you would have the contacts table in the main report since they would be displayed in the subreport. Placing the contacts in the main report might generate a Link Master/Child of ContactID rather than CompanyID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AlanJordan]

They are reports. My mistake. Thank you. Yes, when took out the reference to contacts in the main report's SQL that seemed to do the trick.

Thank you.