Many To Many DB Report with Sub Report Duplication Problem

[bliesveld]

DB Background:

I have a database with a many to many relationship created by using a referance table. For Exampel:

Table 1: Projects
Table 2: Contacts
Table 3: Project_Contact_Relationships

All relationships have been created.

Each Projcet Has Many Contacts.
Each Contact can be associated with Many Projects.

I have this implemented an wroking fine, my problem come when I try and create a report with a sub-report!

I ceated a parent report based on a qurey of the Projects Table that limits and orginizes the data I want in the report.
I have a sub-report based on a qurey that dose the same thing for Contacts but it also has the Project_ID info from the Project_Contact_Relationships Table.

I then related the Parent to the Child like so: Parent_ID -> Project_Contact_Relationships.Project_ID

The problem is I get multipule listings of the Parent form Data so my report looks somthing like this:

Project A
Contact 1
Contact 2
Contact 3
Contact 4

Project A
Contact 1
Contact 2
Contact 3
Contact 4

Project A
Contact 1
Contact 2
Contact 3
Contact 4

Project A
Contact 1
Contact 2
Contact 3
Contact 4

Project B
Contact 1
Contact 4
Contact 5
Contact 8

Project B
Contact 1
Contact 4
Contact 5
Contact 8

How can I get the project to show just once?

-Thanks

 

[sonny]

Post your sql qry for the sub report and I will look...

 

[bliesveld]

Kind of Messy:

SELECT TBL_Contacts.Contact_ID, TBL_Contacts.Contact_First_Name, TBL_Contacts.Contact_Last_Name, TBL_Contacts.Contact_Company_Assc, TBL_Contacts.Contact_Note, TBL_Contacts.Contact_Reel_Sent_Date, TBL_Contacts.Contact_Reel_Sent, TBL_Contacts.Contact_Phone, TBL_Contacts.Contact_Iclude_In_SR, [TBL_Contacts.Contact_First_Name] & " " & [TBL_Contacts.Contact_Last_Name] AS FullName, TBL_Projects.Project_Id, TBL_Data_Relationships.Data_Relationships_Project, TBL_Data_Relationships.Data_Relationships_Contact
FROM TBL_Projects INNER JOIN (TBL_Contacts INNER JOIN TBL_Data_Relationships ON TBL_Contacts.Contact_ID = TBL_Data_Relationships.Data_Relationships_Contact) ON TBL_Projects.Project_Id = TBL_Data_Relationships.Data_Relationships_Project
WHERE (((TBL_Contacts.Contact_Reel_Sent)=Yes) AND ((TBL_Contacts.Contact_Iclude_In_SR)=Yes))
ORDER BY [TBL_Contacts.Contact_First_Name] & " " & [TBL_Contacts.Contact_Last_Name];

I have no idea where to go from here? I cant figure out where these records are comming from.

 

[o1110010]

Kind of Messy...

Holy Buckets. That's the longest SQL statement I've seen as of yet. *gives blue ribbon to bliesveld*

I don't have the time to help you out on this one. But others will pick it up (ie sonny). Good luck!

 

[bliesveld]

found my error! for some reason if I included the Project_ID from the relationship table in both SQL statements:

QRY for TABLE_Projects -> Relationship_Table.Project_ID
QRY for TABLE_Contacts -> Relationship_Table.Project_ID

I got multi records, as soon as I took it out of the Projcet QRY, its not need there becouse I have the Project_ID aready every thing works fine...

 

[sonny]

Holy Buckets. That's the longest SQL statement I've seen as of yet. *gives blue ribbon to bliesveld*

I don't have the time to help you out on this one. But others will pick it up (ie sonny). Good luck!

Right off I see you have this:

TBL_Contacts.Contact_First_Name, TBL_Contacts.Contact_Last_Name

And this:
[TBL_Contacts.Contact_First_Name] & " " & [TBL_Contacts.Contact_Last_Name] AS FullName

If all you want is "FullName" you should be able to remove the 1st part

I'm assuming thats what contact1 & 2 ect are, the FullName!

PS. Heres a qry I use, talking about long(its long!):

SELECT J4FY00_LABOR_HOURS.CHARGE_CODE, J4FY00_LABOR_HOURS.EMP_ID_NO, J4FY00_LABOR_HOURS.HRS, [BASIC_PAY]+[ANNUAL_LEAVE_AMT]+[FOA_OVHD_AMT]+[GOVT_CONTRIB_AMT]+[INDIRECT_ADD_ON_AMT]+[RECOVERY_AMT] AS [Labor_$]
FROM
J4FY00_LABOR_HOURS
WHERE (((J4FY00_LABOR_HOURS.CHARGE_CODE) Not Like "LEAVE") AND ((J4FY00_LABOR_HOURS.EMP_ID_NO)Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666"))) OR (((J4FY00_LABOR_HOURS.CHARGE_CODE)="DISPUT") AND ((J4FY00_LABOR_HOURS.EMP_ID_NO) Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666")))

