Update coulmn in Query Based on Max Report Date

[janmack79]

Hello I have data from a report that I append to a table in Access every week and in the last column I enter the date that the data was appended to the table.



My question is that I then want to create a new table with all the data from the original table and add a new column that says if the date is the Max Date in the table on the report then show "Current", if it is from a week ago from the Max date then put "Last Week", if it is from two weeks ago then put "Two Weeks Ago", and greater than two weeks just put "No"

I am fine with breaking this up into a few queries I just can't seem to figure out how to calculate the , Last Week, Two Weeks, and greater part of the Query .

[Micron]

a) you should not be repeating the data in a new table if you're keeping the old table of data as well
b) the status you're wanting to use as a flag is not constant. What is current today will not be a month from now according to your post, so storing this value makes little sense.
c) you should not be storing calculated fields as a rule (see b)

What you should be doing is 'flagging' the status using either
1) a query with calculated fields for each condition/status, maybe using something IIF and DatePart functions like (this is off the top of my head)

IIf(DatePart("w","myTable.myDate")< DatePart("w",Date()-1),"Last Week","")

OR
2) you should be using vba on a form which has a query or your table as its record source, and setting a text box value to be the appropriate message or probably more appropriate, a label caption. The code would make the comparisons between the current date (Date()) and the report date in the query or table. That way, as the current date changes, the form reflects the proper message.

[janmack79]

Hi Micron, I would not want the data in the new column to remain constant my goal is that I would run an update query every week to update the fields when I append new data in the original table every week

[Micron]

I would run an update query every week to update the fields when I append new data in the original table every week

You can do that, but if it's that volatile, I'd bet the majority of seasoned posters here would tell you it's not optimal practice. Such updates have been known to fail and present no indication of such. That is why queries and reports are used to calculate such things and present the correct calculation when needed.

You definitely are storing a calculated value,

I would not want the data in the new column to remain constant

but yours is not a case such as needing to store past pricing so that old invoice amounts are not affected by price increases. So in your case, it is not accepted as the proper approach.