Help with a calculation

[Tony Williams]

I have a table, tblmaintabs, that has 7 number fields representing data that
is collected on a quarterly basis in March,June,September and December for
the last 10 years. The dates are held in a field in the table called
txtmonth. I want to create additional fields that hold YTD figures, so the
first field will hold March's figures, the second field will hold March +
June's figures, the second field will hold March+June + September's figures
and the fourth field will hold March + June +September +December
March Q1 June Q2 September Q3
December Q4
Field1Amount 10 10 20 30 25 55
40 95
Field2Amount 20 20 30 50 30 80
20 100
Field3Amount 30 30 30 60 25 85
20 105
and so on for 7 fields
I have tried exporting the table to Excel and doing it manually but there
are some 1300 records and it's taking rather a long time!
Can anyone think of a way of doing this?
TIA

 

[Joseph Meehan]

I have a table, tblmaintabs, that has 7 number fields representing
data that is collected on a quarterly basis in March,June,September
and December for the last 10 years. The dates are held in a field in
the table called txtmonth. I want to create additional fields that
hold YTD figures,

No you don't. You want to see year to date figures and use them in
reports etc. You already have all the information in the database to
compute them and that is what you should do. In addition you eliminate the
problem of what happens if someone makes a correction and forgets to update
the YTD.

If you look a a properly prepared worksheet in Excel, you will see that
those figures like YTD are usually computed as well, they display the
results, but in reality they are computations.

so the first field will hold March's figures, the
second field will hold March + June's figures, the second field will
hold March+June + September's figures and the fourth field will hold
March + June +September +December March Q1
June Q2 September Q3
December Q4
Field1Amount 10 10 20 30 25
55 40 95
Field2Amount 20 20 30 50 30
80 20 100
Field3Amount 30 30 30 60 25
85 20 105
and so on for 7 fields
I have tried exporting the table to Excel and doing it manually but
there are some 1300 records and it's taking rather a long time!
Can anyone think of a way of doing this?
TIA

I am a bit confused about the data and how you are storing it. Even
after re-reading your message. I suspect you may have some problems with
your table design that needs to be cleaned up before you move to the next
step. Maybe someone else will be able to figure that out and give you some
guidance on what your next step should be. If not maybe you could try
giving me that information again explaining it in a different way.

 

[Guest]

hi,
any value that is calculated should not be stored in a
table.
I am having a little trouble understanding your table here
but i am pretty sure you can do the calculation with a
select query.
Select all the fields that you want to sum up.
then in the next query field put something like this in
the header
TotalMar:[March]
in the next query field
totalJun:[March]+[June]
then in the next queryfield
totalsept:[March]+[june]+[Sept]
and so on.
the calculated fields in the query with then sum up the
other fields in the query.
Post back if you have question or problems.

 

[Tony Williams]

Thanks for your reply sorry I didn't make myself clear, I'm relatively new
at this and haven't yet learnt how to explain my self in a logical manner!
What I am looking for is a way to do this
Value for Field1 in March =YTDMarchField1
Value for Field1 in March + Value for Field1 in June =YTDMJuneField1
Value for Field1 in March + Value for Field1 in June + Value for Field1 in
September=YTDSeptemberField1
Value for Field1 in March + Value for Field1 in June + Value for Field1 in
September+Value for Field1 in December=YTDMDecemberField1

Value for Field2 in March =YTDMarchField2
Value for Field2 in March + Value for Field2 in June =YTDJuneField2
Value for Field2 in March + Value for Field2 in June + Value for Field2 in
September=YTDSeptemberField2
Value for Field2 in March + Value for Field2 in June + Value for Field2 in
September+Value for Field2 in December=YTDDecemberField2

Value for Field3 in March =YTDMarchField3
Value for Field3 in March + Value for Field3 in June =YTDJuneField3
Value for Field3 in March + Value for Field3 in June + Value for Field3 in
September=YTDSeptemberField3
Value for Field3 in March + Value for Field3 in June + Value for Field3 in
September+Value for Field3 in December=YTDDecemberField3

and so for 7 fields

Having got a query to calculate these values I then want to create an update
query and put the values into fields in a table.
Is that any clearer?
Thanks
Tony

hi,
any value that is calculated should not be stored in a
table.
I am having a little trouble understanding your table here
but i am pretty sure you can do the calculation with a
select query.
Select all the fields that you want to sum up.
then in the next query field put something like this in
the header
TotalMar:[March]
in the next query field
totalJun:[March]+[June]
then in the next queryfield
totalsept:[March]+[june]+[Sept]
and so on.
the calculated fields in the query with then sum up the
other fields in the query.
Post back if you have question or problems.

