Query Records and Detail Section of Report

[Nadine via AccessMonster.com]

Hi -

Thanking anyone in advance for helping me out on this one. I might struggle
with explaining this in a manner that you can understand but here goes.

I have a query which returns at least two records. I am dealing with CLAIM
information - the first record contains the original information as the CLAIM
was paid and the second record is for the revised information as there was a
change in the information used to calculate the claim. The KEY IDENTIFIER
for my CLAIMS is the Claim Release Number. For example: The first has a
CLAIM RELEASE No of 3608-0 and the second one has a CLAIM RELEASE No of 3608-
1. *** As a note - when the report is run - the user is prompted for the
Claim Release No - I have a LEFT FUNCTION on the field in order to ensure
that both records are returned.

This query is used as the Record Source for my REPORT.

On the REPORT in the DETAIL section - there is the following information:

Insured Area 20.00
Measured Area 20.00
Total Harvest Yield 30305.00
Deficit 2437.00
Multiply by Established Price 0.3500
Amount of Claim 852.95
LESS PREVIOUSLY PAID CLAIM *** HERE IS MY PROBLEM - this value comes
from the first record (3608-0)
Total Claim Payable = [AMOUNT OF CLAIM] -
[LESS PREVIOUSLY PAID CLAIM]

All other fields in the detail section of the report (except for the LESS
PREVIOUSLY PAID CLAIM) come from the revised record - 3608-1.

Is this possible?

To have two records returned from a query but displayed as one record in the
detail section of the report?

Hope this makes sense! And let me know if there is anything else I can
provide to you so that you might be able to understand my post!

Thanks again!

Nadine

 

[John Nurick]

Hi Nadine,

You say "a query that returns at least two records", but the example you
give has only two records. I'm assuming that if there are N records then
you need to sum the amount recorded as paid in the first N-1 records and
deduct it from the [Amount of Claim] in the Nth record.

Probably the way to do this is to set up two queries, which I'll call
qryMain and qryPaidToDate, and have a textbox or combobox on a form
where the user specifies the Claim Release Number. I'll call the form
frmX and the textbox txtSelectClaim.

qryMain needs to return only one record, presumably the the most recent
record where the first part of the Claim Release Number matches the one
the user specified. This is the data source of the report; it will look
something like this:

SELECT * FROM MyTable
WHERE ClaimReleaseNumber = Forms!frmX!txtSelectClaim
;

qryPrevious needs to sum any previous records that match the first part
of the Claim Release Number, but not the record returned by qryMain. It
should return just one field, the total amount paid so far. I don't
think you've mentioned the name of the field, so I'll call it
AmountPaid. The query will look something like this:

SELECT SUM(AmountPaid) As AmountPaidToDate FROM MyTable
WHERE Left(ClaimReleaseNumber, 4) = Left(Forms!frmX!txtSelectClaim, 4)
AND ClaimReleaseNumber <> Forms!frmX!txtSelectClaim
;

You can then use a DLookup() expression as the controlsource of the
LESS PREVIOUSLY PAID CLAIM textbox on the report, something like:

=-DLookup("AmountPaidToDate", "qryPrevious")

Hi -

Thanking anyone in advance for helping me out on this one. I might struggle
with explaining this in a manner that you can understand but here goes.

I have a query which returns at least two records. I am dealing with CLAIM
information - the first record contains the original information as the CLAIM
was paid and the second record is for the revised information as there was a
change in the information used to calculate the claim. The KEY IDENTIFIER
for my CLAIMS is the Claim Release Number. For example: The first has a
CLAIM RELEASE No of 3608-0 and the second one has a CLAIM RELEASE No of 3608-
1. *** As a note - when the report is run - the user is prompted for the
Claim Release No - I have a LEFT FUNCTION on the field in order to ensure
that both records are returned.

This query is used as the Record Source for my REPORT.

On the REPORT in the DETAIL section - there is the following information:

Insured Area 20.00
Measured Area 20.00
Total Harvest Yield 30305.00
Deficit 2437.00
Multiply by Established Price 0.3500
Amount of Claim 852.95
LESS PREVIOUSLY PAID CLAIM *** HERE IS MY PROBLEM - this value comes
from the first record (3608-0)
Total Claim Payable = [AMOUNT OF CLAIM] -
[LESS PREVIOUSLY PAID CLAIM]

All other fields in the detail section of the report (except for the LESS
PREVIOUSLY PAID CLAIM) come from the revised record - 3608-1.

Is this possible?

To have two records returned from a query but displayed as one record in the
detail section of the report?

Hope this makes sense! And let me know if there is anything else I can
provide to you so that you might be able to understand my post!

Thanks again!

Nadine