NEED AN ACCESS FORMULA

[amanda.redetzke]

Hello! I need help writing an Access formula. I thought I could use
DSum but I have no idea how. I want to write a formula that will total
my salary budget by the end date which is another field in my table.

 

[John Vinson]

Hello! I need help writing an Access formula. I thought I could use
DSum but I have no idea how. I want to write a formula that will total
my salary budget by the end date which is another field in my table.

If you're trying to do this in a Table... don't. Tables don't contain
calculations. Calculations can be done in Queries (a Totals query for
instance), on the footers of Forms or Reports, using DSum(), using VBA
code, and in other ways - but *not* in Tables.

Could you explain what you have in the table, and what you want to
see? Remember, you can see your database; we cannot.

John W. Vinson[MVP]

 

[amanda.redetzke]

In the table I have a field for "SumofSalary Budget" and another field
named "Grant end Date". What I want is a formula that would allow me to
sum the Salary Budget By the Grant end Date. Currently I sum everything
on a fiscal year but now I need it according to the Grant end date. So
in order to sum it on a fiscal year I have made 2 entries for each
grant to separate out the different years. Now I just want the total of
the Salary Budget to coincide with the year of the grant. My Grant End
Date Field has a mmddyyyy.

I don't know if that makes sense or not.

 

[John Vinson]

In the table I have a field for "SumofSalary Budget"

No such field should exist. Tables are for DATA STORAGE - they are
*not* spreadsheets! If you store a SumOfSalary Budget field in your
table, the value that you store *will be wrong* as soon as any change
is made to any of the values underlying the sum.

and another field
named "Grant end Date". What I want is a formula that would allow me to
sum the Salary Budget By the Grant end Date.

Create a Totals Query; Group BY [Grant End Date] and Sum [Salary
Budget].

Currently I sum everything
on a fiscal year but now I need it according to the Grant end date. So
in order to sum it on a fiscal year I have made 2 entries for each
grant to separate out the different years. Now I just want the total of
the Salary Budget to coincide with the year of the grant. My Grant End
Date Field has a mmddyyyy.

I don't know if that makes sense or not.

Not really. *You* understand how your tables are structured, the
meaning of a fiscal year (for you, they differ), and so on. I do not,
and I cannot see your database to resolve my questions.

But... in a nutshell - any totals like this can and should be
calculated, on the fly, using Totals Queries or other calculations in
Queries. They should almost certainly NOT be stored anywhere, in any
table field. If you're assuming that the data must be in a Table in
order to be reported, revise your assumption - it's perfectly easy and
normal to base a Report on a Query.

John W. Vinson[MVP]

 

[amanda.redetzke]

Okay if I create a query from my budget table I still can't get it to
sum by what you suggest I could be doing it wrong though.

Fields:
Activity
Salary Budget
Budget Start Date
Grant End Date

Fiscal year = jan to dec

Table Entry Example:

10120202 50000 3/1/06 12/31/06
10120202 25000 1/1/07 2/28/07

How do I get those 2 lines to sum up into 1 line b/c that is the "grant
year" I want to look at?

In the table I have a field for "SumofSalary Budget"

No such field should exist. Tables are for DATA STORAGE - they are
*not* spreadsheets! If you store a SumOfSalary Budget field in your
table, the value that you store *will be wrong* as soon as any change
is made to any of the values underlying the sum.

and another field
named "Grant end Date". What I want is a formula that would allow me to
sum the Salary Budget By the Grant end Date.

Create a Totals Query; Group BY [Grant End Date] and Sum [Salary
Budget].

Currently I sum everything
on a fiscal year but now I need it according to the Grant end date. So
in order to sum it on a fiscal year I have made 2 entries for each
grant to separate out the different years. Now I just want the total of
the Salary Budget to coincide with the year of the grant. My Grant End
Date Field has a mmddyyyy.

I don't know if that makes sense or not.

Not really. *You* understand how your tables are structured, the
meaning of a fiscal year (for you, they differ), and so on. I do not,
and I cannot see your database to resolve my questions.

But... in a nutshell - any totals like this can and should be
calculated, on the fly, using Totals Queries or other calculations in
Queries. They should almost certainly NOT be stored anywhere, in any
table field. If you're assuming that the data must be in a Table in
order to be reported, revise your assumption - it's perfectly easy and
normal to base a Report on a Query.

John W. Vinson[MVP]

 

[Anthos]

Create a new query.
Then Add the fields from the table you want.

In the feild where you want it to add the numbers, Right Click and
select totals.
In the feild where you want to do the addition, change the Group By to
Sum

Hope this help

Regards
Anthos

Okay if I create a query from my budget table I still can't get it to
sum by what you suggest I could be doing it wrong though.

Fields:
Activity
Salary Budget
Budget Start Date
Grant End Date

Fiscal year = jan to dec

Table Entry Example:

10120202 50000 3/1/06 12/31/06
10120202 25000 1/1/07 2/28/07

How do I get those 2 lines to sum up into 1 line b/c that is the "grant
year" I want to look at?

