Moving data from one table to another

[cb@chrisb.eu]

Hi.

SELECT * FROM [Patient Details] where [Life Status] = "Deceased"
COPY INTO [Deceased Patients]
Delete * from [Patient Details] where [Life Status] = "Deceased"

Access says missing operator. Any idea which one?

 

[pbaldy]

For starters, it is generally not a good idea to move data around like that. You lose the ability to query across the different types. Using that status and leaving the data in a single table enables you to query deceased, not deceased, or both together as appropriate to the situation. In any case, the proper syntax for the first query is:

INSERT INTO DestinationTableName (Field1, Field2...)
SELECT Field1, Field2...
FROM SourceTableName

 

[cb@chrisb.eu]

Hi. I'm aware of the dangers, hence frequent backups, split database. The client has requested that deseased patients are moved to a different table, as the 'live' database is primarily used to send out letters and update clinical data. I also intend to insert a password loop to reduce errors. So Q1: can I keep my 'delete' line? Q2: both these tables are identical (DP was formed from the structure of PD). Do I have to list all (33) fields, or can I use a summary phrase?

 

[pbaldy]

With all due respect to the client, unless they are experienced relational database designers, they should be dictating functionality, not design. I'd still have them in one table, and restrict letters etc with query criteria or other techniques available to restrict forms and reports.

In any case, since I'm sure you're determined to stay with this design, yes you can keep the delete query, and if the table designs are identical you might be able to use

INSERT INTO TableName
SELECT *
...

I'll point out that having tables with identical fields is always a warning sign of a normalization issue.

 

[cb@chrisb.eu]

Thanks John. Revised:
INSERT INTO [Deceased Patients] (ID, [Patient ID], [Hospital No], Forename, Surname, [Client Type], [NHS No], DOB, Sex, [Ethnic Origin], [House No], Address, Area, [Town/City], [Post Code], [Tel No], [E-mail], [Health Visitor Details], [Patient Referral Date], GP, [At Risk Couple], CaseManager, [Life Status], [File Status], [Family Status], [MothersID], [FathersID], Comments, Comments1, Comments2, Comments3, Comments4, Recall, [Closed Date])
SELECT *
FROM Patient details where [Life Status] = "Deceased"
DELETE * from [Patient Details] where [Life Status] = "Deceased"

I’ve now got a syntax error (missing operator) for: /where [Life Status] = "Deceased"

DELETE * from [Patient Details] where [Life Status] = "Deceased"/

Life Status is the name of the field. The two option drop down box is either Living or Deceased. Is it my where statement phrasing that's wrong? I've matched it (and the delete where) against other examples...

 

[pbaldy]

I hope John is somebody in your office.

You realize those would have to be separate queries? Access can't include multiple statements in one query. I may have misinterpreted your earlier question when I said you could keep it. You need it, but as a separate query. You'd execute the append query and then the delete query.

 

[cb@chrisb.eu]

Hi again Paul.
The routine is working well on change, but it also runs when people change back (eg if a mistake). Is there a 'one way only' or 'only choice 2' easy variant of the 'on change' trigger?

 

[pbaldy]

Setting aside the fact that I wouldn't do this at all, the place to do it would be the after update event of the form, which should only fire when the user is done. I would probably also restrict it to the single displayed record.

 

[cb@chrisb.eu]

Hi Paul. I've moved it to After Update, but it still fires up on the change from Deceased back to Living. Surely there must be some way to say that if Life Status = "Living", do nothing, else run query (or vice versa)?

 

[pbaldy]

Sure, this type of structure:

If Me.ComboName = "Deceased" Then
  'Do something
Else
  'Do something else
End If