automatically generating field text based on other fields

[Kyle]

OK, I would love some thoughts and suggestions on something I'm hoping
to attempt with a database I'm working with.

I'm revising a database using Access 2003 - the database was created
using Access 97, and updated most recently on Access 2000.

What I want to do is create something that will sample a number of
fields in the parent/primary table and one child/secondary table, and
insert those fields at certain points in a text string so that I get
something like the following for each unit in my database (where the
existing fields are in []s):

ALERT! Freezer [ID#] in room [Room#] is in alarm. During working hours,
contact the following people:
[Employee] [OfficePhone] [LabPhone]
[Employee] [OfficePhone] [LabPhone]
After working hours, contact the following people:
[Employee] [HomePhone] [MobilePhone] [Pager]
[Employee] [HomePhone] [MobilePhone] [Pager]

then put that text string into a completely separate field (call it
CAlertText)in a third table which will be exported to Excel for
insertion into a security system's database.

So what I'm wondering is:
(1) Is it possible to have this CAlertText field automatically
generated? (Please tell me I can do this without knowing VB or SQL!)
(1a) Here's a tricky one: can I make this conditional upon what is in
another field entirely? For example, have it generate one text string
if the value in field [AlarmType] is "Local Alarm" and the text string
above if the value of the field is "C-CURE"?
(2) What are the pros and cons of doing this all at one time for the
entire database, vs. doing it as I update a record?
(3) Is there a potential for accidentally bringing code over from
Access to Excel which could then be read into the security system when
importation from the Excel file is done?

(I am very afraid: this sounds like a lot to ask of Access...)

 

[Phillip Windell]

You don't want to "duplicate" data. A piece of data should only appear once
in the entire database. You retrieve the data in whatever form you want to
retrieve it regaurdless of where it was located, but it should never appear
2, 3, or 4 other places in the database. If that becomes a problem then you
need to re-examine your database design,...the tables are probably
improperly normalized.

 

[Phillip Windell]

(1) Is it possible to have this CAlertText field automatically
generated? (Please tell me I can do this without knowing VB or SQL!)
(1a) Here's a tricky one: can I make this conditional upon what is in
another field entirely? For example, have it generate one text string
if the value in field [AlarmType] is "Local Alarm" and the text string
above if the value of the field is "C-CURE"?

You need to know VB and SQL (both). There just is no substitute for knowing
the code. One sememster of VB can go a log way. That is all I've ever had
and I can usually figure out what I want to do, if I research it,...I won't
just know it off the top of my head.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.asp
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.asp

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp

Deployment Guidelines for ISA Server 2004 Enterprise Edition
http://www.microsoft.com/technet/prodtechnol/isa/2004/deploy/dgisaserver.mspx
-----------------------------------------------------