Append or Update Query Solution

[zbaker]

I am looking for a solution for the following problem.

I have 10,000+ records that need updated each month transitionally ie:

ID Date Cost
2 1/1/14 15000
2 2/1/14 16500
2 3/1/14 12000
3 1/1/14 100
3 2/1/14 100
3 3/1/14 100

Is there a way to update a table monthly so that instead of typing the month in for each record it will add a new line for the new month each time the query is run?

[June7]

What does 'transitionally' mean?

Essentially anything can be done with enough code. However, I don't really understand what you want. You want to edit or create records? With what data from where?

[zbaker]

Sorry about my typo i mean Transactionally, for each ID I want to add a record with the new month

[June7]

Maybe an INSERT SELECT will work for you. Do you have a master table of the IDs - where ID is a unique identifier?

INSERT INTO tablename(ID, [Date]) SELECT ID, Date() AS NewDate FROM IDtable


BTW, Date is a reserved word. Should not use reserved words as names.

[zbaker]

I do have a Master table with ID being the unique identifier.

[zbaker]

How do I go about doing that, my guess is SQL?

[June7]

Use the query builder SQL view to type an SQL statement. Then switch to Design View.

If you want some date other than today, say the 1st:

INSERT INTO tablename(ID, [Date]) SELECT ID, DateSerial(Year(Date()), Month(Date()), 1) AS NewDate FROM IDtable

In VBA procedcure:

CurrentDb.Execute "INSERT INTO tablename(ID, [Date]) SELECT ID, DateSerial(Year(Date()), Month(Date()), 1) AS NewDate FROM IDtable"

[zbaker]

If i just wanted it to say Month and Year would it be the following?
INSERT INTO tablename(ID, [Date]) SELECT ID, DateSerial(Year(), Month()) AS NewDate FROM IDtable

[June7]

Can't have just month and year in a Date/Time type field. Also, the DateSerial arguments are not optional.

[zbaker]

Thanks for your help!