Recording dates and tmies of queries 1

[Duplicity]

Here we go:

Imagine three tables, called A, B and C respectively.

Each table has the same fields: ID [autonumber], Field1, Field2 and Field3.

If I wanted to move information out of one table and into another, then again into another, then possibly back to any one of the three, I would like to be able to record the date and time that a row of information was moved.

I'm thinking along the lines of a jumped up append query or some such to insert a row from one to another tables, but I have no idea of how to:

1. remove a row completely from one table and insert it into another.

2. record the date that the row was moved.

3. record the time that the row was moved.

Three tables of the same layout, move a record from one to another table [remove and append/insert] and record the date and time of the move.

I hope I made that clear enough.

Any polite suggestions would be very much appreciated.

 

[BNPMike]

You can't add and delete in one go so you will have to insert (append) and then delete.
Add a date/time field in the target table and set it to default to Now(). It will automatically record the time it was moved.

 

[Duplicity]

Easy for you to say but for an infrequent and often confused Access user, any further hints ?

Thanks for the very rapid reply though, if I only knew how to implement it...

 

[BNPMike]

I don't want to get at you here, but this is a bit like saying "I drive my mother's car infrequently so can you give me some hints on when to use the brake pedal?" I would really recommend you get a book on Access or at least work through the Access Help.

It's unusual to need to move records back and forth between tables. You may want to tell the Forum what you are trying to achieve. Most likely someone can propose a simpler solution.

 

[Duplicity]

Each table corresponds to specific tests or overchecks performed on those items listed in them.

A record in table "A" may be flagged for attention for a certain timespan.

If the necessary work has been completed on the record within that timespan it will be moved to the next stage of investigation which could be table B or C [etc] depending on the nature of the entry and which person will handle it or the tests/checks that will need to be done next.

If the item fails at a certain point it may have to be returned to a previous stage and progress once more through the various stages.

Output will be sent to Excel for reporting purposes and further manipulation.

I need to be able to easily see when a record was moved from one to another stage. I hope to be able to record the date and time when the record is moved, the date and time of each subsequent move, possibly also recording which table it was moved to as well - a when and where system if you will.

It's a concept I'm bouncing around and I'd like to implement it in the long term. It's not mission critical but it would be very useful over time.

Is that clearer or did I just complicate things even more ?

;o)

 

[BNPMike]

May I suggest that each stage is actually an attribute of the item. Instead of each table representing a collection of records in a stage, why not simply have a field (or fields) in one table which denote(s) which stage the item is at?

As to tracking, you could have for example:

ItemKey, (various data), Stage_A_DateTime, Stage_B_DateTime, Stage_c_DateTime.

As the item is first entered, Stage_A_DateTime is set to Now(). The other timestamps are null. You can now select this as a Stage A record. When the item moves to Stage B you set Stage_B_DateTime to Now() and so on.

If the item is knocked back to Stage A you could just set Stage_B_DateTime back to null, and possibly set Stage_A_DateTime to Now(). If however you wished to track the move from A to B to A (in the general case) you would need to add another table like this

ItemKey,FromStage,DateTime.(possibly ToStage)

It depends whether you need an exhaustive audit trail or just need to know where you are.

 

[Duplicity]

That's an interesting idea and might just do the trick.

I'm going to think about it a bit more and see if I can imlpement something along those lines.

Many thanks for your input. It's given me a springboard to start from and simplified the database design enormously from the concept I'd been bouncing around.

I'm off to play with Access nw and see if I can pull this off. If you see a mushroom cloud go up it'll just be me trynig to get my head around Acess.

;o)

Thanks again. I'll try and let you know how I got on, or not as the case may be.