Formula Question?

[spyldbrat]

Hi,

I not very Access savvy, but I was wondering how to do two specific excel formulas in Access?

I have rows of data for each employee. Each row shows the pay week and the amount of hours worked in that pay period.



The first formula I need adds all the hours worked each week in the row. The excel formula looks like this: =SUM(C2:AI2)
The second formula creates an accrual based on the amount of hours worked each week. The excel formula looks like this: =+AJ2/30 (AJ represents the total hours from the above formula)

How can I create these formulas in Access for each row of data?

[June7]

Forget everything you know about spreadsheets when working with relational database.

Not sure what you mean by "adds all the hours worked each week in the row". What is field structure of table?

What do you mean by 'accrual' - want to add up all the weeks? Maybe you need to do an aggregate (GROUP BY) Totals query to summarize the hours data by employee. Use the query builder. Lots of info on web about building queries. Any introductory Access book will cover this.

[John_G]

Further to June7's remarks -

You cannot import data from Excel into Access, keep the same structure, and expect it to work the same way. It doesn't - the concept of Ranges does not exist in Access, even within the same record (row).

I suggest you find a good reference on database design, and become familiar with the concept of data normalization. A properly normalized data structure from the start will save you from all kinds a grief later on.

Here is one reference: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[vicsaccess]

spyldbrat, how are you trying to display this data? as an avid excel user I too was confused on how to do equations that I was used to doing easily in excel. if you are trying to take data from a table and display the result on a form, I would suggest doing some research on how queries work, then on how to base a form on a query. a big difference between excel and access is that in access we don't want to keep the results longer than the user is viewing it. we can always recalculate it when needed, no need is taking up storage space for results as long as we have the root information. what you are describing is a simple query with a groupby in the employee field and a sum in the hours field of a query. suggest doing a google search on access forms based on queries.