report using a subreport based on a crosstab query

  • Thread starter Thread starter Nona
  • Start date Start date
N

Nona

I have a data entry form where the data entry fields are for: Amounts
Billed, Amounts Paid and Amounts Invoiced for each month for 20 contracts
(records). The accountant enters the amounts billed and the amounts paid
each month on the primary form. He does not enter the Invoiced Amounts
because that data is pulled from a separate table. It is included on the
primary form as a subform, based on a crosstab query.

The accountant has been printing each form individually for each contract.
He has requested a report that would duplicate the form, so that he would not
have to print each screen separately. He wants the data for all contracts to
print on a separate page of a single report.

I duplicated the query for the main report and another one for the
subreport. I created the main report and inserted the crosstab query as a
subreport. However, there are two things that are not working correctly.

Both queries work correctly when opened separately. Both have the order
property set for “ContractName†and both have a page break at the bottom of
each report.

1. The subform gives the Invoice Totals as the first record instead of as
totals at the bottom of the column. Thus, it is not matching up with each
contract of the main report which also has totals, but they show up on the
report correctly at the bottom of the report. (It all works perfectly on the
data entry form, just not on the report. The main report also works perfectly
if the subform is not on the report.)

2. In spite of the page breaks, the subreport down not break and continues
with the other 19 records. Worse, the main report shows the first record but
continues without the remaining records.

I’ve tried various things without success. Can you tell me what I am doing
wrong?
Or should I just advise him to keep printing the forms individually?

Many thanks!
 
You can probably achieve what you want with the use of a report that is
based on a single Select query that is based on the Contracts table and the
Invoices table with a link between the ContractName in each table and making
use of a Group Header/Footer in the report grouping on the ContractName and
with the Invoice data in the body of the report.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.
 
Can you post the SQL of the crosstab?
Is there a link master/child of the subreport?

Duane Hookom
MS Access MVP
 
Thank you both for your quick responses.
Here is the SQL for the crosstab query:
TRANSFORM Sum([ServCodeRate]*[UnitsUsed]+[TotnonUCRClaim]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]+[TotNonUCRClaim]) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
ORDER BY tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

I don't know how to determine whether there is a master/child link. If there
is one, then Access created it for me. I don't know how to do that.
 
The link master child are two properties on your subreport control. This is
not much different from the last master child properties of a form and
subform.

Duane Hookom
MS Access MVP

Nona said:
Thank you both for your quick responses.
Here is the SQL for the crosstab query:
TRANSFORM Sum([ServCodeRate]*[UnitsUsed]+[TotnonUCRClaim]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]+[TotNonUCRClaim]) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
ORDER BY tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

I don't know how to determine whether there is a master/child link. If
there
is one, then Access created it for me. I don't know how to do that.
Click to expand...
 
PERFECT! Just what was needed.
Thank you very very much!

--
Nona


Duane Hookom said:
The link master child are two properties on your subreport control. This is
not much different from the last master child properties of a form and
subform.

Duane Hookom
MS Access MVP

Nona said:
Thank you both for your quick responses.
Here is the SQL for the crosstab query:
TRANSFORM Sum([ServCodeRate]*[UnitsUsed]+[TotnonUCRClaim]) AS TheValue
SELECT tblContracts.InvType, tblContracts.ContractName,
Sum([ServCodeRate]*[UnitsUsed]+[TotNonUCRClaim]) AS TotInv
FROM (tblServicesProvided LEFT JOIN tblContracts ON
tblServicesProvided.ContractName = tblContracts.ContractName) LEFT JOIN
tblServCodes ON tblServicesProvided.ServCode = tblServCodes.ServCode
GROUP BY tblContracts.InvType, tblContracts.ContractName
ORDER BY tblContracts.ContractName
PIVOT tblServicesProvided.CalMonth In
("July","August","September","October","November","December","January","February","March","April","May","June");

I don't know how to determine whether there is a master/child link. If
there
is one, then Access created it for me. I don't know how to do that.



--
Nona


Duane Hookom said:
Can you post the SQL of the crosstab?
Is there a link master/child of the subreport?

Duane Hookom
MS Access MVP

I have a data entry form where the data entry fields are for: Amounts
Billed, Amounts Paid and Amounts Invoiced for each month for 20
contracts
(records). The accountant enters the amounts billed and the amounts
paid
each month on the primary form. He does not enter the Invoiced Amounts
because that data is pulled from a separate table. It is included on
the
primary form as a subform, based on a crosstab query.

The accountant has been printing each form individually for each
contract.
He has requested a report that would duplicate the form, so that he
would
not
have to print each screen separately. He wants the data for all
contracts
to
print on a separate page of a single report.

I duplicated the query for the main report and another one for the
subreport. I created the main report and inserted the crosstab query as
a
subreport. However, there are two things that are not working
correctly.

Both queries work correctly when opened separately. Both have the order
property set for “ContractName†and both have a page break at the
bottom
of
each report.

1. The subform gives the Invoice Totals as the first record instead of
as
totals at the bottom of the column. Thus, it is not matching up with
each
contract of the main report which also has totals, but they show up on
the
report correctly at the bottom of the report. (It all works perfectly
on
the
data entry form, just not on the report. The main report also works
perfectly
if the subform is not on the report.)

2. In spite of the page breaks, the subreport down not break and
continues
with the other 19 records. Worse, the main report shows the first
record
but
continues without the remaining records.

I’ve tried various things without success. Can you tell me what I am
doing
wrong?
Or should I just advise him to keep printing the forms individually?

Many thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top