-----Original Message-----
I have a table, tblmaintabs, that has 7 number fields representing data that
is collected on a quarterly basis in March,June,September and December for
the last 10 years. The dates are held in a field in the table called
txtmonth. I want to create additional fields that hold YTD figures, so the
first field will hold March's figures, the second field will hold March +
June's figures, the second field will hold March+June + September's figures
and the fourth field will hold March + June +September +December
March Q1 June Q2 September Q3
December Q4
Field1Amount 10 10 20 30 25 55
40 95
Field2Amount 20 20 30 50 30 80
20 100
Field3Amount 30 30 30 60 25 85
20 105
and so on for 7 fields
I have tried exporting the table to Excel and doing it manually but there
are some 1300 records and it's taking rather a long time!
Can anyone think of a way of doing this?
TIA


.

 

[Guest]

hi again,
yes clearer. and my post reply with do that for you.
i made a little test table to test it.
4 fields - mar, jun, sept, dec with each field = 10
the query
4 fields -mar, jun, sept, dec
4 calcalulate fields = totmar, totjun, totsept, totdec
the query results
mar jun sept dec totmar totjun totsept totdec
10 10 10 10 10 20 30 40

here's the sql
SELECT [1testtabel].mar, [1testtabel].jun,
[1testtabel].sept, [1testtabel].dec, [1testtabel].mar AS
totMar, [mar]+[jun] AS totjun, [mar]+[jun]+[sept] AS
totsept, [mar]+[jun]+[sept]+[dec] AS totDec
FROM 1testtabel;

what was confusing me was that your example table you
posted looked more like a pivot table than an access table.
and fields on the side was confusing. fields are in
columns. records are in rows.
but i still think this can be done with a select query
with calculated fields.
Post back if you have questions/problems
good luck.

-----Original Message-----
Thanks for your reply sorry I didn't make myself clear, I'm relatively new
at this and haven't yet learnt how to explain my self in a logical manner!
What I am looking for is a way to do this
Value for Field1 in March =YTDMarchField1
Value for Field1 in March + Value for Field1 in June =YTDMJuneField1
Value for Field1 in March + Value for Field1 in June + Value for Field1 in
September=YTDSeptemberField1
Value for Field1 in March + Value for Field1 in June + Value for Field1 in
September+Value for Field1 in December=YTDMDecemberField1

Value for Field2 in March =YTDMarchField2
Value for Field2 in March + Value for Field2 in June =YTDJuneField2
Value for Field2 in March + Value for Field2 in June + Value for Field2 in
September=YTDSeptemberField2
Value for Field2 in March + Value for Field2 in June + Value for Field2 in
September+Value for Field2 in December=YTDDecemberField2

Value for Field3 in March =YTDMarchField3
Value for Field3 in March + Value for Field3 in June =YTDJuneField3
Value for Field3 in March + Value for Field3 in June + Value for Field3 in
September=YTDSeptemberField3
Value for Field3 in March + Value for Field3 in June + Value for Field3 in
September+Value for Field3 in December=YTDDecemberField3

and so for 7 fields

Having got a query to calculate these values I then want to create an update
query and put the values into fields in a table.
Is that any clearer?
Thanks
Tony

hi,
any value that is calculated should not be stored in a
table.
I am having a little trouble understanding your table here
but i am pretty sure you can do the calculation with a
select query.
Select all the fields that you want to sum up.
then in the next query field put something like this in
the header
TotalMar:[March]
in the next query field
totalJun:[March]+[June]
then in the next queryfield
totalsept:[March]+[june]+[Sept]
and so on.
the calculated fields in the query with then sum up the
other fields in the query.
Post back if you have question or problems.

-----Original Message-----
I have a table, tblmaintabs, that has 7 number fields representing data that
is collected on a quarterly basis in

March,June,September
and December for

the last 10 years. The dates are held in a field in the table called
txtmonth. I want to create additional fields that hold YTD figures, so the
first field will hold March's figures, the second field will hold March +
June's figures, the second field will hold March+June + September's figures
and the fourth field will hold March + June +September +December
March Q1 June Q2 September Q3
December Q4
Field1Amount 10 10 20 30 25 55
40 95
Field2Amount 20 20 30 50 30 80
20 100
Field3Amount 30 30 30 60 25 85
20 105
and so on for 7 fields
I have tried exporting the table to Excel and doing it manually but there
are some 1300 records and it's taking rather a long time!
Can anyone think of a way of doing this?
TIA


.

.

 

[Guest]

Thanks as it's 20.49 in the UK I'm calling it a day and will try your
suggestion tomorrow Thanks for your help and I will post back if I get any
problems
Tony

hi again,
yes clearer. and my post reply with do that for you.
i made a little test table to test it.
4 fields - mar, jun, sept, dec with each field = 10
the query
4 fields -mar, jun, sept, dec
4 calcalulate fields = totmar, totjun, totsept, totdec
the query results
mar jun sept dec totmar totjun totsept totdec
10 10 10 10 10 20 30 40

