Duplicating Cell

[Seb]

Hi Guys

Not sure what I should do here. I would like to have a cell populate with the value of a specific cell previously.
explain:
in excel, we can use formulas like this:

A1: 00:00 B1: 08:30
A2: 08:30 B2: 13:00
A3: 13:00 B3: 15:00
etc, etc
Notice that each cell in A is the same as the previous B Cell. So the formula for Cell A2 would be something like: =IF(B1>0,B1,"00:00")
and Cell B3 would be =IF(B2>0,B2,"00:00")

The fields I have in Access are:

TimeIn TimeOut

So I would need to get TimeIn to be the same as the previous timeout???
Not sure If I'm making any sense right now

 

[neileg]

If TimeIn is always going to be the same as the previous TimeOut, why do you need to store it again?

 

[access10000]

Maybe Seb needs to have it stored in a table and doesn't want to bother entering the same info twice. Seb - look at the attached.

Run the 2 queries. If you put In times into table In1 and run the TimeIn query it makes a new table and adds a column with Out times. The other table and query does it the other way round based on entering out times in table Out1. This will add the In times to the right of the Out times. As they are tables this is irrelevant though. Run a select query to get them in the right order or just change the whole thing to a select query if you need it that way.

Hope it helps. Is this what you need?



Neil

 

[Seb]

Sorry for the delay in getting back to you. Been on holiday

not sure really. I would prefer to do it on the fly.....the times will always be the same. you stop working at 5pm. you start resting at 5pm. your start working at 8am. etc...

The query looked good but I would prefer to have it happen straight away on a form, than running queries

I'm lost on this one!

 

[gemma-the-husky]

the thing is, that unlike excel, access can only see one row at a time. also unlike excel access cannot easily locate the previous row - indeed in truth there is no such thing as a previous row - everything is realy just a "set" of data. therefore if you want a particluar row related to the one you are using you need to find it yourelf with some vba code

an alternative is that we we store the new row, we first lookup the value from the previous row, and store it in the row - after all in excel you are doing that - you are storing the old value in the new row

although access looks like excel, it isnt quite - i think its far more powerful for data handling, but it brings with it more rigorous requirements for data handling

 

[access10000]

Hi Seb

Could you just have a form where you enter one set of times and set the query to run when another form opens which fills in the other set of times?

Set the query as an action in a macro and then set the macro as the 'On Open' property of the form you want to use to list all the times.

As a base for what you are doing it seems like this query should do what you need but it would need to be tied in with the rest of your system. If you post a sample I could have a look at it but I've only got ready access to Access 97.