Append Query Based on Max Date

[Schwimms]

I have to tables that are exactly the same data..One is historical the other
is the new data. Whats the criteria that I enter into the Append query to add
on the new data based on dates?

IE I have a billing status date in the historical from 2/1/06 thru 4/20/08 I
want to add on the new data starting from 4/21/08. There is data in the new
data report that goes back to 4/1/08.

 

[Wayne-I-M]

Add

#4/21/08#

to criteria row

 

[Ken Sheridan]

Wayne:

I think you meant >= #4/21/08#

Ken Sheridan
Stafford, England

 

[Schwimms]

Hrmm..I would like to only add on the new data if it is more current then
what is in the history data.... The thing is this date changes and what I
would like to do is build a macro that appends the data without me looking at
the history for the max date...so I want the query to take the new data and
add the the DMAX of the history

 

[Wayne-I-M]

ooops

: - )


--
Wayne
Manchester, England.

Wayne:

I think you meant >= #4/21/08#

Ken Sheridan
Stafford, England

 

[Wayne-I-M]

Hi

You have small problem of having the same date in the database twice - not
good.

Of course you could use a query to show if the date in one table is later
(or equall to, as Ken said) than some other date in another table

But ??

This is just asking for problems. You should only store the data once.
Then (just an idea) add (if you need) another table with dates and the
primary field as link from the 1st table. This would allow you to alter the
data without it being wrong somewhere else in the DB and add as many dates as
you like.

If it were me I would spend a little time redoing the tables and adding the
relationship to a date table. This really make it all much better.

Hope this helps

 

[Schwimms]

Im not sure if im coming accross wrong, you are saying there is no way I can
append the new data to the historical by having access figure out the max
date from the historical and adding in from the new data anything greater
then that max date?

 

[Schwimms]

You've all got me so lost. I am adding in unique data based on dates. I have
a history file with data from a column called "billing status date", the data
is from 4-5-05 thru 5-2-08, I am then adding on data from a column called
"billing status date" in my new table, the data is from 5-3-08 thru 5-5-08
"cinco de mayo!". How am I creating a problem? There is no name of the field
as historical. I feel like you are speaking a language I don't understand.

This is exactly whats happening:

-The historical table name is "GSP"
-I then have a new table called "PICSSHIP" that I get emailed daily
-I have a macro that imports PICSSHIP in
-I then want to add to that macro an append query that adds in the new data
based on the billing status dates.

Am I still making a mistake?

 

[Schwimms]

I figured it out... I just need to insert this into the criteria of the
append query:

(SELECT MAX([Billing Status Date]) FROM [GSP])

Im going to give myself a post is helpful remark