Calculating in a Query

[ckirmser]

Is it possible to use the result from a calculation in a query as data for a field in the table the query is based on?

Basically, I have an event that has a start date and an end date. The table stores individual events and the query only shows those events that have a start date but nio end date.

Also, each event can restart later with a new start date and end date.

I want to use the dates to calculate the number of days duration for the event's instance and store that in the table for later use - each event is allowed only a certain number of days.

Then, I'll need to add up the number of days for each instance of an event, to see if it has reached the maximum allowed.

So, like this;

Table Fields:
eventID, eventtype, startdate, enddate, numdays

As soon as I enter an end date for an event, I want it to calculate the number of days since the start and put that in the numdays field.

Then, I'll need to add up all the numdays for each eventtype.

The part I'm really having trouble with, though, is just the part to do a calculation and put the answer in the table that the query is based on.

In the event builder I tried [numdays] = [enddate] - [startdate], but it always drops the numdays part, leaving me with = [enddate] - [startdate].

Any ideas?

 

[The_Doc_Man]

Number one problem, you are trying to store computed data. If you search this forum (it has a good SEARCH function), you will note many posts on the topic of "Store Computed Value" - which is generally a no-no of the highest order.

FIRST, don't store what you can easily recompute. CPU cycles are cheaper than the time added to your disk I/O because your records got bigger.

SECOND, if either of the dates in question is "today" then your values are dynamic and you don't want to store them. (Because tomorrow they will be wrong...). Not to mention a technical violation of normalization, but from the looks of it you aren't headed that way... yet.

My advice is to Google-search "Database Normalization" - and look up Access Help on Normalization, too. For the web search, only look for articles from reasonable or recognizable colleges and universities in the .EDU domain.

Once you do this, reconsider your design to store things that are ephemeral. Queries are where you do this. Not only is that good, but if you base a form off of a query, the form is, in general, too stupid to care. Just base a locked control on the computed field so that you can't try to update it. (You could not anyway; the locked control just prevents you from getting errors.)

If you want to do a summation of the days, there is no problem in running a layered query that includes the SUM of the event-days computed for the first query.

Think dynamic, not static. Static makes you an easier target for gremlins. Also, static means "going nowhere" - which you would be if you stored dynamic data as though it were static.

 

[ckirmser]

Thanx, doc man. I figured a search would have been good, but I didn't have a clue on what terms to use. I'm pretty much self-taught Access and don't have the jargon down properly.

Now, I have the jargon and I can refine my searches a lot better.

I thought of using the queries and recalculating each time, but Access kept knocking me on the beaner. I'm probably doing it wrong, but now I can probably find my answers more easily.

Thanx again!