Common Controls in Access: Calculated Control Video Lecture | SSC CGL Tier 2 - Study Material, Online Tests, Previous Year

hey this is Ralph and in this video I
want to go over creating some calculated
controls in a new web form and in an
Access database so let's go and get
started here now I have an Access
database that I've used in a couple of
recent videos and this database keeps
track of a number of clients there's
about 80 or so and in fact I'll open one
up here there's a 83 clients listed
there's quite a few employees under
contract there's 300 employees and
there's a billing rate and a pay rate
for each employee and I have a jobs log
table my jobs log table has a little
over eleven hundred records and this
keeps track of the date of the job the
employee involved the client involved
the tasks involved the start time and
the end time for that particular job now
I'm going to create a really quick form
when we go and close these tables and
fastest way to create a form especially
if that form is just out of one table is
to click once select the table in
question in this case I'm going to
select my jobs log table jobs log one
precisely I'll go to the create ribbon
and I'll just click the form button and
that's going to create a form instantly
out of the table that happen to be
active in my navigation pane now I have
my property sheet open I don't need that
at the moment so I'm gonna go ahead and
close that so this is my generic form
that was created very basic just in a
default style now if I look at this in
form view right now I'm in layout view
by the way if I click on my lower
portion of my view button
I'll see that I'm in layout view I'm
going to jump over to form view it's
gonna look pretty similar and this is
basically how your employees would you
or your employees would interact with
this particular form and I can see that
I can navigate through records you know
there's 1100 of them and I can see the
job date which employee was involved
which client was involved by their IDs
though which tasks and so forth tasks
happens to be a drop-down where I can
change that to do dropdowns for employee
ID and client ID would be a pretty
useful thing but I think
tackle that another video there's a
couple ways to go about it job start
time and job end time what I would like
to have happen here is that I would like
to see the number of hours calculated
for the job start time in the job end
time furthermore I would also like to
consider incorporating maybe the bill
rate to once I know the number of hours
for a particular job and since I already
know my employee in question I should be
able to look at that employee's billing
rate multiply it by the hours to put in
a bill amount right here inside of the
form now these are things that I'd also
probably do in a query and then
ultimately in a report to produce an
invoice but I'd like to also have it
right here in the form so that way if I
enter in a new job I can enter in the
start time and the end time and the
hours will automatically display for me
and then the billing amount will
automatically display for me that'll
make it easier for a customer service
rep to kind of start to estimate a price
you know well if we enter in a job of so
many hours at this employees pay rate
this is what it will be so I've got this
one generic form created I'm going to go
ahead and jump over to design view of
this form and I'm going to go ahead and
just do a little bit of cleanup here I'm
just going to click holding down my
shift key I'll click on these other form
fields and I'm going to make them a
little bit more narrow so that I've got
some space to work and really since my
job start time and job in timer right
down here at the bottom I'll go ahead
and put in the hours worked off over
here to the side so if I jump back to
form view you'll see that my form fields
like narrow I'll end up putting my job
hours work hours over here and then
later on we can put in estimated billing
amount or billing amount right down here
below that it shouldn't be too tough
force so back over to design view so
what I want to do is I want to create a
control here and controls are all the
kinds of objects or things you can put
into a form or a report for that matter
by the way design view of a form is
ethically identical to design a view of
a report so if you practice here in
forms you'll find reports much easier to
work with so I've got a certainly my
control panel up here and the one I want
to start with first is going to be my
text box and when I choose the text box
control and I click and drag a box and
I'm just kind of dragging it you have to
be too precise roughly where I want it
down here it's automatically going to
create a label which is happens to be
another control that's not exactly where
I want it so I'm going to kind of move
it off to the right now notice when I
click and drag my control my label goes
with it and that's if I click and drag
it on one of the borders there and then
I'll go ahead and clean this up a little
bit for my label I'll go ahead and
change that to ours now for my unbound
control this is where I'm going to type
in a formula that's going to take the
start time and the end time and figure
out the hours and there's already a
function in access that it's going to do
all that hard work force the date diff
function so I'm just going to go ahead
and click in here and in fact let me
take care of this real quick to make it
easier for you guys to see I'm going to
make this box really big in the short
term just so you can see exactly what
I'm typing because it's kind of a large
function so I'm going to write out an
equal sign date diff parenthesis I'm
gonna use the date diff function the
date diff function has several
parameters the first thing is the let's
see the
is the unit that you want so in is four
minutes and I want this in minutes comma
and now I'm going to put in square
brackets you know I always use brackets
when you refer to a field name I'm going
to take my job start time comma job in
time closing parenthesis so the date
diff function I want minutes and that's
what the n stands for in quotes and then
the starting date which happens to be a
time in this example and the ending date
which happens to be a time in this
example and this is going to tell me the
number of minutes in between these two
times and if I take the number of
minutes and I divide it by 60 I'll get
the number of hours so if I go to form
view right now I can see that for this
particular job that I'm active on right
now it started it at 9:20 finished at
4:50 that was seven and a half hours so
now I just want to do a little bit of
cleanup I'll jump back over to design
view and now I can make this box a
little bit smaller take my label I'll
move it a little bit closer actually I
want to move my control without moving
both so I'll just use the move handle
top left corner there we go just kind of
size them similar and I'll take my hours
my label I'm going to jump over to
format and I'm going to make that right
aligned so now when I go back to form
view there we go so it's a nice cleaner
look now this is going to calculate
automatically too so if I were to start
to change times instead of maybe I
messed up on entering this one job and
it didn't finish at 450 it finished at
250 I can change the four to a two and
as soon as I tab away to go to some
other cell or click somewhere else my
hours will get recalculated it's now 5.5
hours so that's kind of a nice thing and
that is a calculated control inside of a
form
this particular calculated control is
using the date diff function so that
wasn't too bad let's go ahead and add to
this form beneath my hours my calculated
hours now I'd also like to put in a
billing amount my billing amount is
going to utilize the employees billing
rate multiplied by the hours so pretty
simple multiplication formula but to
really get to get this to work I want to
include the fields in this form that are
going to be part of the calculation so
I'd like to display the employees
billing rate right next to their right
next to their ID and I know I don't have
their name in there but I'll like to
display it right up in here so let's go
ahead and take care of that I need to
add a new field of this form so I'm in
design view of my form and I'm on the
design ribbon for that form I'm going to
click on add existing fields in the
field that I want the employees billing
rate is not in this current table so I
need to show all tables in the table
that I want is going to be in my
employees table in the field that I want
is the employee bill rate so I'm going
to take that field and just drag it up
to a blank area of my form then I'll
close this field list I'll just kind of
move this around a little bit and I kind
of want it to be close to I guess I can
line this use my grid to line things up
there we go that's pretty easy and just
that little change there if I now go to
form view
I'll see that for employee a one two
nine five the employee bill rate is
twenty three ninety four the particular
job that I'm active on right now it's at
eight point eight three hours if I
advance to another record you can see
that each employee it looks like if
you'll notice this I've got several jobs
in a row from the same employee a four
one six two so that's why my employee
bill rate doesn't change until the
employee changes so now it's simply
taking one formula or I'm sorry one
field multiplied by this formula down
here so I'm going to go ahead and jump
back over to design view and I'm going
to create a new control down here just
beneath my hours and scroll this just a
little bit kind of see both and I will
make a new control once again I'm going
to use that textbox control and I'm
going to start off I'll just draw a
really big rectangle right over here
it's kind of big and for the label I'm
going to go ahead and put in bill rate
or not bill rate that's not a good label
for job filling amount
move that off a little bit to the left
now I'm going to take the existing hours
calculation so I remember I've got this
large formula in here to calculate the
number of hours I'm just going to copy
that control C to copy and I'm going to
paste it right up here so I've got this
large calculation here the date
difference is going to give me the
number of hours for this job and I'm
going to take this response and I want
to multiply it by my employee bill rate
now I'm going to enclose opening
parenthesis and closing parenthesis so
I'm going to enclose this formula in a
set of parenthesis so then I can simply
multiply by M bill rate so make sure you
can see that nice and clear
there we go so I've got this date gift
function which I calculated or put in
just a second ago
we know that gives me the number of
hours for the job now I'm multiplying by
employee bill rate and if I jump over to
form view we'll see this actually
working we need to do a little bit of
cleanup but just to make sure things are
working properly pop open a little
calculator here let's just do the math
23.9 is the employees billing rate
multiplied by eight point eight three
hours equals two hundred and eleven
dollars and that's what we're getting
down here I'd like to format this as
currency and while I'm formatting I
might as well go ahead and make sure the
hours only display one or two decimal
places so I'm going to jump back over to
design view and I can get to my property
sheet in a couple ways I can just turn
on the property sheet or and right-click
on the control and it's below my
recording here but there's an option for
properties on this context menu and that
displays and I will format this one as
currency now for my date diff field I'm
going to format that as fixed and I will
show one decimal place so now when I
rerun this I only see one decimal place
for the hours and my job billing amount
is currency and then of course you can
take a few minutes to kind of make this
look a little neater more lined up and
just so that we can see that it is
working I'll just jump over to a
different job here's one here and
instead of 10:30 a.m. to 3:30 p.m. I'll
change the time to 1:27 p.m.
that of course changes the hours in this
case to 3 hours and employees billing
rate is 4180 job billing amount 120
$3.31 so have fun working with this