Using max date as criteria in append query?

[Sam_NY]

Hello,

I'm trying to create an append query that adds records only if a date stamp field in the source table (a linked table called "BillHx_Auto_Test") is greater than the most recent date stamp field in the destination table (called "BillHx").

Both the destination and source fields are called "DateStamp"

In design view, I tried changing the criteria for the source table DateStamp field to

>Max([BillHx].[DateStamp])

Which generated the error "Cannot have aggregate function in WHERE clause"

I also tried creating a separate totals query (called BillStatus_Max) that returns the Max record from the destination DateStamp field, and then uses that in my criteria for the DateStamp field on the append query like so:

>[BillStatus_Max].[MaxOfDateStamp]



This just opens an alert prompting me to "Enter Parameter Value" Pretty sure that's not what I was looking for.

I'm out of ideas. Any ideas about how I can do this? The idea is that I will be updating the linked source table (actually it'll be updating itself through a web connection) and then using the append query to just add the most recent records to my Access database.

Thanks for any suggestions/help! Any advice going beyond the design view would be fine, but I would appreciate help in deciphering, as I'm not that proficient in either SQL or VBA.

[ranman256]

do you mean like:

if DMax("[datestamp],"BillHx_Auto_Test") > DMax("[DateStamp]","BillHx") >then
  docmd.OPenquery  "qaAppendData"

endif

or do you mean append if the record datestamp is > the one record in main table?

[Sam_NY]

Hi ranman256,

Closer to the first way, I believe, but if I'm reading that right, I don't think that's quite there either.

Essentially, if any of the records in the source table are newer than the latest DateStamp (from any record) in the destination table, those rows (and ONLY those rows) should be appended.

So if this were my source table

BillID,Action,ActionDate,Notes,DateStamp
1234,Approved,07-23-2015,,08-6-2015
5678,Approved,07-23-2015,,08-4-2015
1278,Approved,07-23-2015,,08-2-2015

And the max DateStamp on the destination table was, say, 08-3-2015, then the query would append the first two records, but not the third. Hope that's making sense...

[ranman256]

Make a query, Q1, that pulls the key field and the MAX(Datestamp)
Make another query, Q2, that has Q1, and the main data table.
Join the tables on the key fld.
bring down the Q1.MaxOfDate field to the grid. Under its criteria put < mainData.[DateStamp]

append this to your table. It means find all records with MaxDates < mainData date , so append it.

[Sam_NY]

ranman256, it's been forever since you offered this advice and thanks for doing so. I couldn't get it to work unfortunately, but came to something similar and wanted to come back around to it and hopefully get your thoughts (or anyone else's)...

Here's what I ended up doing:
1) Created a totals query (Q1) and added the DateStamp field in my destination table; this was the ONLY field I added to this query because when I tried adding the key field, it kept returning the max of each ID (which was not needed); I set it to return 1 record; I specified "max" in the totals field.
2) Created another query (Q2) where I added both the source table (which happens to be a linked Excel table) and Q1; I did not join them (not sure I need to, but could be wrong); I added all the fields from the source table that I want copied into the source table. For the source table DateStamp field, I specified the criteria as >[Q1].[MaxOfDateStamp]
3) Created a third append query (Q3) where I added Q2 and specified the destination table as the "append to" table. I added all the fields I wanted to append and mapped them to the correct "append to" fields.

Can you see any problems with the basic method here?

Proof is in the pudding, I suppose, and it *almost* works. When I try to append I get a key violation error that's coming from an ID field. That field is NOT the primary key for the table and allows duplicates. It's a number type, as is the field in the table I'm appending from, so I'm not sure what the issue is.

I expect I'll figure that out (any suggestions welcome), but I was wondering if the general method I used is close to what you were suggesting in the first place (which I didn't fully grasp) and if not, is it still okay...

Thanks again!

[Sam_NY]

Oh, and meant to say that I specified "max" in the total field in step 1