UNION SELECT J4FY01_LABOR_HOURS.CHARGE_CODE, J4FY01_LABOR_HOURS.EMP_ID_NO, J4FY01_LABOR_HOURS.HRS, [BASIC_PAY]+[ANNUAL_LEAVE_AMT]+[FOA_OVHD_AMT]+[GOVT_CONTRIB_AMT]+[INDIRECT_ADD_ON_AMT]+[RECOVERY_AMT] AS [Labor_$]
FROM
J4FY01_LABOR_HOURS
WHERE (((J4FY01_LABOR_HOURS.CHARGE_CODE) Not Like "LEAVE") AND ((J4FY01_LABOR_HOURS.EMP_ID_NO)Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666"))) OR (((J4FY01_LABOR_HOURS.CHARGE_CODE)="DISPUT") AND ((J4FY01_LABOR_HOURS.EMP_ID_NO) Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666")))

UNION SELECT J4FY02_LABOR_HOURS.CHARGE_CODE, J4FY02_LABOR_HOURS.EMP_ID_NO, J4FY02_LABOR_HOURS.HRS, [BASIC_PAY]+[ANNUAL_LEAVE_AMT]+[FOA_OVHD_AMT]+[GOVT_CONTRIB_AMT]+[INDIRECT_ADD_ON_AMT]+[RECOVERY_AMT] AS [Labor_$]
FROM
J4FY02_LABOR_HOURS
WHERE (((J4FY02_LABOR_HOURS.CHARGE_CODE) Not Like "LEAVE") AND ((J4FY02_LABOR_HOURS.EMP_ID_NO)Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666"))) OR (((J4FY02_LABOR_HOURS.CHARGE_CODE)="DISPUT") AND ((J4FY02_LABOR_HOURS.EMP_ID_NO) Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666")))

UNION SELECT J4FY98_LABOR_HOURS.CHARGE_CODE, J4FY98_LABOR_HOURS.EMP_ID_NO, J4FY98_LABOR_HOURS.HRS, [BASIC_PAY]+[ANNUAL_LEAVE_AMT]+[FOA_OVHD_AMT]+[GOVT_CONTRIB_AMT]+[INDIRECT_ADD_ON_AMT]+[RECOVERY_AMT] AS [Labor_$]
FROM
J4FY98_LABOR_HOURS
WHERE (((J4FY98_LABOR_HOURS.CHARGE_CODE) Not Like "LEAVE") AND ((J4FY98_LABOR_HOURS.EMP_ID_NO)Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666"))) OR (((J4FY98_LABOR_HOURS.CHARGE_CODE)="DISPUT") AND ((J4FY98_LABOR_HOURS.EMP_ID_NO) Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666")))

UNION SELECT J4FY99_LABOR_HOURS.CHARGE_CODE, J4FY99_LABOR_HOURS.EMP_ID_NO, J4FY99_LABOR_HOURS.HRS, [BASIC_PAY]+[ANNUAL_LEAVE_AMT]+[FOA_OVHD_AMT]+[GOVT_CONTRIB_AMT]+[INDIRECT_ADD_ON_AMT]+[RECOVERY_AMT] AS [Labor_$]
FROM
J4FY99_LABOR_HOURS
WHERE (((J4FY99_LABOR_HOURS.CHARGE_CODE) Not Like "LEAVE") AND ((J4FY99_LABOR_HOURS.EMP_ID_NO)Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666"))) OR (((J4FY99_LABOR_HOURS.CHARGE_CODE)="DISPUT") AND ((J4FY99_LABOR_HOURS.EMP_ID_NO) Not In ("BEACJ7559","MONGK1847","TYBUD4572","ANTOF1087","ARRUA5958","BAXTJ3114","MOORD6671","HOTCJ1527","SMITV2229","FOREA3589","JARVE1920","HELLD8760","WAUGL0712","BROWH4901","VITAW1016","KINGC6208","ABUNJ0666")));

 

[o1110010]

...its long!

ROFL. Yikes! *steals ribbon back and redistributes accordingly*

 

[sonny]

Glad you got it.... Usally it something simple if it runs....One can get blinded by the amount of text in big queries!

 

[sonny]

That was just step one of 16 queries to get to the final data!