Re: Query Records and Detail Section of Report
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Tue, 27 Sep 2005 07:24:15 +0100
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")
On Mon, 26 Sep 2005 19:18:55 GMT, "Nadine via AccessMonster.com"
<u9090@uwe> wrote:
>
>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 [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- References:
- Query Records and Detail Section of Report
- From: Nadine via AccessMonster.com
- Query Records and Detail Section of Report
- Prev by Date: RE: try again get values from another tabel
- Next by Date: Re: How can I send automatic emails from an Access 2000 database?
- Previous by thread: Query Records and Detail Section of Report
- Next by thread: Re: MVP Book Suggestions
- Index(es):
Relevant Pages
|
|
