hello i'm ok taxes 2013 shoot or ooze
this is going to be the first in a series a tutorial showing you how to
create
an invoice system the invoices from looking to create a
will involve I am taking
payments to have been made toward booking in taking
charges turtle evidence booking so for example the charges
and room favorite I'm any extra charges
after them together and creating invoices were running some
and are I'm going
now quickly show you when I mean if you go in
taxes the very first am database
reversed what he called a download
that's it ice ago okay yes you go get a first down motion the download page
should get something like this
case if we go to
fortune on the invoices on the reports will change
cartoon voices become
mister hudson click on print at this is we should get
I am so Devin Voice number for the day the
working on this number cast and every level details
regard invoice and we would have for example
a charge through the rooms Mitch equals a house in four including VAT
miss the pasa makes a balance minus a 40 then you have charged a second room 720
which makes that
-56 the then there's PA
payment thousand so that that's the balance down to minus five sixes
ourselves using running some here
I'm sheet to go to the first download might be slightly different style to
some the figures but the concept exactly the same which is
if it's a charge it a.m.
decreases the balance well make small negative I should say
and if it's a payment the puts a balance closer to zero in
obviously only one institutions are a balance music and charger everything
they've paid everything
so let's give it a nap together that database for the in the bin
okay right now we've got axed 2013 TV 45 which is
where we're going from now Silas ok for now on up
and you go to use the downloads section website
by following the link below the video next find that one password is
very original password hokey dokey
okay a.m. right where to begin we need to begin
by creating what's called a union query
TerraNet credit union query because what we're trying to do
and initially he's
we're trying to take movie
charges and put it in one query
and his two main different places we keep the charges at the moment
one is essentially on the booking details
because you've got checkin day and a check out today
got a room so what we would do is you would
take the charge per night
for the rooms multiplier by the dates for the check in date the check out
today
and I give us the charge levying for that particular
room not particular time period another place we keep
charges is in
table is charges and these extra charges
of things like minibar TV package excellent
such as a truck so we're actually keeping the charges in two different
places
and so good days are gonna take then we're gonna put them together in a
using query so how do we do that well let's close this
an effect as close mine as well
I'm gonna create queries a festival just create
a query that you'd wanna see so we look at the
a charge for the rooms festivals or tables we gonna need to that so again
the table booking details
take bookings and table rooms
at Damen okay
let's just make a stand aquarium I'm not doing anything fancy so let's go to
booking ID
and booking day should run their came back to design view
and then when it is we're gonna create a column called item
and item is gonna list
the I think the room and the
the date difference
so we're gonna abuse in concatenation so click into the top row
of the empty column click on fielder
and fortunate for a few she's face of rising
which is knowing gonna say a.m.
item and then semicolon
in open/close quotation marks so actually building a string
on me so room I'm lovin the
officials to Teixeira marks for the semicolon space
no se and a
table runes stock
room number so I to
so room and tyrants the room number
in the the and then and
as a few quotation marks space dash
space closed for tation Mar and
know if really do you can see here things table
booking details doc checkin day
and
same as before table booking details
top check-out date so it is actual building a string
to the should say something like room 2001
dash and and the check in date test check out a citrus okay let's just
scared distribution runner
room 201 52 my to the nineties with my
from case now we just need to
at a column to create the cost to the booking and cost a booking is going to
be the
number nights times
the cost per night the room
so clicking here say costs
George could cost actually she's gonna charge
switch charge
and will say date dish so many state dysfunction and take the function
is function we parse into dates
sources first the numbers in the summer season 2
so actually start that date function again
K so you parse into a person into balloon to date since phone is
days gonna find a difference in days
1001 table booking details talk
checking the AC to the early days first continue to
the and late today second
now give you the man days so I'll show you that for now
there's about days and we can multiply that
by the
tapeworms top cost tonight
report no
actually km weekend yep to get the charge now super can I D booking day
item and charge so this is the first
query makin and now we're going to have to these are gonna make second query
and then organizacion joined together the key thing when you make in the
second period at the second clear he's going to revolve around these extra
charges here
is that you get basically the same amount feels
the same day to talk so we've got a number
day text
cart see so when I made the single area I'm gonna make
number take tax currency and in a belt for them together
to say crate for design so now we'll go table X charges
at in anything on that
actually charged a item
amount you
Singh booking details I tsk
yeah disco table booking details
and table rooms
okay so we need booking ID to just take that from table booking details next
Wednesday
to use extra charge de the next thing we need is to
column called because a match is called the same is called item
and will say do something actually was a
room
an image from or program number
number so if they weren't certain number and ices room
2007 I mean it's a woody is so
missus item here and
Ste face first stash
and am
table extra charges 20 item
and
space and
a.m. something for one for the amount of
nothing as are actually keeps
leader that to press OK his first wrong
we get iTunes to dinner
and interest the and we just need to charge which
is I'm so we got we got to queries now
okay
so sorry for them together very simple take the
first query and just into
SQL View and then take a second period rock band SQL query
skill view even highlight both
a lines in this case control CEC copied it
am in the first query
delete the semicolon at the end
press Return and go to Union ship should be
capital letters Union all
pressure turn in pace in the second query
numbers run we've actually for the two queries together
and so we've got the charges down here
and we've got the but he's up here the one thing we do wanna do you organize it
but booking day
now when you I'm ordering you have to use a name for the shields
up top you can use a name in the fields down below
so I will say a.m. border
by to
table bookings door King
take and runner
syntex missing operator buddy something silly
sorry space the
a great so now its stake in everything and it or did it by putting day
key point even know you the second query
each table X charges to exit charged a you still user names refer to
in first select query which is table bookings the booking date it does it for
everything
okay okay so now I just name it
saving cooler cleary looking
X costs group booking
costs extra charges
Union Pacific and the second clear you can just
get rid alright so
that's a union query made a complicated
I am Becky place remember you inquiries
you craig to standard select queries
make sure that the shield staff to be named the same make sure the head they
use the same datatype serves a number
for the first column in the first query attorneys to be
a number type for the first column second query menu
day night this case texting currencies eventual solution
found still types here is make sure that the same
and then when you put in Scopus remember little things like
to remove T a.m. semicolon
from the and here I
you you know when the at the second things like order by
I'm you need to put right at the end and any names you referring to
and you were to buy clothes need to be from the first select query
here otherwise it won't work okay so thank you for watching
and OC next video will be doing that all over again i think is a go second union
crease make
so CNN