Update Query Help

[Shell]

In Access 2000, I have a table that contains several dates. I can easily
write an update query that subtracts 2 dates and places the results an
another field.

However, When the process is run, the data can represent different
companies. I know which company we are using at execution time. But each
company uses different dates for the calculation. I don't want to write a
select case statement based on company to get the specific query to use. I'd
rather use a configuration table which contains the companyID and which dates
to use for the calculation.

I am having a lot of trouble in writing one update query which uses the
referenced dates.

Any help would be appreciated.

Thanks

 

[Dale Fye]

There are two trains of thought on this. The first is that relational
database theology would strongly advise against storing any field in the
database that can be determined by running a query. In this case, you want
to take two pieces of data, and compute and store a third, probably not
necessary. There are times, however, when violating this rule is acceptable.
Assuming that you want to continue, //

I'm not sure that I understand what these dates are that you want to
subtract from one another. I can understand that one company might want
reports more frequently than another, or something like that, but a better
understanding of your data would probably help us write the query for you.

 

[Shell]

Relational database theory aside, the date difference represents the time it
takes for a specific action to occur. Each comany has different rules about
this action. I need to calculate this action based on the company.

 

[Dale Fye]

My problem is that I don't know what kind of date you are going to store in
your companies table, that would apply to all of the records for that company
in the other table.

Let me give you an example. I have a Tools table that is part of a tool
calibration database, this tools table contains fields ToolID, ToolTypeID,
and LastCalibrated (among others). I also have a ToolType table which
contains ToolTypeID, and a MonthFreq table, which tells me how many months
between calibrations.

To determine when the next calibration is due, I can write a query that
looks like:

SELECT T.ToolID, T.LastCalibration, DateAdd("m", TT.MonthFreq,
T.LastCalibration) as NextCalibration
FROM Tools as T
INNER JOIN ToolType as TT
ON T.ToolTypeID = TT.ToolTypeID

If my Tools Table contained a NextCalibration field, I could update that with:

UPDATE Tools as T
INNER JOIN ToolType as TT
ON T.ToolTypeID = TT.ToolTypeID
SET T.NextCalibration = DateAdd("m", TT.MonthFreq, T.LastCalibration)
WHERE T.LastCalibration IS NOT NULL

Don't think I can help much more than that without more details about your
table structure.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Shell]

My data table has fields that specify the companyID, several different date
fields all in MM/DD/YYYY format. The dates can be ImportDate,
ReactivationDate, LastCalledDate, NextCallDate, DeactivationDate, etc. Some
companies need the difference between ImportDate and LastCalledDate. Other
companies need the difference between ImportDate and ReactivationDate. The
combinations go on and on.

I know that storing the results of the computation in the table is wasteful,
further calculations are performed later on.

 

[Dale Fye]

That helps.

Sorry, I'm not aware of any easy to do what you are referencing.

You might want to rewrite your post, including the details you provided in
your last post, and repost it with a subject like "Rule based query" or
something like that.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.