Hi,

I am in serious need of some help PLEASE?

I have a 4 tables (April data, May data, June data and July data) with
numerous fields. Each of the tables has its own "Passed for payment to
date" field.

In a query I am trying to calculate the "Total passed for payment"
which will be the sum of the "Passed for payment" fields taken from
each of the 4 (month) tables.

I have tried numerous ways of calculating this figure but I am not
having much joy! Any suggestions would be much appreciated?

Cheers,

DWH.

If I understand correctly, you're trying to count the number of records in a
table where there is data in the field [Passed for payment...]:

NumPassedA = DCount("*","[TableApril]","IsNull([Passed for payment])=False")
NumPassedB = DCount("*","[TableMay]","IsNull([Passed for payment])=False")

Then total NumPassed = NumPassedA + NumPassedB + ...


davidhumphrey70@gmail.com wrote:

>Hi,
>
>I am in serious need of some help PLEASE?
>
>I have a 4 tables (April data, May data, June data and July data) with
>numerous fields. Each of the tables has its own "Passed for payment to
>date" field.
>
>In a query I am trying to calculate the "Total passed for payment"
>which will be the sum of the "Passed for payment" fields taken from
>each of the 4 (month) tables.
>
>I have tried numerous ways of calculating this figure but I am not
>having much joy! Any suggestions would be much appreciated?
>
>Cheers,
>
>DWH.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Thanks but I think I am being a bit thick!

I'm not sure if

a) I made myself clear in what I am trying to achieve?
b) I might just not know what I'm doing (which is more likely to be the
case)?

My "April" table has a field in called "Passed for payment this month"
e.g. 5
My "May" table has a field in called "Passed for payment this month"
e.g. 10
My "June" table has a field in called "Passed for payment this month"
e.g. 20
My "July" table has a field in called "Passed for payment this month"
e.g. 20

Therefore 5 + 10 + 20 + 20 = 55.

In a query I am trying to calculate the total passed for payment to
date (in the example I've shown above, should be 55) which I would have
thought should be easy enough to do my adding together April PFP, May
PFP, June PFP and July PFP but obviously not?

If what you suggested is still valid please could you explain how to
get it to work as I haven't managed to get the result I am expecting?

Many Thanks,

DWH.

This seems to indicate that each table has only one record. If so, I'd
recommend using one table instead and another field to indicate the month.
Then you could sum the field in a totals query. In your case, you could use
the function DSum() or create a union query then a totals query based on the
union query.

DSum("[Passed for ...]","TableApril") + DSum("[Passed for ...]","TableMay")...


SELECT [Passed for...] FROM TableApril
UNION SELECT [Passed for...] FROM TableMay...;


davidhumphrey70@gmail.com wrote:

>Thanks but I think I am being a bit thick!
>
>I'm not sure if
>
>a) I made myself clear in what I am trying to achieve?
>b) I might just not know what I'm doing (which is more likely to be the
>case)?
>
>My "April" table has a field in called "Passed for payment this month"
>e.g. 5
>My "May" table has a field in called "Passed for payment this month"
>e.g. 10
>My "June" table has a field in called "Passed for payment this month"
>e.g. 20
>My "July" table has a field in called "Passed for payment this month"
>e.g. 20
>
>Therefore 5 + 10 + 20 + 20 = 55.
>
>In a query I am trying to calculate the total passed for payment to
>date (in the example I've shown above, should be 55) which I would have
>thought should be easy enough to do my adding together April PFP, May
>PFP, June PFP and July PFP but obviously not?
>
>If what you suggested is still valid please could you explain how to
>get it to work as I haven't managed to get the result I am expecting?
>
>Many Thanks,
>
>DWH.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1