Using the latest date in a table as criteria

[macc boy]

Hi - and thanks for reading this.

I have a table (let's call it 'Sales') and I want to append to it from
a number of other tables (the bits that make up the sale) if the
transaction date is later (larger) than or the same as the latest date
in the Sales table.
The query I currently use uses about 5 ODBC tables linked together and
I manually put the date criteria in - and it works well. How can I use
the latest date in the Sales table as criteria so it will run on its
own?

Thanks.
maccboy

 

[John W. Vinson]

Hi - and thanks for reading this.

I have a table (let's call it 'Sales') and I want to append to it from
a number of other tables (the bits that make up the sale) if the
transaction date is later (larger) than or the same as the latest date
in the Sales table.
The query I currently use uses about 5 ODBC tables linked together and
I manually put the date criteria in - and it works well. How can I use
the latest date in the Sales table as criteria so it will run on its
own?

Thanks.
maccboy

Ummmm...

Why?

It sounds like you're copying data from a query set of normalized tables into
a redundant, non-normalized table.

Why not use *the query itself* as the source for a Form, for a Report, for
data display, etc.?

If you do have a good reason to do this (say, the ODBC tables can change,
you're not in charge of them, whatever) then use a criterion on the date field
of

=(SELECT Max([datefield]) FROM Sales)

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[macc boy]

Hi - and thanks for reading this.

I have a table (let's call it 'Sales') and I want to append to it from
a number of other tables (the bits that make up the sale) if the
transaction date is later (larger) than or the same as the latest date
in the Sales table.
The query I currently use uses about 5 ODBC tables linked together and
I manually put the date criteria in - and it works well. How can I use
the latest date in the Sales table as criteria so it will run on its
own?

Thanks.
maccboy

Ummmm...

Why?

It sounds like you're copying data from a query set of normalized tables into
a redundant, non-normalized table.

Why not use *the query itself* as the source for a Form, for a Report, for
data display, etc.?

If you do have a good reason to do this (say, the ODBC tables can change,
you're not in charge of them, whatever) then use a criterion on the date field
of

 >=(SELECT Max([datefield]) FROM Sales)

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John.

Thanks for the response.
I'll try and explain why! Each day, new transactions are added to the
ODBC tables and I need to report on them - and previous days' and
weeks' transactions. I need, therefore, to keep my table up to date.
I've created a primary key to avoid duplicates and I just want to be
able to add the past few days' transactions without refreshing the
whole table each time.
maccboy

 

[John W. Vinson]

Thanks for the response.
I'll try and explain why! Each day, new transactions are added to the
ODBC tables and I need to report on them - and previous days' and
weeks' transactions. I need, therefore, to keep my table up to date.
I've created a primary key to avoid duplicates and I just want to be
able to add the past few days' transactions without refreshing the
whole table each time.
maccboy

Did the subquery criterion work?

Still... you ALREADY HAVE THE DATA (in the ODBC tables). It's not going away
daily, is it? Can you not just use a query selecting last week's or last
month's data, rather than storing the data redundantly, in two copies, one in
the ODBC tables and a different copy in your table? If you do store it
redundantly, do you have any provision for keeping the two copies in synch if
someone corrects errors in the ODBC tables?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[macc boy]

Did the subquery criterion work?

Still... you ALREADY HAVE THE DATA (in the ODBC tables). It's not going away
daily, is it? Can you not just use a query selecting last week's or last
month's data, rather than storing the data redundantly, in two copies, one in
the ODBC tables and a different copy in your table? If you do store it
redundantly, do you have any provision for keeping the two copies in synch if
someone corrects errors in the ODBC tables?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John

Your suggestion worked beautifully! I want to update the tables so
that I can hold the information locally and run various reports from
it without having to re-access the ODBC tables - which have over
300,000 records.

Thanks.
macc