By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

creating dynamic formulaes in Access

P: 16
Hi guys,

Its my first post and i hope that you will answer my query.

I want to transfer my salesdata report from excel to Access and The issue I am facing is that there are lot of formulaes in my excel file which are used dynamically.for eg.I have this monthly sales data
jan feb mar apr
30 40 10 56
So YTD sales for FEB would be sales of jan+sales of feb and so on.
In excel you put a formulae in the feb column and drag it to any range you want.
And normally I have sales data for more than 36 months.

But I want to link this file in access so that the query does this calculation for me everytime new months are added.
Is there any way I can make the calculation dynamic in access.
Just to inform that I am using Office 2002.
Thanks
Victor
Sep 11 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi guys,

Its my first post and i hope that you will answer my query.

I want to transfer my salesdata report from excel to Access and The issue I am facing is that there are lot of formulaes in my excel file which are used dynamically.for eg.I have this monthly sales data
jan feb mar apr
30 40 10 56
So YTD sales for FEB would be sales of jan+sales of feb and so on.
In excel you put a formulae in the feb column and drag it to any range you want.
And normally I have sales data for more than 36 months.

But I want to link this file in access so that the query does this calculation for me everytime new months are added.
Is there any way I can make the calculation dynamic in access.
Just to inform that I am using Office 2002.
Thanks
Victor
Hi Victor,

In Access you would store the information differently. For instance

tblMonthSales
MonthSalesID
SalesYear
SalesMonth
SalesAmount

On that basis you can run a query as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT SalesYear, Sum([SalesAmount])
  2. FROM tblMonthSales
  3. WHERE SalesYear = Year(Now())
  4.  
For more information on how to structure your tables have a look at this tutorial.

Database Normalisation and Table Structures
Sep 13 '07 #2

P: 16
Hi Victor,

In Access you would store the information differently. For instance

tblMonthSales
MonthSalesID
SalesYear
SalesMonth
SalesAmount

On that basis you can run a query as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT SalesYear, Sum([SalesAmount])
  2. FROM tblMonthSales
  3. WHERE SalesYear = Year(Now())
  4.  
For more information on how to structure your tables have a look at this tutorial.

Database Normalisation and Table Structures

Hi
Thanks for replying,But Sorry I am still confused.
Do you mean I should make different tables for sales,month,and year.
Also I forgot to mention in my query that all these sales are for products which are thousands in number.so actually data looks like this.
Prod Jan04 feb04 mar04 April04......sep07
AA 30 40 50 60 58
BB 70 40 50 60 57
CC
...
...

I read that link,but I still didn't get it, how should I seggregate the data so that I can make a simple running formulae(sum) dynamic in Access.so that I can link the this data and create a YTD query and when you run it you not only get the ytd for the previous months but new months as well which are added.

Hope I am not asking too much
Sep 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
This is one table.

tblMonthSales (Just the table name)
MonthSalesID (Primary key)
ProductID (Foreign key - referencing Primary key of the products table)
SalesYear (Store the year)
SalesMonth (Store the month)
SalesAmount (Store the sales amount for the month)
Sep 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.