Report won’t deliver all data stored in table

[amjjam]

Hi,

My Contacts and Companies database includes a basic tabbed form for data
input. The Company tab has a Contacts subform which in turn has a
Communication subform. The communication data gets stored for each contact in
the underlying Communication table. I have a report titled “Communication
History.†It should show name, date, time, type of communication, subject,
etc. for each contact with whom I’ve spoken or corresponded. It’s delivering
information for the first several contacts when I click on the command button
to View Reports and click on Communication History in the reports list. They
all have communication data associated with their contact information in the
underlying table. Then several contacts entered have blank records as far as
communication data is concerned. In other words, no data is associated with
them in the communication table and none shows in the form. Then other
contacts have communication data entered. These contacts, for whom
information was entered in the communication form have data showing for them
in the table and the form, but they don’t show up in the report. Any way to
get their information to list too? When I bring up a report for their
individual communication history, I get all of the data. It’s weird. Any help
is appreciated. ~ amjjam

 

[Fred]

You have a lot going on these so any first attampt at a post is not going to
include all of the needed information.

I'm assuming that your report hase a 3 level heirarchy. Companies, People,
Communications.

Are the "missing people" linked to a company? By "linked," I mean do
they have a CompanyID # (or whatever your company table PK is) placed into
the FK field of that people record?

 

[John W. Vinson]

Hi,

My Contacts and Companies database includes a basic tabbed form for data
input. The Company tab has a Contacts subform which in turn has a
Communication subform. The communication data gets stored for each contact in
the underlying Communication table. I have a report titled “Communication
History.” It should show name, date, time, type of communication, subject,
etc. for each contact with whom I’ve spoken or corresponded. It’s delivering
information for the first several contacts when I click on the command button
to View Reports and click on Communication History in the reports list. They
all have communication data associated with their contact information in the
underlying table. Then several contacts entered have blank records as far as
communication data is concerned. In other words, no data is associated with
them in the communication table and none shows in the form. Then other
contacts have communication data entered. These contacts, for whom
information was entered in the communication form have data showing for them
in the table and the form, but they don’t show up in the report. Any way to
get their information to list too? When I bring up a report for their
individual communication history, I get all of the data. It’s weird. Any help
is appreciated. ~ amjjam

Correct the error in your report's Recordsource query.

For help doing so please open the query in SQL view and post it here. My guess
is that you want a LEFT JOIN instead of an INNER JOIN but you don't give
enough information to be sure.

 

[amjjam]

Hi,

Here’s the query on which the Communication History report is based:

SELECT Contacts.LastName, Contacts.FirstName, Communication.CommDate,
Communication.CommTime, Communication.Subject, Contacts.ContactsID, [Type of
Communication].CommunicationType, [In-coming or Out-going
Communication].InOrOutgoingComm
FROM Contacts INNER JOIN ([In-coming or Out-going Communication] INNER JOIN
([Type of Communication] INNER JOIN Communication ON [Type of
Communication].CommunicationTypeID = Communication.CommunicationTypeID) ON
[In-coming or Out-going Communication].InOrOutgoingCommID =
Communication.InOrOutgoingCommID) ON Contacts.ContactsID =
Communication.ContactsID
ORDER BY Contacts.LastName, Communication.CommDate;

If it needs changing from an inner join to a left join, would you please
explain why. I'm trying to learn the logic behind the programming. Thanks for
your help. ~ amjjam

 

[John W. Vinson]

Hi,

Here’s the query on which the Communication History report is based:

SELECT Contacts.LastName, Contacts.FirstName, Communication.CommDate,
Communication.CommTime, Communication.Subject, Contacts.ContactsID, [Type of
Communication].CommunicationType, [In-coming or Out-going
Communication].InOrOutgoingComm
FROM Contacts INNER JOIN ([In-coming or Out-going Communication] INNER JOIN
([Type of Communication] INNER JOIN Communication ON [Type of
Communication].CommunicationTypeID = Communication.CommunicationTypeID) ON
[In-coming or Out-going Communication].InOrOutgoingCommID =
Communication.InOrOutgoingCommID) ON Contacts.ContactsID =
Communication.ContactsID
ORDER BY Contacts.LastName, Communication.CommDate;

If it needs changing from an inner join to a left join, would you please
explain why. I'm trying to learn the logic behind the programming. Thanks for
your help. ~ amjjam

The default INNER JOIN will return records only if data exists in all four
tables (Contacts, Type of Communication, In-coming or Out-going Communication,
and Communication). That's how an inner join works: if you select the join
line in the query design window you'll see an explanation that it will return
records where both tables match.

You can change this to option 2 or 3 - "Show all records in Contacts and
matching records in <other table>" if you want to see the contact information
even if there is no matching record.

I'm not sure what your Communication and In-coming or Out-going Communication
tables represent, but it sounds like you might have some design issues.

 

[amjjam]

Thanks, John. You were right. I had to change the join property between the
Communication table and the In-Coming or Out-going Communication table and
between the Communication table and the Type of Communication from 1) Only
include rows where the joined fields from both tables are equal to 3) Include
ALL records from ‘Communication’ and only those records from ‘In-Coming or
Out-going Communication’ (or ‘Type of Communication’) where the joined fields
are equal. The query that returns the correct data in the report now reads:

SELECT Contacts.LastName, Contacts.FirstName, Communication.CommDate,
Communication.CommTime, Communication.Subject, Contacts.ContactsID, [Type of
Communication].CommunicationType, [In-coming or Out-going
Communication].InOrOutgoingComm
FROM Contacts INNER JOIN ([In-coming or Out-going Communication] RIGHT JOIN
([Type of Communication] RIGHT JOIN Communication ON [Type of
Communication].CommunicationTypeID = Communication.CommunicationTypeID) ON
[In-coming or Out-going Communication].InOrOutgoingCommID =
Communication.InOrOutgoingCommID) ON Contacts.ContactsID =
Communication.ContactsID
ORDER BY Contacts.LastName, Communication.CommDate;

I appreciate your help. ~ amjjam

Hi,

Here’s the query on which the Communication History report is based:

SELECT Contacts.LastName, Contacts.FirstName, Communication.CommDate,
Communication.CommTime, Communication.Subject, Contacts.ContactsID, [Type of
Communication].CommunicationType, [In-coming or Out-going
Communication].InOrOutgoingComm
FROM Contacts INNER JOIN ([In-coming or Out-going Communication] INNER JOIN
([Type of Communication] INNER JOIN Communication ON [Type of
Communication].CommunicationTypeID = Communication.CommunicationTypeID) ON
[In-coming or Out-going Communication].InOrOutgoingCommID =
Communication.InOrOutgoingCommID) ON Contacts.ContactsID =
Communication.ContactsID
ORDER BY Contacts.LastName, Communication.CommDate;

If it needs changing from an inner join to a left join, would you please
explain why. I'm trying to learn the logic behind the programming. Thanks for
your help. ~ amjjam

The default INNER JOIN will return records only if data exists in all four
tables (Contacts, Type of Communication, In-coming or Out-going Communication,
and Communication). That's how an inner join works: if you select the join
line in the query design window you'll see an explanation that it will return
records where both tables match.

You can change this to option 2 or 3 - "Show all records in Contacts and
matching records in <other table>" if you want to see the contact information
even if there is no matching record.

I'm not sure what your Communication and In-coming or Out-going Communication
tables represent, but it sounds like you might have some design issues.