Am I dealing with a cartesian product? Help?!!!

[Mr_Si]

Hi all,

it's been a while since I've been on here, but I'm having an issue with what I think is a cartesian product problem, as I'm dealing with a "many" relationship

Basically, for a report, I'm trying to have a single page for each contact with ALL of their phone numbers on this single page.

The query I currently have, displays a record for each phone number so that there are multiple entries for some contacts.

I've attached the print screens of the output and design for the query below, and also the SQL code.

What I NEED, is to be able to return one record with all the telephone numbers in one record under the heading of the contact so that there is only 1 record per contact. If you catch my drift...

Output:

Design:

SQL Code:

SELECT qryContact.idsContactID, tblTelNo.idsTelNoID, tblTelNo.lngzTelNoType
FROM qryContact LEFT JOIN (tblTelNoType RIGHT JOIN (tblTelNo RIGHT JOIN tblContactTelLink ON tblTelNo.idsTelNoID = tblContactTelLink.lngzTelNoID) ON tblTelNoType.idsTelNoTypeID = tblTelNo.lngzTelNoType) ON qryContact.idsContactID = tblContactTelLink.lngzContactID;

I look forward to hearing replies!!!

Sometimes I hate the implications of normalisation.

Thank you in advance.

 

[Rabbie]

Your query seems to be working correctly. What you need to do is to use the Grouping facility in your report to group the Phone numbers for each contact under that contact. You can set up each Contact group to be output on a new page if that is what you want/need.

 

[Mr_Si]

Oh right, thanks Rabbie!

This is actually a simplified version of the report I want to make.

Is it possible to put a report inside a report? So I could make a report for the contact (this will include emails and websites etc too) and then put this as a sub-report into the "Enquiry" report and link via common fields?

 

[Rabbie]

Access supports subreports in a similar way to forms/subforms so you should be able to do exactly what you want.