Adding Date to Append Query

[jcbhydro]

Good evening,

I have an Append Query which appends personal details from a Mail List Table to a Leavers Table when members Leave the organisation. The coding used to achieve this is as follows;

INSERT INTO Leavers ( [Member ID], Surname, [First Name], [Address 1], [Address 2], Town, PostCode, Phone, [E-Mail], Notes, Joined )
SELECT [Mail List].[Member ID], [Mail List].Surname, [Mail List].[First Name], [Mail List].[Address 1], [Mail List].[Address 2], [Mail List].Town, [Mail List].PostCode, [Mail List].Phone, [Mail List].[E-Mail], [Mail List].Notes, [Mail List].Joined
FROM [Mail List]
WHERE ((([Mail List].Leaving)=True));

I am trying to add the date of leaving to a LeaveDate field in the Leavers Table when the Append Query is activated. I have tried adding Date() to the append data but without success.
Any suggestions to achieve this apparently simple procedure would be appreciated.

Regards,

jcbhydro

 

[plog]

I have an Append Query which appends personal details from a Mail List Table to a Leavers Table when members Leave the organisation

Why?

A well structured relational database doesn't achieve what you want to achieve in this way. Data shouldn't be moved around. When you do that you are essentially storing a field value in a table name. I mean, you don't have a table for every single Post Code do you? No, you have a field and that tells you what Post Code a person is in.

You should aachieve this the same way. Instead of a whole new table, you simple need a field ([LeaveDate]). When someone leaves, put a date in there to denote they left.

 

[Stormin]

I agree, you should have two select queries selecting from one main table instead of two tables.
i.e. Query1 selects all current members of the mailing list (LeaveDate Is Null)
Query2 selects all ex-members of the mailing list (LeaveDate Is Not Null)

Otherwise you will have to append the leavers to another table, then delete the members from the first table.

If you want a date for when the query runs, you can just have an update query that updates LeaveDate to Date() where Leaving = True.

I don't know what you use the data from the table for, but using a select query as a datasource is (in my experience) exactly the same as using a table.



and for what it's worth, you can use the date in an append query. But you won't need to in your case (please don't). E.g.:

INSERT INTO [Left] ( ID, FirstName, Surname, PostCode, Joined, DateLeft )
SELECT Members.ID, Members.FirstName, Members.Surname, Members.PostCode, Members.Joined, Date() AS QueryRunDate
FROM Members
WHERE (((Members.Leaving)=True));

 

[jcbhydro]

Thank you plog & stormin for your comments.

Unfortunately, I have inherited a 'poorly structured' database as you describe it and have to live with what I've got. There is no possibility of rebuilding the database with a more ideal structure, so your, no doubt, entirely valid suggestions are not feasible.

I have found a way of achieving what I need to do with the addition of a 'LeaveDate' field and a minor mod. to the Append Query.

Regards,

jcbhydro