Need to move data from table a to table b and delete it

[kiask2343]

Well I have two tables, one is the workorder register and the other is the closed work orders. This is for maintenance. I have a form for creating and veiwing workorders. I need to link a command button to the form that will delete the record being viewed in the WO REG table and paste it in the Closed WO table. I think I need to use two append queries but have no idea what I am doing. Here is what I have so far.

INSERT INTO [CLOSED WO] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
SELECT [WO REG].[WO], [WO REG].[MMCN], [WO REG].[TECH], [WO REG].[NOMIN], [WO REG].[FUALTS], [WO REG].[TYPE], [WO REG].[SECTION], [WO REG].[CLOSEDATE], [WO REG].[OPENDATE]
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].[MMCN]=[WO REG].[MMCN]
WHERE ((([WO REG].[WO])=[WHAT WO TO CLOSE?]));

Like I said I have no idea what I am doing, this is out of the SQL view for my "move to closed WO" query. the fields I need to move are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, and OPENDATE.

Is this even remotly right? I know nothing of VBA an all these symbols mean nothing to me.

 

[FoFa]

OK, question, why do you have seperate tables instead of a closed flag/date something like that and just leave them in that one table.
If you just have to go down this road, I would create a number of queries that obtain the required key information from your form (call it workorder number).
Query one returns a count of the number of records in your closed table with that workorder number (you would expect zero, or you have a problem).
Second query would copy the data from your open to your closed table.
Third query would inner join the two tables and return a count of the workorder number (you expect it to be one, which because of the inner join means the copy was good).
Four would delete the data from the open table.
Fifth would return a count from your open table (you would expect zero now or the delete failed).
You also have to have the form move off the record before you start this process (you are tying up that record with your form).
You have to have checks in between each query in case you get a result you do not expect.
You do not want to delete the open workorder if the copy was unsuccesful as an example.
Just having an open/closed date and/or a closed flag and leaving the data in one table is much easier.

 

[kiask2343]

Ok you sold me, it is much more compliated than I would have ever thought.

So....now how do I do the open/close flags? And what are they?

 

[FoFa]

Best thing to do, (and some don't like duplication of effort, but I like easy). I would just add a YES/NO field, default it to FALSE and call it CLOSED. Also set a date and when you close a workorder, set the date and the CLOSED flag. That way you have the date the workorder was closed, but you can use the CLOSED flag in your queries. Example, if you are only looking at open work orders, then CLOSED = FALSE. simple, easier than CLOSEDDATE IS NULL, besides CLOSED = TRUE or CLOSED = FALSE even makes sense.

 

[kiask2343]

That will work, thanks for the simple answer. You have helped me a bunch.