Report not displaying records that don't have children

[thegetch1]

Hi,

I have 3 tables.

Project - pk = Intake ID
Project Resources = pk = Intake ID, Resource ID, Resource Name
Resources - pk = Resource ID

Project Resources is the composite of the other two.

My problem is that when I run a report based on a query that is to
select everything from all the tables, the report only returns projects
that have resources. i.e. If there are no resources assigned to the
project, it doesn't show up. This is a problem because there is a lot
of data other than the resources that need to be reported on.

Query code:

SELECT
[IPS Projects].[IPS Intake ID], [IPS Projects].[Project Name], [IPS
Projects].[Project Code #], [IPS Projects].[Mantis Ticket #], [IPS
Projects].[Registration Type], [IPS Projects].Status, [IPS
Projects].[Program Name], [IPS Projects].[Program #], [IPS
Projects].[Service Request Type], [IPS Projects].[Project Sponsor],
[IPS Projects].[Sponsor Department], [IPS Projects].[Customer Name],
[IPS Projects].[PM Name], [IPS Projects].[RFS Completed (Charter)?],
[IPS Projects].[Project Assessment Checklist Completed?], [IPS
Projects].[Registered with IPS?], [IPS Projects].[Registered as an
Exception?], [IPS Projects].[Emergency Registration Requested], [IPS
Projects].[IPS Triage Completed], [IPS Projects].[Scheduled for IPS
Intake Meeting], [IPS Projects].[Date scheduled for IPS Intake
Meeting], [IPS Projects].[Project Approved for RCS Completion], [IPS
Projects].[Department Responsible for IRC], [IPS Projects].[IPS
Planning Coordinator], [IPS Projects].[RCS Target Date], [IPS
Projects].[Phase of Project], [IPS Projects].[Implementation Date],
[IPS Projects].[Infrastructure Project Manager Assigned], [IPS
Projects].[RCS - Delivery Date], [IPS Projects].[Infrastructure Stage
Gate 1 Complete], [IPS Projects].[IPS Intake Completed], [IPS
Projects].[All Resources Assigned?], [IPS Projects].[All Resource names
are in PlanIT], [IPS Projects].Comments, Resources.[Resource Service],
[Project Resources].[Resource Name]

FROM Resources INNER JOIN ([IPS Projects] INNER JOIN [Project
Resources] ON [IPS Projects].[IPS Intake ID] = [Project Resources].[IPS
Intake ID]) ON Resources.[Resource ID] = [Project Resources].[Resource
ID]

WHERE ((([IPS Projects].[Project Name])=[Forms]![Project
Lookup]![projLookupCombo]));


Except for the numerous fields, it should be a pretty simple query. Any
thoughts?

Getch

 

[thegetch1]

I might also mention that the combo box is from an input form for the
report which works perfectly for projects that do have resources,
(tested by printing out what it was passing to the report and it is
passing that right variable), so I don't think the problem is there.

 

[Van T. Dinh]

Check the joins you used in the Query / SQL being used as the RecordSource
for the Form.

From what you described, the joins should be Outer Joins.

Try changing the FROM clause to:
....
FROM ( [IPS Projects] LEFT JOIN [Project Resources]
ON [IPS Projects].[IPS Intake ID] = [Project Resources].[IPS Intake ID] )
LEFT JOIN Resources
ON [Project Resources].[Resource ID] = Resources.[Resource ID]
....

--
HTH
Van T. Dinh
MVP (Access)

Hi,

I have 3 tables.

Project - pk = Intake ID
Project Resources = pk = Intake ID, Resource ID, Resource Name
Resources - pk = Resource ID

Project Resources is the composite of the other two.

My problem is that when I run a report based on a query that is to
select everything from all the tables, the report only returns projects
that have resources. i.e. If there are no resources assigned to the
project, it doesn't show up. This is a problem because there is a lot
of data other than the resources that need to be reported on.

Query code:

SELECT
[IPS Projects].[IPS Intake ID], [IPS Projects].[Project Name], [IPS
Projects].[Project Code #], [IPS Projects].[Mantis Ticket #], [IPS
Projects].[Registration Type], [IPS Projects].Status, [IPS
Projects].[Program Name], [IPS Projects].[Program #], [IPS
Projects].[Service Request Type], [IPS Projects].[Project Sponsor],
[IPS Projects].[Sponsor Department], [IPS Projects].[Customer Name],
[IPS Projects].[PM Name], [IPS Projects].[RFS Completed (Charter)?],
[IPS Projects].[Project Assessment Checklist Completed?], [IPS
Projects].[Registered with IPS?], [IPS Projects].[Registered as an
Exception?], [IPS Projects].[Emergency Registration Requested], [IPS
Projects].[IPS Triage Completed], [IPS Projects].[Scheduled for IPS
Intake Meeting], [IPS Projects].[Date scheduled for IPS Intake
Meeting], [IPS Projects].[Project Approved for RCS Completion], [IPS
Projects].[Department Responsible for IRC], [IPS Projects].[IPS
Planning Coordinator], [IPS Projects].[RCS Target Date], [IPS
Projects].[Phase of Project], [IPS Projects].[Implementation Date],
[IPS Projects].[Infrastructure Project Manager Assigned], [IPS
Projects].[RCS - Delivery Date], [IPS Projects].[Infrastructure Stage
Gate 1 Complete], [IPS Projects].[IPS Intake Completed], [IPS
Projects].[All Resources Assigned?], [IPS Projects].[All Resource names
are in PlanIT], [IPS Projects].Comments, Resources.[Resource Service],
[Project Resources].[Resource Name]

FROM Resources INNER JOIN ([IPS Projects] INNER JOIN [Project
Resources] ON [IPS Projects].[IPS Intake ID] = [Project Resources].[IPS
Intake ID]) ON Resources.[Resource ID] = [Project Resources].[Resource
ID]

WHERE ((([IPS Projects].[Project Name])=[Forms]![Project
Lookup]![projLookupCombo]));


Except for the numerous fields, it should be a pretty simple query. Any
thoughts?

Getch

 

[thegetch1]

You guys on here are good! Works perfectly. Now, for this query, I just
did it in design mode. Why would Access incorrectly join the tables if
they are set up correctly in the relationship view?

Thank you so much!!

Getch

 

[Van T. Dinh]

It seems to default correctly, i.e. according to the relationship set in
relationship window in the DesignView of a new Query in A2003.