Querying Unrelated Tables into One Report

[JohnLute]

I have 3 tables of data that I need to query into a single report.

1st table:
tblRevisions
Fields:
PK numRevisionID
EffectiveDate
IssueNumber
Revision

This table is in a 1-to-Many relationship with:
tblRevisionsProfiles
Fields:
PK numRevisionID
PK txtProfileID

The 3rd table is:
tblProfilesRevisions
Fields:
PK txtProfileID
EffectiveDate
IssueNumber
Revision

Please note that tblRevisions and tblProfilesRevisions contain the identical
fields:
EffectiveDate
IssueNumber
Revision

I need to query these 3 fields from the two tables into one report for each
txtProfileID record.

I've noodled with this until my head exploded!

Is there a way to query these unrelated tables of data?

Thanks in advance for your help!

 

[Larry Linson]

As you have, in your question, defined the relationships, why do you call
these "unrelated" tables? This appears to be a typical many-to-many
relationship between tblRevisions and tblProfilesRevisions with a "junction"
or "intersection" table, tblRevisionsProfiles.

If by "identical fields" you imply that the contents of those three fields
are identical, then be aware that when you store redundant data, sooner or
later supposedly-identical fields are going to turn up actually-different
and then you have a conflict-resolution issue with which to deal.

Thus, I have to assume that you want the three fields from _each_ of the
tables in which they appear to be on the Report. From your discussion, it
appears that you want a report of the Records in tblProfilesRevisions,
showing all the related records from tblRevisions. That should not be
difficult.

In the Query Builder, add the Tables tblProfilesRevisions,
tblRevisionsProfiles, and tblRevisions. Click field txtProfileID in
tblProfilesRevisions, drag and drop on field txtProfileID in
tblRevisionsProfiles, then click the join line to highlight it, right-click
it and choose Join Properties, and choose All Records from
tblProfilesRevisions and only those that match from tblRevisionsProfiles.

Then click field numRevisionID in tblRevisionsProfiles drag to field
numRevisionID in tblRevisions and drop. Again click the Join line to
highlight it, right-click and select Join Properties. Choose All Records
from tblRevisionsProfiles and only those that match from tblRevisions.

Drag down the key fields and the three fields of interest from both Tables.

Use this as the RecordSource of your Report. For ease of use, I'd group by
txtProfileID.

NOTE: this does not identify "mismatches," e.g., a Revision with no matching
Profile. Your relationships and referential integrity, or other queries and
reports will have to handle that situation. I suggest you carefully review
the design to determine if the fields are, in fact, redundant and revise, if
so. But, despite the Field names, it is possible that they are not redundant
but refer in one case to the EffectiveDate, IssueNumber, and Revision of the
Revision and in the other to the EffectiveDate, IssueNumber, and Revision of
the Profile.

Good luck with your project.

Larry Linson
Microsoft Access MVP

 

[JohnLute]

Thanks for the very detailed and clear response, Larry!

Considering your comments I've made some design changes that have simplified
some things.

Here now is my subreport SQL:
SELECT tblProfilesRevisionsProfiles.txtProfileID,
tblProfilesRevisions.txtProfileID, tblProfilesRevisions.EffectiveDate,
tblProfilesRevisions.IssueNumber, tblProfilesRevisions.Revision
FROM tblProfilesRevisions LEFT JOIN tblProfilesRevisionsProfiles ON
tblProfilesRevisions.numProfilesRevisionsID =
tblProfilesRevisionsProfiles.numProfilesRevisionsID
ORDER BY tblProfilesRevisions.EffectiveDate DESC;

This returns all of the expected data however I'm now faced with the
challenge of linking this with the main report. The main report contains
txtProfileID in its SQL and when I link this as the Master to the Child
tblProfilesRevisionsProfiles.txtProfileID the subreport returns those records
accordingly however records from tblProfilesRevisions.txtProfileID do not.
When I use tblProfilesRevisions.txtProfileID as the Child then those records
return but the records from tblProfilesRevisionsProfiles.txtProfileID do not.

I've used the subreport field linker to link the Master txtProfileID to BOTH
tblProfilesRevisions.txtProfileID and
tblProfilesRevisionsProfiles.txtProfileID. However when I do this then the
subreport returns blank.

How can I resolve this?

Thanks!!!