Can I use a query or macro to populate a field?

  • Thread starter Thread starter NSC_24
  • Start date Start date
N

NSC_24

I would like for a field (Total Visits) to be populated from the addition of
Hospital_1 + Hospital_2 + Hospital_3.

Would I be able to use a macro or query so that this occurs automatically?

Thanks in Advance!
 
NSC_24 said:
I would like for a field (Total Visits) to be populated
from the addition of Hospital_1 + Hospital_2 + Hospital_3.

If those three fields are all in the same record, you would not want to
populate yet another field with the total -- you would want to use an
expression in a Query to calculate the total when you retrieve the
information for display to the user. It is not good practice to give your
users access to datasheet view or tables or queries, because that makes it
so easy for human error to corrupt your data.

Also, it will likely make your life easier if you would create a related
table with information about hospital visits rather than having a count for
specific hospitals in the same record. As you have it designed, what do you
do when you add a fourth hospital to your resources? Looks like you'd have
to re-design. With a related table you'd just add another data value option
to the "hospital id" field, and record the visit.
Would I be able to use a macro or query so that this occurs
automatically?

With macros and/or VBA code, _almost_ anything is possible. But, because I
suspect you need to redesign, I would not want to help you move farther away
from that good design.

Larry Linson
Microsoft Office Access MVP
 
If your Access table has 'repeating fields' (e.g., [Hospital_1],
[Hospital_2], ...), you have ... a spreadsheet! A well-normalized
relational database table does NOT add "one more field" to get one more
fact. Instead, if you have a one-to-many relationship (and you must, if you
have more than one "Hosptial_xxx"), you use a minimum of TWO tables, the
first (a "parent") to hold the common information, and the second (a
"child") to hold the, in your case, "hospital" info (I'm guessing these
fields hold # of visits, which sounds like a derived/calculated value
anyway!

I'd recommend you brush up on relational database design and normalization
before spending your time/energy coming up with a work-around for the fact
that the Access table is not set up in a way that Access can readily use.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP
 
P.S. the short answer is "yes" ... but not a good idea.

Regards

Jeff Boyce
Microsoft Access MVP
 
I would like for a field (Total Visits) to be populated from the addition of
Hospital_1 + Hospital_2 + Hospital_3.

Would I be able to use a macro or query so that this occurs automatically?

Thanks in Advance!

STOP.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it as a
calculated field in a Query.

Worse, you apparently are "committing spreadsheet", with three Hospital fields
in the same table. What will you do when you need a *fourth* hospital?
Redesign your table, rewrite all your queries, restructure all your forms...!?
Ouch!

I'd really suggest that you step back and properly normalize your tables
first. See:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Thanks Larry and Jeff. I haven't actually created the database but my
supervisor asked if this was possible and I wasn't sure. I will take your
advice about relationships.
 
Back
Top