possible with cell formula in access?

[rantz]

I have just abandoned excel in favor of access because excel can't
handle all my data. I don't know that much about access.

I have a table in access with date + time in one cell (Swedish format)

Date&time
2005-06-01 00:08:32
2005-06-01 00:08:36
2005-06-01 00:09:02
2005-06-01 00:09:07
2005-06-01 00:09:41
2005-06-01 00:10:51
2005-06-01 00:11:52
2005-06-01 00:13:03

I need a column next to this with accumulated time. Like this:

Date&time accumulated minutes
2005-06-01 00:08:32 0
2005-06-01 00:08:36 0,066666661
2005-06-01 00:09:02 0,499999991
2005-06-01 00:09:07 0,583333333
2005-06-01 00:09:41 1,149999996
2005-06-01 00:10:51 2,316666663
2005-06-01 00:11:52 3,333333327
2005-06-01 00:13:03 4,516666664

In excel this is no problem. I simply use formula '=A3-$A$2' and
copy it down.

The problem is I can't do this in access and the data has 320000 rows
so I can't use excel either.

Can anyone help me with this?

 

[Norman Yuan]

I am afraid that you "abandoned" Excel and "in favor" of Access with wrong
reason. regardless whether Excel can do for more than 320000 rows of data or
not, you DO NOT use Access for the reason you described. Access table is for
storing data in a relational structure, and one rule of it is not storing a
value that can be calculated from other stored data.

In your case, it is fine to store the date/time (with other possible related
data in the same or different table(s)). The calculated timespan is only
calculated and displayed on the user interface level, say, form or report.
or even in Excel, which is used the get row data (date/time) from Access and
does the calculation.

 

[rantz]

OK, some background. The data was originally 12 text files exported
from a database. I have imported the text files to 12 excel sheets (one
for each month, approx 25'000 rows/sheet) and done my calculations.
When I was done calculating I imported the sheets to Access to have all
rows (approx 320'000 rows) in one table. I was not intending to do
any more calculations.

I will use the table in access as input data to run a simulation model
in Rockwell Arena.

I realize that this new column "accumulated minutes" can be
calculated from other data and that it creates redundant data. But I
need this as input to my simulation model.

Now I want to add this column "accumulated minutes" to my access
table. Is my only option to do the calculations in excel and import the
data again or can this be done in Access?

 

[Joseph Meehan]

I have just abandoned excel in favor of access because excel can't
handle all my data. I don't know that much about access.

I have a table in access with date + time in one cell (Swedish format)

Date&time
2005-06-01 00:08:32
2005-06-01 00:08:36
2005-06-01 00:09:02
2005-06-01 00:09:07
2005-06-01 00:09:41
2005-06-01 00:10:51
2005-06-01 00:11:52
2005-06-01 00:13:03

I need a column next to this with accumulated time. Like this:

Date&time accumulated minutes
2005-06-01 00:08:32 0
2005-06-01 00:08:36 0,066666661
2005-06-01 00:09:02 0,499999991
2005-06-01 00:09:07 0,583333333
2005-06-01 00:09:41 1,149999996
2005-06-01 00:10:51 2,316666663
2005-06-01 00:11:52 3,333333327
2005-06-01 00:13:03 4,516666664

In excel this is no problem. I simply use formula '=A3-$A$2' and
copy it down.

The problem is I can't do this in access and the data has 320000 rows
so I can't use excel either.

Can anyone help me with this?

You would normally store just the date-time (Access uses a single filed
to store both and displays the parts wanted) The calculation you want would
be done in a query, form or report. In this situation you have a little
more difficult issue as Access does not have rows. That is data is not
stored in an orderly fashion. A table is more of a bucket of data than a
list of data. In a query etc. you can sort the data. In your case I am
going to guess you will always want it sorted in date-time order so that is
good.

I have not used Access where I have had to reference a prior records
value, but it is not an unusual question. Hopefully someone will offer you
an solution for that or maybe you can do some searching to find an example
given in answer to someone else's question.

Good Luck

 

[Guest]

As Norman already posted, tables are for storing values, not calculated data.
However, your request can be accomplished with a query, which can then be
used as the data source for your simulation:

SELECT Table1.DtTm, ([DtTm]-(SELECT Min([DtTm]) AS Minimum
FROM Table1))*60*24 AS AccumulatedMinutes
FROM Table1;

In the above query, please substitute your table name for "Table1", and the
name of your Date/Time field for "DtTm".

Since the query uses the minimum value of the Date/Time field to do
calculations, it should not be subject to any Sort Order problems of the type
described by Joseph.

Note: When duplicating the Excel file you described below, your formula
'=A3-$A$2' did not give me the results you described, perhaps due to a
difference in the way our systems deal with Date/Time fields. In order to
duplicate your results in Excel, I had to change the formula to
'=(A3-$A$2)*60*24'. This has been incorporated into the above query, which
on my system gives me the same results in Access as your Excel results below.

-Michael