473,287 Members | 1,418 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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,
Nov 12 '05 #1
2 1648
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,

Nov 12 '05 #2
Possibly an easier way is to have a boolean field "blnExported" 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,

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: AndyBell | last post by:
Hi all! I have an Access 2000 database for the Habiat for Humanity where I work. This is the second database I have written and it gets a bit more complex each time... I have learned much and...
3
by: Melissa | last post by:
I have this table: TblProjectYear ProjectYearID ProjectYearStartDate ProjectYearEndDate The Project Year will always span across December 31; for example 9/1/04 to 6/30/05. How do I build a...
1
by: samotek | last post by:
Filter the last date only I am trying t0 filter only the last date of the query,but i cannot manage that. My query is the following : SELECT Sum(.liters) AS SumOfLiters, orders.invoicedate...
2
by: solar | last post by:
I have a problem with building a totals query showing data for the last date only.Under the last date i understand the last date of field invoicedate.This invoice date is in the table orders....
9
by: ice | last post by:
Hello, I have a couple of tables. The client tables and the contacted tables. I am not sure how to start on this, what I need is a way to query all my clients then show any client that the...
2
by: dauwe.peter | last post by:
A table : Nameperson, Book nr, Bookdatein, Bookdateout, CD nr, cddatein, cddateout, dvd nr, dvddatein, dvddateout. I would like a query where a see the personsname en de last book with the...
3
by: paii, Ron | last post by:
I have a sales table that logs sales information for each active job at the end of each month. My question is, will the following query give me the last sell price for each job in the sales table?...
2
oszapped
by: oszapped | last post by:
I am new to creating SQL queries and need some help. I am trying to query from three tables. 1 - A table with a list of Drawings (DRAWINGS) 2 - A table with the Revision history of the Drawings...
2
by: scott | last post by:
Hi Everyone, I have a table which has many fields in it but I need to pull some specific info via a query and I don't know if it's possible. I want to run a query which includes two tables....
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.