Prev: Hiding Fields in Access
Next: Cannot open access reports
From: snoo_yp on 12 Jul 2008 00:49
I use a query as the report source. When not selecting group, everything
seems fine, but when I try to group by name, some of the data was missing.
Can anyone help ?

Here's my query:

SELECT custlisting.Name, custlisting.GroupName, custlisting.Policyno,
custlisting.[2DSUM], custlisting.[3DSUM], custlisting.LADYSUM, custlisting.
ACCSUM, custlisting.HOSSUM, custlisting.HOS
FROM custlisting
ORDER BY custlisting.custid;

Result:

Name Groupname Policyno Premium 2dsum 3dsum Ladysum
Accsum Hossum
Tan TT xxxxxxx 135.45 20,
000
Tan TT yyyyyyy 854.25 30,
000 20,000 50,000
Tan TT zzzzzzz 586.00
Jing TT kkkkkkk 965.25


But when I group under Name, it only show:

ame Groupname Policyno Premium 2dsum 3dsum Ladysum
Accsum Hossum
Tan TT xxxxxxx 135.45
Tan TT yyyyyyy 854.25
Tan TT zzzzzzz 586.00
Jing TT kkkkkkk 965.25


why ??



This query actually base on another query , which is as follow:

SELECT customer.CustID, customer.Name, Group.GroupName, PayMode.Shortname,
Policy.Policyno, Policy.commdate, Policy.sumassrued AS Poassured, Policy.
premium, Policy.nominee, Policy.waiver, plancat.CatCode, plancat.PlanCode,
plancat.sumassured AS plansum, Group.GroupID, Company.CompanyCode, (select
top 1 custrider.subassured from custrider where custrider.policyid=policy.
policyid and custrider.catcode="2D" ) AS 2DSUM, (select top 1 custrider.
subassured from custrider where custrider.policyid=policy.policyid and
custrider.catcode="3D" ) AS 3DSUM, (select top 1 custrider.subassured from
custrider where custrider.policyid=policy.policyid and custrider.
catcode="LADY" ) AS LADYSUM, (select top 1 custrider.subassured from
custrider where custrider.policyid=policy.policyid and custrider.
catcode="ACC" ) AS ACCSUM, (select top 1 custrider.subassured from custrider
where custrider.policyid=policy.policyid and custrider.catcode="HOS" ) AS
HOSSUM, (select top 1 custrider.ridercode from custrider where custrider.
policyid=policy.policyid and custrider.catcode="HOS" ) AS HOS
FROM ((((Policy LEFT JOIN customer ON Policy.CustID=customer.CustID) LEFT
JOIN [Group] ON customer.Group=Group.GroupID) LEFT JOIN PayMode ON Policy.
paymodeid=PayMode.Paymodeid) LEFT JOIN plancat ON Policy.planid=plancat.
PlanID) LEFT JOIN Company ON Policy.company=Company.CompanyID
WHERE ((([group.groupid])=forms![group Range]!cbogroup));

From: Duane Hookom on 12 Jul 2008 10:46
First, Name is not a good name for anything is Access since Name is a
property.

Where did you group, in the query or the report? Your subject mentions
"using subquery" but there is no subquery that I can see.

What is the data that you are showing? Is it from the query or the report?
If it is in the report, which section?

--
Duane Hookom
Microsoft Access MVP


"snoo_yp" wrote:

