This was interesting to work on.
This query does the job:
INSERT INTO table01 ( dateField, textField )
SELECT table02.dateField, table02.textField
FROM table02
WHERE (((table02.dateField)>DMax("[table01].[datefield]","[table01]")));
You must have at least one record in table01 with a date in it for this
to work.
I assumed that you want to append table02 records where that date is >
the most recent (maximum) date in table 01. You didn't indicate that
you're comparing a particular record in table02 to a particular record
in table01 - so I went for the max date in table 02.
I used DMax("[table01].[datefield]","[table01]") to get that maximum date.
I built a table01 and table02. Each has a fieldDate and fieldText, and
also a fieldAutoNum that is effectively irrelevant to this task.
If you're not used to working with straight SQL in Access - -
Create a new query, but add no tables or anything.
Click View - SQL. Paste in the SQL from above.
Go back to design view if you like.
Of course you need to add any other fields you have that need to be
"copied" to your table01.
Don't try to copy data from an AutoNum field as table01 will maintain
its own AutoNum field if it has one.
To see where the destination table is specified, click Query - Append.
To run it from design view, click Query-run, or click the big red ! on
the toolbar.
(I had to write that, since it's a real drag if you don't know where to
run it from.)
Bruce Pick
du**@coolgroups.com wrote:
Hi there,
I have 2 tables, one with data in it, where each record has
a date, this table being table01. I want to apend data to
this table, but from Tabl02, but only new data, so
essentially append to table01 from table02 where dateField
in Table02 is greater than dateField in table01...
Hope someone is able to help, I have searched MS access
2000 help thouroughly, but haven't been able to find
anything helpful.
thx,