In the table I have a field for "SumofSalary Budget"

No such field should exist. Tables are for DATA STORAGE - they are
*not* spreadsheets! If you store a SumOfSalary Budget field in your
table, the value that you store *will be wrong* as soon as any change
is made to any of the values underlying the sum.

and another field
named "Grant end Date". What I want is a formula that would allow me to
sum the Salary Budget By the Grant end Date.

Create a Totals Query; Group BY [Grant End Date] and Sum [Salary
Budget].

Currently I sum everything
on a fiscal year but now I need it according to the Grant end date. So
in order to sum it on a fiscal year I have made 2 entries for each
grant to separate out the different years. Now I just want the total of
the Salary Budget to coincide with the year of the grant. My Grant End
Date Field has a mmddyyyy.

I don't know if that makes sense or not.

Not really. *You* understand how your tables are structured, the
meaning of a fiscal year (for you, they differ), and so on. I do not,
and I cannot see your database to resolve my questions.

But... in a nutshell - any totals like this can and should be
calculated, on the fly, using Totals Queries or other calculations in
Queries. They should almost certainly NOT be stored anywhere, in any
table field. If you're assuming that the data must be in a Table in
order to be reported, revise your assumption - it's perfectly easy and
normal to base a Report on a Query.

John W. Vinson[MVP]

 

[amanda.redetzke]

That doesn't work b/c I need the grant end dates as part of my query
and they are different for each line.

Create a new query.
Then Add the fields from the table you want.

In the feild where you want it to add the numbers, Right Click and
select totals.
In the feild where you want to do the addition, change the Group By to
Sum

Hope this help

Regards
Anthos

Okay if I create a query from my budget table I still can't get it to
sum by what you suggest I could be doing it wrong though.

Fields:
Activity
Salary Budget
Budget Start Date
Grant End Date

Fiscal year = jan to dec

Table Entry Example:

10120202 50000 3/1/06 12/31/06
10120202 25000 1/1/07 2/28/07

How do I get those 2 lines to sum up into 1 line b/c that is the "grant
year" I want to look at?

On 1 Nov 2006 08:58:02 -0800, [email protected] wrote:

In the table I have a field for "SumofSalary Budget"

No such field should exist. Tables are for DATA STORAGE - they are
*not* spreadsheets! If you store a SumOfSalary Budget field in your
table, the value that you store *will be wrong* as soon as any change
is made to any of the values underlying the sum.

and another field
named "Grant end Date". What I want is a formula that would allow me to
sum the Salary Budget By the Grant end Date.

Create a Totals Query; Group BY [Grant End Date] and Sum [Salary
Budget].

Currently I sum everything
on a fiscal year but now I need it according to the Grant end date. So
in order to sum it on a fiscal year I have made 2 entries for each
grant to separate out the different years. Now I just want the total of
the Salary Budget to coincide with the year of the grant. My Grant End
Date Field has a mmddyyyy.

I don't know if that makes sense or not.

Not really. *You* understand how your tables are structured, the
meaning of a fiscal year (for you, they differ), and so on. I do not,
and I cannot see your database to resolve my questions.

But... in a nutshell - any totals like this can and should be
calculated, on the fly, using Totals Queries or other calculations in
Queries. They should almost certainly NOT be stored anywhere, in any
table field. If you're assuming that the data must be in a Table in
order to be reported, revise your assumption - it's perfectly easy and
normal to base a Report on a Query.

John W. Vinson[MVP]

 

[John Vinson]

Okay if I create a query from my budget table I still can't get it to
sum by what you suggest I could be doing it wrong though.

Fields:
Activity
Salary Budget
Budget Start Date
Grant End Date

Fiscal year = jan to dec

Table Entry Example:

10120202 50000 3/1/06 12/31/06
10120202 25000 1/1/07 2/28/07

How do I get those 2 lines to sum up into 1 line b/c that is the "grant
year" I want to look at?

Create a Query based on your table.
Add whatever expression is needed to convert your [Grant End Date] to
a "grant year" - you know that better than I do, I have no idea if
2/28/07 is in the 2007 grant year or whether the fiscal year starts in
March, June, or September. If it's the calendar year, you can put

GrantYear: Year([Grant End Date])

If it's some fiscal year you'll need a more complex expression
involving DateAdd - for example, if FY 2007 begins on October 1, you
could use

GrantYear: Year(DateAdd("m", 3, [Grant End Date])

Put a criterion

[Enter grant year:]

on this field.

Change it to a Totals query. Add the [Salary Budget] field to the
query; Group By the GrantYear field, and Sum the Salary Budget field.

Don't include the other two fields, unless you want to either group by
them or do some other totals operation with them.

John W. Vinson[MVP]

 

[John Vinson]

That doesn't work b/c I need the grant end dates as part of my query
and they are different for each line.

But in your previous message you say

How do I get those 2 lines to sum up into 1 line b/c that is the "grant
year" I want to look at?

You're contradicting yourself, or we're misunderstanding. Do you want
one line? or two?

John W. Vinson[MVP]