Is this report possible?

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

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
 
Leslie,
Could you provide some sample records and how you would want them to appear
in the report?
 
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




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

--
Duane Hookom
MS Access MVP


Leslie Isaacs said:
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
Click to expand...
Click to expand...
 
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
--

Leslie Isaacs said:
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




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

--
Duane Hookom
MS Access MVP


Leslie Isaacs said:
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
Click to expand...
Click to expand...
Click to expand...
 
Duane

Many thanks for that - worked a treat!

Regards
Les


Duane Hookom said:
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
--

Leslie Isaacs said:
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




Duane Hookom said:
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),
Click to expand...
[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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top