Is this report possible?

[Leslie Isaacs]

Hello All

I have a table [private] with fields [fee] (currency), [date_received] and
[date_banked] (amoung others). Is it possible to produce a report that
would
list each record once if a [date_received] value has been entered, and
then
list it again if a [date_banked] value has been entered. The idea is to
create a 'cash book' that would show the running total of fees that have
been received but with that running total being reduced by the
corresponding
amount when the fee is banked.

I'm sure this can be done, but cannot see how!

Thanks for any help.
Leslie Isaacs

 

[Duane Hookom]

Leslie,
Could you provide some sample records and how you would want them to appear
in the report?

 

[Leslie Isaacs]

Thanks for your reply.

A sample data set might be:

Record 1:
service_given = Medical report
fee = £45.50
date_fee_received = 4 April 2005
date_fee_banked = 8 April 2005

Record 2:
service_given = Insurance report
fee = £48.00
date_fee_received = 9 April 2005
date_fee_banked = 12 April 2005

Record 3:
service_given = Medical report
fee = £25.50
date_fee_received = 5 April 2005
date_fee_banked = <no value>

Record 4:
service_given = BMA report
fee = £125.00
date_fee_received = 3 April 2005
date_fee_banked = 7 April 2005

From this data, I would need a report that looks like:

Date Record number Service given Received Banked
Balance
3 April 2005 4 BMA report £125
£125
4 April 2005 1 Medical Report £45.50
£170.50
5 April 2005 3 Medical Report £25.50
£196.00
7 April 2005 4 BMA report
£125.00 £71.00
8 April 2005 1 Medical Report
£45.50 £25.50
9 April 2005 2 Insurance report £48.00
£73.50
12 April 2005 2 Insurance report
£48.00 £25.00

So each record appears once in the report when the fee has been received,
and then it appears again in the report if (and when) the fee has been
banked.

Hope this helps explain what I'm hoping to achieve - and thanks for you
continued help!
Les

Leslie,
Could you provide some sample records and how you would want them to appear
in the report?

--
Duane Hookom
MS Access MVP

Hello All

I have a table [private] with fields [fee] (currency), [date_received] and
[date_banked] (amoung others). Is it possible to produce a report that
would
list each record once if a [date_received] value has been entered, and
then
list it again if a [date_banked] value has been entered. The idea is to
create a 'cash book' that would show the running total of fees that have
been received but with that running total being reduced by the
corresponding
amount when the fee is banked.

I'm sure this can be done, but cannot see how!

Thanks for any help.
Leslie Isaacs

 

[Duane Hookom]

You should be able to use a union query like:
SELECT Received As TransDate, RecordNumber,
ServiceGiven, Amount As Received, Null As Banked
FROM tblSampleData
WHERE Received is not Null
UNION ALL
SELECT Banked, RecordNumber, ServiceGiven, Null, Amount
FROM tblSampleData
WHERE Banked is not Null;

You can get a running balance as a Running Sum in a report.

--
Duane Hookom
MS Access MVP
--

Thanks for your reply.

A sample data set might be:

Record 1:
service_given = Medical report
fee = £45.50
date_fee_received = 4 April 2005
date_fee_banked = 8 April 2005

Record 2:
service_given = Insurance report
fee = £48.00
date_fee_received = 9 April 2005
date_fee_banked = 12 April 2005

Record 3:
service_given = Medical report
fee = £25.50
date_fee_received = 5 April 2005
date_fee_banked = <no value>

Record 4:
service_given = BMA report
fee = £125.00
date_fee_received = 3 April 2005
date_fee_banked = 7 April 2005

From this data, I would need a report that looks like:

Date Record number Service given Received
Banked
Balance
3 April 2005 4 BMA report £125
£125
4 April 2005 1 Medical Report £45.50
£170.50
5 April 2005 3 Medical Report £25.50
£196.00
7 April 2005 4 BMA report
£125.00 £71.00
8 April 2005 1 Medical Report
£45.50 £25.50
9 April 2005 2 Insurance report £48.00
£73.50
12 April 2005 2 Insurance report
£48.00 £25.00

So each record appears once in the report when the fee has been received,
and then it appears again in the report if (and when) the fee has been
banked.

Hope this helps explain what I'm hoping to achieve - and thanks for you
continued help!
Les

Leslie,
Could you provide some sample records and how you would want them to appear
in the report?

--
Duane Hookom
MS Access MVP

Hello All

I have a table [private] with fields [fee] (currency), [date_received] and
[date_banked] (amoung others). Is it possible to produce a report that
would
list each record once if a [date_received] value has been entered, and
then
list it again if a [date_banked] value has been entered. The idea is to
create a 'cash book' that would show the running total of fees that
have
been received but with that running total being reduced by the
corresponding
amount when the fee is banked.

I'm sure this can be done, but cannot see how!

Thanks for any help.
Leslie Isaacs

 

[Ruth Isaacs]

Duane

Many thanks for that - worked a treat!

Regards
Les

You should be able to use a union query like:
SELECT Received As TransDate, RecordNumber,
ServiceGiven, Amount As Received, Null As Banked
FROM tblSampleData
WHERE Received is not Null
UNION ALL
SELECT Banked, RecordNumber, ServiceGiven, Null, Amount
FROM tblSampleData
WHERE Banked is not Null;

You can get a running balance as a Running Sum in a report.

--
Duane Hookom
MS Access MVP
--

Thanks for your reply.

A sample data set might be:

Record 1:
service_given = Medical report
fee = £45.50
date_fee_received = 4 April 2005
date_fee_banked = 8 April 2005

Record 2:
service_given = Insurance report
fee = £48.00
date_fee_received = 9 April 2005
date_fee_banked = 12 April 2005

Record 3:
service_given = Medical report
fee = £25.50
date_fee_received = 5 April 2005
date_fee_banked = <no value>

Record 4:
service_given = BMA report
fee = £125.00
date_fee_received = 3 April 2005
date_fee_banked = 7 April 2005

From this data, I would need a report that looks like:

Date Record number Service given Received
Banked
Balance
3 April 2005 4 BMA report £125
£125
4 April 2005 1 Medical Report £45.50
£170.50
5 April 2005 3 Medical Report £25.50
£196.00
7 April 2005 4 BMA report
£125.00 £71.00
8 April 2005 1 Medical Report
£45.50 £25.50
9 April 2005 2 Insurance report £48.00
£73.50
12 April 2005 2 Insurance report
£48.00 £25.00

So each record appears once in the report when the fee has been received,
and then it appears again in the report if (and when) the fee has been
banked.

Hope this helps explain what I'm hoping to achieve - and thanks for you
continued help!
Les

Leslie,
Could you provide some sample records and how you would want them to appear
in the report?

--
Duane Hookom
MS Access MVP


Hello All

I have a table [private] with fields [fee] (currency),

[date_received]
and

[date_banked] (amoung others). Is it possible to produce a report that
would
list each record once if a [date_received] value has been entered, and
then
list it again if a [date_banked] value has been entered. The idea is to
create a 'cash book' that would show the running total of fees that
have
been received but with that running total being reduced by the
corresponding
amount when the fee is banked.

I'm sure this can be done, but cannot see how!

Thanks for any help.
Leslie Isaacs