Display all records from table regardless of relationship

[gmazza via AccessMonster.com]

Hi there,
I am trying to display all records in a table on a report. So RecordSource
for report is PatientData.
PatientData - columns:
PatientID - Primary Key - autonumber
CriteriaID - Foreign Key
CriteriaOptionID - FK

This table has only FK's from other tables and I need the other tables actual
values.
Thats ok though because I can just grab the values based on the report's
RecordSource table, PatientData.

Problem is it only displays records in the table that actual values in the
fields, so not null fields.
Some CriteriaID's are null in the PatientData table, but I still want them
showing up in the report as blank, but its not showing because a related
record is not in the actual Criteria table, due to my relationships set up.

So how do I get it to display all records regardless?
Thanks for your help!

 

[KARL DEWEY]

Post the query you are using as source for your report.

 

[Marshall Barton]

I am trying to display all records in a table on a report. So RecordSource
for report is PatientData.
PatientData - columns:
PatientID - Primary Key - autonumber
CriteriaID - Foreign Key
CriteriaOptionID - FK

This table has only FK's from other tables and I need the other tables actual
values.
Thats ok though because I can just grab the values based on the report's
RecordSource table, PatientData.

Problem is it only displays records in the table that actual values in the
fields, so not null fields.
Some CriteriaID's are null in the PatientData table, but I still want them
showing up in the report as blank, but its not showing because a related
record is not in the actual Criteria table, due to my relationships set up.

That's not an issue with your relationships. Your query
just needs to use an outer join. In the query designer,
right click on the line connectiong the two tables and
select the type of result you need.

 

[gmazza via AccessMonster.com]

I've tried that, everytime I do and run my query I get an error saying SQL
statement could not be executed because it contains ambiguous outer joins.

I am trying to display all records in a table on a report. So RecordSource
for report is PatientData.

[quoted text clipped - 13 lines]

showing up in the report as blank, but its not showing because a related
record is not in the actual Criteria table, due to my relationships set up.

That's not an issue with your relationships. Your query
just needs to use an outer join. In the query designer,
right click on the line connectiong the two tables and
select the type of result you need.

 

[gmazza via AccessMonster.com]

SELECT PatientData.PatientId, PatientData.ClinicalTrialId, PatientData.
CriteriaId, PatientData.CriteriaOptionId, PatientData.SeverityOptionId,
Criteria.CriteriaValue
FROM ((PatientData INNER JOIN Criteria ON PatientData.CriteriaId = Criteria.
CriteriaId) INNER JOIN CriteriaOption ON (PatientData.CriteriaOptionId =
CriteriaOption.CriteriaOptionId) AND (Criteria.CriteriaId = CriteriaOption.
CriteriaId)) INNER JOIN SeverityOption ON (SeverityOption.SeverityOptionId =
PatientData.SeverityOptionId) AND (Criteria.CriteriaId = SeverityOption.
CriteriaId)
WHERE (((PatientData.ClinicalTrialId)=getactivestudy()));

Post the query you are using as source for your report.

Hi there,
I am trying to display all records in a table on a report. So RecordSource

[quoted text clipped - 17 lines]

So how do I get it to display all records regardless?
Thanks for your help!

 

[KARL DEWEY]

You havetoo many joins that serve no purpose. They just go in circles.
What kind if function is getactivestudy() and why use a function as
criteria?

Try this --
SELECT PatientData.PatientId, PatientData.ClinicalTrialId,
PatientData.CriteriaId, PatientData.CriteriaOptionId,
PatientData.SeverityOptionId, Criteria.CriteriaValue
FROM PatientData LEFT JOIN Criteria ON PatientData.CriteriaId =
Criteria.CriteriaId;

SELECT PatientData.PatientId, PatientData.ClinicalTrialId, PatientData.
CriteriaId, PatientData.CriteriaOptionId, PatientData.SeverityOptionId,
Criteria.CriteriaValue
FROM ((PatientData INNER JOIN Criteria ON PatientData.CriteriaId = Criteria.
CriteriaId) INNER JOIN CriteriaOption ON (PatientData.CriteriaOptionId =
CriteriaOption.CriteriaOptionId) AND (Criteria.CriteriaId = CriteriaOption.
CriteriaId)) INNER JOIN SeverityOption ON (SeverityOption.SeverityOptionId =
PatientData.SeverityOptionId) AND (Criteria.CriteriaId = SeverityOption.
CriteriaId)
WHERE (((PatientData.ClinicalTrialId)=getactivestudy()));

Post the query you are using as source for your report.

Hi there,
I am trying to display all records in a table on a report. So RecordSource

[quoted text clipped - 17 lines]

So how do I get it to display all records regardless?
Thanks for your help!

 

[Marshall Barton]

From what you've posted, I agree with Karl. His reduced
query with the single Left Join is what I was driving at so
give thet a try.
--
Marsh
MVP [MS Access]

I've tried that, everytime I do and run my query I get an error saying SQL
statement could not be executed because it contains ambiguous outer joins.

I am trying to display all records in a table on a report. So RecordSource
for report is PatientData.

[quoted text clipped - 13 lines]

showing up in the report as blank, but its not showing because a related
record is not in the actual Criteria table, due to my relationships set up.

That's not an issue with your relationships. Your query
just needs to use an outer join. In the query designer,
right click on the line connectiong the two tables and
select the type of result you need.