How to get four fields from a table into one field to another tabl

[Guest]

I'm working on a calendar that is based on a single DATE field.

In my database there is a table with two DATE field:
ContractExpired & OptionRenewal

In a query I also created calculation field to remind me of the
expired/Option date (about two months notice).
START: ([ContractExpired])-96
PROCESS: ([OptionRenewal])-58

So, this gave me 4 four fields that I must convert to one field in a new
table.

I created an UNION QUERY for the two field in my Table as follows:

SELECT Agency.AgentNum, Agency.StoreLocation, Agency.ContractExpired,
IIf([Address1]="Manufacturers",[ContractExpired]-28,[ContractExpired]-99) AS
Start
FROM Agency
WHERE (((Agency.ContractExpired) Is Not Null))


UNION ALL SELECT Agency.AgentNum, Agency.StoreLocation,
Agency.OptionContract, [OptionContract]-58 AS Process
FROM Agency
WHERE (((Agency.OptionContract) Is Not Null));

Please held for the other (QUERY) calculation fields.

Thanks in advance

 

[Jeff Boyce]

Serge

It sounds like you are trying to place a calculated value in a new field in
a table. It is rarely necessary to do so, and there are good reasons not
to.

If you need the calculated date(s), your queries provide a great way to do
that (and they are "current" -- a calculated value placed in a field will be
static).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Joseph Meehan]

Serge wrote:


Normally this is a bad idea. The only time it would normally be a good
idea is to store a result that you want to keep for historical information
and not allow it to change if any of the data going into it changes.
Otherwise you recompute the data anytime you use it in a form, report or
query.

 

[Guest]

Thanks Jeff for your comments:

However, my calendar, as for showing DATES is based on a Table with one DATE
field only. So, if I use the Table created by UNION QUERY and create a new
query with two calculation fields - I cannot link it to the Calendar, because
it is build on one DATE field only.

The Calendar I'm using is made from:

*******************************************
'Author: Michael Blake
'Contact Via: www.weAscend.com
'Date: October 12, 2000, 04:09:01 PM
'Copyright ©2000 Michael Blake
******************************************
It does work good, in showing my two field in my Database, but I must find a
way to make the calendar show "reminders - date" which are calculated values
from a query.

Hope this is a little clearer.
--
Serge

Serge

It sounds like you are trying to place a calculated value in a new field in
a table. It is rarely necessary to do so, and there are good reasons not
to.

If you need the calculated date(s), your queries provide a great way to do
that (and they are "current" -- a calculated value placed in a field will be
static).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

I'm working on a calendar that is based on a single DATE field.

In my database there is a table with two DATE field:
ContractExpired & OptionRenewal

In a query I also created calculation field to remind me of the
expired/Option date (about two months notice).
START: ([ContractExpired])-96
PROCESS: ([OptionRenewal])-58

So, this gave me 4 four fields that I must convert to one field in a new
table.

I created an UNION QUERY for the two field in my Table as follows:

SELECT Agency.AgentNum, Agency.StoreLocation, Agency.ContractExpired,
IIf([Address1]="Manufacturers",[ContractExpired]-28,[ContractExpired]-99) AS
Start
FROM Agency
WHERE (((Agency.ContractExpired) Is Not Null))


UNION ALL SELECT Agency.AgentNum, Agency.StoreLocation,
Agency.OptionContract, [OptionContract]-58 AS Process
FROM Agency
WHERE (((Agency.OptionContract) Is Not Null));

Please held for the other (QUERY) calculation fields.

Thanks in advance