Grouped data not shown in report using subquery

[snoo_yp]

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));

 

[Duane Hookom]

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

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));

 

[snoo_yp via AccessMonster.com]

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~

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));

 

[Duane Hookom]

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

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~

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));