> I use a query as the report source. When not selecting group, everything
> seems fine, but when I try to group by name, some of the data was missing.
> Can anyone help ?
>
> Here's my query:
>
> SELECT custlisting.Name, custlisting.GroupName, custlisting.Policyno,
> custlisting.[2DSUM], custlisting.[3DSUM], custlisting.LADYSUM, custlisting.
> ACCSUM, custlisting.HOSSUM, custlisting.HOS
> FROM custlisting
> ORDER BY custlisting.custid;
>
> Result:
>
> Name Groupname Policyno Premium 2dsum 3dsum Ladysum
> Accsum Hossum
> Tan TT xxxxxxx 135.45 20,
> 000
> Tan TT yyyyyyy 854.25 30,
> 000 20,000 50,000
> Tan TT zzzzzzz 586.00
> Jing TT kkkkkkk 965.25
>
>
> But when I group under Name, it only show:
>
> ame Groupname Policyno Premium 2dsum 3dsum Ladysum
> Accsum Hossum
> Tan TT xxxxxxx 135.45
> Tan TT yyyyyyy 854.25
> Tan TT zzzzzzz 586.00
> Jing TT kkkkkkk 965.25
>
>
> why ??
>
>
>
> This query actually base on another query , which is as follow:
>
> SELECT customer.CustID, customer.Name, Group.GroupName, PayMode.Shortname,
> Policy.Policyno, Policy.commdate, Policy.sumassrued AS Poassured, Policy.
> premium, Policy.nominee, Policy.waiver, plancat.CatCode, plancat.PlanCode,
> plancat.sumassured AS plansum, Group.GroupID, Company.CompanyCode, (select
> top 1 custrider.subassured from custrider where custrider.policyid=policy.
> policyid and custrider.catcode="2D" ) AS 2DSUM, (select top 1 custrider.
> subassured from custrider where custrider.policyid=policy.policyid and
> custrider.catcode="3D" ) AS 3DSUM, (select top 1 custrider.subassured from
> custrider where custrider.policyid=policy.policyid and custrider.
> catcode="LADY" ) AS LADYSUM, (select top 1 custrider.subassured from
> custrider where custrider.policyid=policy.policyid and custrider.
> catcode="ACC" ) AS ACCSUM, (select top 1 custrider.subassured from custrider
> where custrider.policyid=policy.policyid and custrider.catcode="HOS" ) AS
> HOSSUM, (select top 1 custrider.ridercode from custrider where custrider.
> policyid=policy.policyid and custrider.catcode="HOS" ) AS HOS
> FROM ((((Policy LEFT JOIN customer ON Policy.CustID=customer.CustID) LEFT
> JOIN [Group] ON customer.Group=Group.GroupID) LEFT JOIN PayMode ON Policy.
> paymodeid=PayMode.Paymodeid) LEFT JOIN plancat ON Policy.planid=plancat.
> PlanID) LEFT JOIN Company ON Policy.company=Company.CompanyID
> WHERE ((([group.groupid])=forms![group Range]!cbogroup));
>
>
From: snoo_yp via AccessMonster.com on 13 Jul 2008 21:45
I group it in the report Group Header. The subquery is acutally inside
another master query and I use tht master query to generate this new query
for report to avoid multi-level group error.(my report have a selection of
GROUPID WHERE ([group.groupid])=forms![group Range]!cbogroup). And the
subquery is inside this master query as I shown at the bottom of my post.

Funny is tht if no grouping is done ,everything seems ok, but when come to
header group, data from last few fields won't show at all~





Duane Hookom wrote:
>First, Name is not a good name for anything is Access since Name is a
>property.
>
>Where did you group, in the query or the report? Your subject mentions
>"using subquery" but there is no subquery that I can see.
>
>What is the data that you are showing? Is it from the query or the report?
>If it is in the report, which section?
>
>> I use a query as the report source. When not selecting group, everything
>> seems fine, but when I try to group by name, some of the data was missing.
>[quoted text clipped - 52 lines]
>> PlanID) LEFT JOIN Company ON Policy.company=Company.CompanyID
>> WHERE ((([group.groupid])=forms![group Range]!cbogroup));

--
Message posted via http://www.accessmonster.com

From: Duane Hookom on 14 Jul 2008 02:59
Is it possible to pull out the subqueries and save them as regular queries?
Then join the former subqueries to the main query.

--
Duane Hookom
Microsoft Access MVP


"snoo_yp via AccessMonster.com" wrote:

> I group it in the report Group Header. The subquery is acutally inside
> another master query and I use tht master query to generate this new query
> for report to avoid multi-level group error.(my report have a selection of
> GROUPID WHERE ([group.groupid])=forms![group Range]!cbogroup). And the
> subquery is inside this master query as I shown at the bottom of my post.
>
> Funny is tht if no grouping is done ,everything seems ok, but when come to
> header group, data from last few fields won't show at all~
>
>
>
>
>
> Duane Hookom wrote:
> >First, Name is not a good name for anything is Access since Name is a
> >property.
> >
> >Where did you group, in the query or the report? Your subject mentions
> >"using subquery" but there is no subquery that I can see.
> >
> >What is the data that you are showing? Is it from the query or the report?
> >If it is in the report, which section?
> >
> >> I use a query as the report source. When not selecting group, everything
> >> seems fine, but when I try to group by name, some of the data was missing.
> >[quoted text clipped - 52 lines]
> >> PlanID) LEFT JOIN Company ON Policy.company=Company.CompanyID
> >> WHERE ((([group.groupid])=forms![group Range]!cbogroup));
>
> --
> Message posted via http://www.accessmonster.com
>
>
 | 
Pages: 1
Prev: Hiding Fields in Access
Next: Cannot open access reports