Limiting Number of Records

  • Thread starter Thread starter Outpost
  • Start date Start date
O

Outpost

I am not sure this is the correct ng to post this? I have a report
that is a contract form. The report is based on a parameter query. A
field "CALL_NO" can have multiple line items. These are identified by
"ITEM_NO". When the report is open it fires the query and populates
the form. The problem is trying to group the CALL_NO's that will
return an aggregate total dollar value. I would appreciate any
assistance. TIA
 
If I correctly understood your description, one "CALL_NO" can have many
"ITEM_NO"s, but you want to group several "CALL_NO"s, in order to "return an
aggregate total dollar value".

You didn't describe how you decide how to group "CALL_NO"s.

More info, please...

Jeff Boyce
<Access MVP>
 
Jeff Boyce said:
If I correctly understood your description, one "CALL_NO" can have many
"ITEM_NO"s, but you want to group several "CALL_NO"s, in order to "return an
aggregate total dollar value".

You didn't describe how you decide how to group "CALL_NO"s.

More info, please...

Jeff Boyce
<Access MVP>
Click to expand...


Jeff;

Thanks for the quick response. Your understanding is correct. The
grouping is on the "CALL_NO"

There are 3 tables in the query. The CALL_NO is the primary field. The
Report has an embedded form/contract). The query has a parameter set
on the CALL_NO field. There is also a "PLAN_NO" used to identify the
option(s).

When the Report is opened I want to have one page(form/contract) to
return the aggregate results of all the "LINE_ITEMS" against that call
number. I am not sure if it is possible to achieve this when using an
embeded form in a Report.

I have the a query on the query that will return the aggregate sum of
the CALL_NO's however I had to omit the PLAN_NO to make it work. I
have included an extract of the query below. I appreciate your
assistance.

FROM qryEXERCISEORDERS
GROUP BY qryEXERCISEORDERS.CallNo, qryEXERCISEORDERS.VENDOR,
qryEXERCISEORDERS.[BPA No], qryEXERCISEORDERS.ACTIVITY,
qryEXERCISEORDERS.ADDRESS1, qryEXERCISEORDERS.[TERM LENGTH],
Format$(qryEXERCISEORDERS.[Performance date],'mmmm yyyy'),
Format$(qryEXERCISEORDERS.[Performance date1],'mmmm yyyy'),
Format$(qryEXERCISEORDERS.[ORD DATE],'mmmm yyyy'),
qryEXERCISEORDERS.JON, qryEXERCISEORDERS.CITY,
qryEXERCISEORDERS.COUNTRY, qryEXERCISEORDERS.DAPSReqnNo,
qryEXERCISEORDERS.PHONE, Year(qryEXERCISEORDERS.[Performance
date])*12+DatePart('m',qryEXERCISEORDERS.[Performance date])-1,
Year(qryEXERCISEORDERS.[Performance
date1])*12+DatePart('m',qryEXERCISEORDERS.[Performance date1])-1,
Year(qryEXERCISEORDERS.[ORD
DATE])*12+DatePart('m',qryEXERCISEORDERS.[ORD DATE])-1;
 
Back
Top