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?

 

[Ken Sheridan]

You can do it by referencing the MAX value of the date column in the archive
table by means of a subquery:

INSERT INTO YourTable_Historical
SELECT *
FROM YourTable_New
WHERE BillingStatusDate >
(SELECT MAX(BillingStatusDate)
FROM YourTable_Historical);

The first point at issue, though, is whether you should have an archive
table at all. What it is doing is in effect encoding data (the fact that a
row is 'historical') as a table name. In a relational database data should
be stored only as values at column positions in rows in tables. In your case
you could add a Boolean (Yes/No) could named 'Archived' or similar and set
this to True when a row is archived. To work only with current data you'd
use a query 'WHERE NOT Archived'.

This assumes that with your present arrangement all values in the two tables
will remain the same. If after archiving a row values in the 'new' table can
change, however, then obviously a second table is needed. However, this
table should only contain the key column(s) necessary to relate the two
tables one-to-one and the columns whose values can change vis-à-vis the two
tables. This avoids the redundancy which having columns whose values will
remain the same vis-à-vis the two tables, and, the crux of the matter, the
possibility of inconsistent data to which this gives rise. While this might
seem unlikely, good database design respects Murphy's Law, which states that
if something can go wrong then sooner or later it will go wrong.

Nobody would be so proscriptive as to say you must not keep your present
arrangement. The choice is yours, but you should be aware of the design flaw
and the risk of inconsistent data resulting from it.

Ken Sheridan
Stafford, England

 

[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