Criteria for Query Question

[salbd]

I'm trying to create an update query that will change the status of residents who live in a building to "Yes" when they have lived there for at least 30 days. What would this criteria look like in datasheet view in Access? Fields would be Move In Date and Approved from a Rooms table.

 

[arnelgp]

something like (if Status is a Yes/No field)

Update [ResidentTable] Set [Status] = -1 Where [DateFieldStartOfOccupancy] <= Date() - 30

if Status is a Short Text:

Update [ResidentTable] Set [Status] = 'Yes' Where [DateFieldStartOfOccupancy] <= Date() - 30

 

[XPS35]

It is a bad idea to store that in your table. You need to run the query every day.
Make a query with a calculated field and you can always show the right value at the current date

 

[arnelgp]

yes, like this:

Select *, IIF([DateFieldStartOfOccupancy] <= (Date() - 30), "Yes", "No") As NewStatus From [ResidentTable];

Or

Select *, Choose(Abs(([DateFieldStartOfOccupancy] <= Date() - 30)-1), "No", "Yes") As NewStatus From [ResidentTable];

 

[Isaac]

Usually avoid storing calculated values in a table column. In larger scale enterprises, storing calculated values is not all that uncommon, but it's usually done to INCREASE efficiency and speed for dashboards or datamarts that need to be run and run fast during the day, so it may be useful to preaggregate the data (or even necessary, in many cases in my current job, where people really don't want to wait for queries to process billions of records every time they hit Update Dashboard!)

use an Update query?

Hey everyone. I think this is the right forum for this question. I have a form that tracks Production Downtime. The table contains the fields: [Total Minutes], [Time Down], and [Time Restart]. A form uses the function: =Sum(DateDiff("n",[Time Restart],[Time Down])*-1) to show the difference...

www.access-programmers.co.uk

Issues with a Query

I am struggling to get this query to work. I can get the answers I need if I dump everything into excel but I am trying to get it done via Access so other users can get what they need with one click. I've attached a Test db with samples of the two tables being used. tbl_CustData contains all...

www.access-programmers.co.uk

That's my best attempt at linking to some mentions about the balance of the issue

 

[Pat Hartman]

I'm trying to create an update query that will change the status of residents who live in a building to "Yes" when they have lived there for at least 30 days.

The problem with this request is that if you update the data today, then tomorrow some data will be wrong so unless you calculate the "yes" on the fly, you can never be sure your data is accurate.

EXACTLY what are you using this value for and why does it need to be stored? Why won't calculating it on a form or report on the fly work?