Referring to last date in table01 to run a query on table02

[dude]

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,

[Bruce Pick]

This was interesting to work on.

This query does the job:
INSERT INTO table01 ( dateField, textField )
SELECT table02.dateFie ld, table02.textFie ld
FROM table02
WHERE (((table02.date Field)>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,

[Bruce Pick]

Possibly an easier way is to have a boolean field "blnExporte d" in
table02 that has a default of No.

You build an Append query that appends WHERE blnExported=No.
Immediately after running it, you run an update query that sets all
those blnExported fields in Table02 to Yes. You don't need to export
the boolean field to table01, and you don't need to track dates if you
don't need them for other purposes.

That method might run more quickly if you eventually end up with lots of
records for it to test.

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,