here's the sql
SELECT [1testtabel].mar, [1testtabel].jun,
[1testtabel].sept, [1testtabel].dec, [1testtabel].mar AS
totMar, [mar]+[jun] AS totjun, [mar]+[jun]+[sept] AS
totsept, [mar]+[jun]+[sept]+[dec] AS totDec
FROM 1testtabel;

what was confusing me was that your example table you
posted looked more like a pivot table than an access table.
and fields on the side was confusing. fields are in
columns. records are in rows.
but i still think this can be done with a select query
with calculated fields.
Post back if you have questions/problems
good luck.

-----Original Message-----
Thanks for your reply sorry I didn't make myself clear, I'm relatively new
at this and haven't yet learnt how to explain my self in a logical manner!
What I am looking for is a way to do this
Value for Field1 in March =YTDMarchField1
Value for Field1 in March + Value for Field1 in June =YTDMJuneField1
Value for Field1 in March + Value for Field1 in June + Value for Field1 in
September=YTDSeptemberField1
Value for Field1 in March + Value for Field1 in June + Value for Field1 in
September+Value for Field1 in December=YTDMDecemberField1

Value for Field2 in March =YTDMarchField2
Value for Field2 in March + Value for Field2 in June =YTDJuneField2
Value for Field2 in March + Value for Field2 in June + Value for Field2 in
September=YTDSeptemberField2
Value for Field2 in March + Value for Field2 in June + Value for Field2 in
September+Value for Field2 in December=YTDDecemberField2

Value for Field3 in March =YTDMarchField3
Value for Field3 in March + Value for Field3 in June =YTDJuneField3
Value for Field3 in March + Value for Field3 in June + Value for Field3 in
September=YTDSeptemberField3
Value for Field3 in March + Value for Field3 in June + Value for Field3 in
September+Value for Field3 in December=YTDDecemberField3

and so for 7 fields

Having got a query to calculate these values I then want to create an update
query and put the values into fields in a table.
Is that any clearer?
Thanks
Tony

hi,
any value that is calculated should not be stored in a
table.
I am having a little trouble understanding your table here
but i am pretty sure you can do the calculation with a
select query.
Select all the fields that you want to sum up.
then in the next query field put something like this in
the header
TotalMar:[March]
in the next query field
totalJun:[March]+[June]
then in the next queryfield
totalsept:[March]+[june]+[Sept]
and so on.
the calculated fields in the query with then sum up the
other fields in the query.
Post back if you have question or problems.

-----Original Message-----
I have a table, tblmaintabs, that has 7 number fields
representing data that
is collected on a quarterly basis in March,June,September
and December for
the last 10 years. The dates are held in a field in the
table called
txtmonth. I want to create additional fields that hold
YTD figures, so the
first field will hold March's figures, the second field
will hold March +
June's figures, the second field will hold March+June +
September's figures
and the fourth field will hold March + June +September
+December
March Q1 June Q2
September Q3
December Q4
Field1Amount 10 10 20 30
25 55
40 95
Field2Amount 20 20 30 50
30 80
20 100
Field3Amount 30 30 30 60
25 85
20 105
and so on for 7 fields
I have tried exporting the table to Excel and doing it
manually but there
are some 1300 records and it's taking rather a long time!
Can anyone think of a way of doing this?
TIA


.

.

 

[Guest]

Thanks as it's 20.49 in the UK I'm calling it a day. However the reason I
want to create fields and a new table is because we are not using quarterly
data in the future we're usin YTD data and we wanted to convert the existing
data to YTD data so that we would have a mix of quarterly and YTD data in the
same database. I will have another look at my structure and post back
tomorrow when I have with what I think is my situation. Thanks for your help
and I will post back if I get any problems
Tony

 

[Joseph Meehan]

Thanks as it's 20.49 in the UK I'm calling it a day. However the
reason I want to create fields and a new table is because we are not
using quarterly data in the future we're usin YTD data and we wanted
to convert the existing data to YTD data so that we would have a mix
of quarterly and YTD data in the same database. I will have another
look at my structure and post back tomorrow when I have with what I
think is my situation. Thanks for your help and I will post back if I
get any problems
Tony

That makes sense.

 

[Michael J. Strickland]

Try:

1. Copy the original table (structure only) to a blank table.

2. In Design view, add the YTD fields (YTD1, YTD2, YTD3, YTD4)
immediately to the right of the corresponding Q1, Q2, Q3, Q4 fields..

3. Make a query using the original table. In Design mode, insert four
calculated YTD values into new columns immediately to the right of the Q1,
Q2, Q3, Q4 values:
= [Q1]
= [Q1] + [Q2]
= [Q1] + [Q2] + [Q3]
= [Q1] + [Q2] + [Q3] + [Q4]


4. Open the query and copy it into the blank table. (Click upper left corner
of query, Ctl-C, Minimize query, Open blank table, Click upper left corner,
Ctl-V).

Alternatively, you could just use the query as the source for your reports.
This would avoid the problem of someone accidentally updating the new table
manually.