SteveMillman
Programmer
Hello,
I inherited a complex Access 97 report that generates a report and distributes it to the requestor via email.
Its complexity lies in its many inner joins tieing over a half dozen tables together to produce the report.
The requestor's id is tied to their company, and this report works fine when only one requestor per company asks for the report; but when more than one request comes from the same company (despite their unique send-to addresses) the report starts printing duplicates at the detail line level.
When I tried to "hide" the duplicates from the report, I got blank lines instead, marking where the duplicates would have been.
When I tried requesting "unique values or records" in the query properties, I end up with the duplicate values.
Is there a way to tell the sql to only return unique values at the detail line level.. so that the redundancies are missing from the datasheet view.. and therefore the report?
I am posting the actual sql statements below (don't be frightened.. its fairly horrible!).. in hope that there is an easy answer to this dilemna.
Thank you for your help,
Steve
SELECT MEMBERS.MBR, [Member Services].Service, [Member Services].Expires, MEMBERS.Expire, PLANS.Received, MEMBERS.Fax, MEMBERS.Company, PLANS.MBE, PLANS.ProjTitle, PLNSPEC.Subspec, SPECSEC.Title, PLANS.Bin, PLANS.EstCost, PLANS.ProjCity, PLANS.ProjState, PLANS.[Ad for Bid], PLANS.[Plan Type]
FROM (MEMBERS INNER JOIN (PLANS INNER JOIN ((PLNSPEC INNER JOIN MBRSPEC ON PLNSPEC.Subspec = MBRSPEC.Subspec) INNER JOIN SPECSEC ON PLNSPEC.Subspec = SPECSEC.Specsec) ON PLANS.MBE = PLNSPEC.MBE) ON MEMBERS.MBR = MBRSPEC.MBR) INNER JOIN [Member Services] ON MEMBERS.MBR = [Member Services].MBR
WHERE (((MEMBERS.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].Service)="MBRSPEC EMAIL"
AND (([Member Services].Expires)>=Date()) AND ((MEMBERS.Expire)>=Date()) AND ((PLANS.Received)=Date()) AND ((MBRSPEC.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].MBR)=[forms]![ReportParameterF]![txtParameter]) AND ((PLANS.PIP)=No));
When there are more than one requesting member
When
SELECT MEMBERS.MBR, [Member Services].Service, [Member Services].Expires, MEMBERS.Expire, PLANS.Received, MEMBERS.Fax, MEMBERS.Company, PLANS.MBE, PLANS.ProjTitle, PLNSPEC.Subspec, SPECSEC.Title, PLANS.Bin, PLANS.EstCost, PLANS.ProjCity, PLANS.ProjState, PLANS.[Ad for Bid], PLANS.[Plan Type]
FROM (MEMBERS INNER JOIN (PLANS INNER JOIN ((PLNSPEC INNER JOIN MBRSPEC ON PLNSPEC.Subspec = MBRSPEC.Subspec) INNER JOIN SPECSEC ON PLNSPEC.Subspec = SPECSEC.Specsec) ON PLANS.MBE = PLNSPEC.MBE) ON MEMBERS.MBR = MBRSPEC.MBR) INNER JOIN [Member Services] ON MEMBERS.MBR = [Member Services].MBR
WHERE (((MEMBERS.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].Service)="MBRSPEC EMAIL" AND (([Member Services].Expires)>=Date()) AND ((MEMBERS.Expire)>=Date()) AND ((PLANS.Received)=Date()) AND ((MBRSPEC.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].MBR)=[forms]![ReportParameterF]![txtParameter]) AND ((PLANS.PIP)=No));
I inherited a complex Access 97 report that generates a report and distributes it to the requestor via email.
Its complexity lies in its many inner joins tieing over a half dozen tables together to produce the report.
The requestor's id is tied to their company, and this report works fine when only one requestor per company asks for the report; but when more than one request comes from the same company (despite their unique send-to addresses) the report starts printing duplicates at the detail line level.
When I tried to "hide" the duplicates from the report, I got blank lines instead, marking where the duplicates would have been.
When I tried requesting "unique values or records" in the query properties, I end up with the duplicate values.
Is there a way to tell the sql to only return unique values at the detail line level.. so that the redundancies are missing from the datasheet view.. and therefore the report?
I am posting the actual sql statements below (don't be frightened.. its fairly horrible!).. in hope that there is an easy answer to this dilemna.
Thank you for your help,
Steve
SELECT MEMBERS.MBR, [Member Services].Service, [Member Services].Expires, MEMBERS.Expire, PLANS.Received, MEMBERS.Fax, MEMBERS.Company, PLANS.MBE, PLANS.ProjTitle, PLNSPEC.Subspec, SPECSEC.Title, PLANS.Bin, PLANS.EstCost, PLANS.ProjCity, PLANS.ProjState, PLANS.[Ad for Bid], PLANS.[Plan Type]
FROM (MEMBERS INNER JOIN (PLANS INNER JOIN ((PLNSPEC INNER JOIN MBRSPEC ON PLNSPEC.Subspec = MBRSPEC.Subspec) INNER JOIN SPECSEC ON PLNSPEC.Subspec = SPECSEC.Specsec) ON PLANS.MBE = PLNSPEC.MBE) ON MEMBERS.MBR = MBRSPEC.MBR) INNER JOIN [Member Services] ON MEMBERS.MBR = [Member Services].MBR
WHERE (((MEMBERS.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].Service)="MBRSPEC EMAIL"
AND (([Member Services].Expires)>=Date()) AND ((MEMBERS.Expire)>=Date()) AND ((PLANS.Received)=Date()) AND ((MBRSPEC.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].MBR)=[forms]![ReportParameterF]![txtParameter]) AND ((PLANS.PIP)=No));When there are more than one requesting member
When
SELECT MEMBERS.MBR, [Member Services].Service, [Member Services].Expires, MEMBERS.Expire, PLANS.Received, MEMBERS.Fax, MEMBERS.Company, PLANS.MBE, PLANS.ProjTitle, PLNSPEC.Subspec, SPECSEC.Title, PLANS.Bin, PLANS.EstCost, PLANS.ProjCity, PLANS.ProjState, PLANS.[Ad for Bid], PLANS.[Plan Type]
FROM (MEMBERS INNER JOIN (PLANS INNER JOIN ((PLNSPEC INNER JOIN MBRSPEC ON PLNSPEC.Subspec = MBRSPEC.Subspec) INNER JOIN SPECSEC ON PLNSPEC.Subspec = SPECSEC.Specsec) ON PLANS.MBE = PLNSPEC.MBE) ON MEMBERS.MBR = MBRSPEC.MBR) INNER JOIN [Member Services] ON MEMBERS.MBR = [Member Services].MBR
WHERE (((MEMBERS.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].Service)="MBRSPEC EMAIL"
AND (([Member Services].Expires)>=Date()) AND ((MEMBERS.Expire)>=Date()) AND ((PLANS.Received)=Date()) AND ((MBRSPEC.MBR)=[forms]![ReportParameterF]![txtParameter]) AND (([Member Services].MBR)=[forms]![ReportParameterF]![txtParameter]) AND ((PLANS.PIP)=No));
