1
00:00:01,360 --> 00:00:03,730
hello i'm ok taxes 2013 shoot or ooze

2
00:00:03,730 --> 00:00:07,910
this is going to be the first in a series a tutorial showing you how to

3
00:00:07,910 --> 00:00:08,559
create

4
00:00:08,558 --> 00:00:13,160
an invoice system the invoices from looking to create a

5
00:00:13,160 --> 00:00:16,660
will involve I am taking

6
00:00:16,660 --> 00:00:20,170
payments to have been made toward booking in taking

7
00:00:20,170 --> 00:00:24,080
charges turtle evidence booking so for example the charges

8
00:00:24,079 --> 00:00:28,059
and room favorite I'm any extra charges

9
00:00:28,059 --> 00:00:33,189
after them together and creating invoices were running some

10
00:00:33,189 --> 00:00:36,759
and are I'm going

11
00:00:36,759 --> 00:00:40,539
now quickly show you when I mean if you go in

12
00:00:40,539 --> 00:00:44,079
taxes the very first am database

13
00:00:44,079 --> 00:00:47,770
reversed what he called a download

14
00:00:47,770 --> 00:00:52,940
that's it ice ago okay yes you go get a first down motion the download page

15
00:00:52,939 --> 00:00:58,280
should get something like this

16
00:00:58,280 --> 00:01:05,280
case if we go to

17
00:01:05,390 --> 00:01:09,478
fortune on the invoices on the reports will change

18
00:01:09,478 --> 00:01:13,129
cartoon voices become

19
00:01:13,129 --> 00:01:17,699
mister hudson click on print at this is we should get

20
00:01:17,700 --> 00:01:21,609
I am so Devin Voice number for the day the

21
00:01:21,609 --> 00:01:25,579
working on this number cast and every level details

22
00:01:25,579 --> 00:01:28,798
regard invoice and we would have for example

23
00:01:28,799 --> 00:01:33,939
a charge through the rooms Mitch equals a house in four including VAT

24
00:01:33,938 --> 00:01:40,309
miss the pasa makes a balance minus a 40 then you have charged a second room 720

25
00:01:40,310 --> 00:01:41,060
which makes that

26
00:01:41,060 --> 00:01:44,799
-56 the then there's PA

27
00:01:44,799 --> 00:01:48,368
payment thousand so that that's the balance down to minus five sixes

28
00:01:48,368 --> 00:01:50,290
ourselves using running some here

29
00:01:50,290 --> 00:01:55,649
I'm sheet to go to the first download might be slightly different style to

30
00:01:55,649 --> 00:01:58,609
some the figures but the concept exactly the same which is

31
00:01:58,609 --> 00:02:01,819
if it's a charge it a.m.

32
00:02:01,819 --> 00:02:06,639
decreases the balance well make small negative I should say

33
00:02:06,640 --> 00:02:10,250
and if it's a payment the puts a balance closer to zero in

34
00:02:10,250 --> 00:02:13,870
obviously only one institutions are a balance music and charger everything

35
00:02:13,870 --> 00:02:14,920
they've paid everything

36
00:02:14,919 --> 00:02:19,628
so let's give it a nap together that database for the in the bin

37
00:02:19,628 --> 00:02:23,669
okay right now we've got axed 2013 TV 45 which is

38
00:02:23,669 --> 00:02:27,250
where we're going from now Silas ok for now on up

39
00:02:27,250 --> 00:02:30,759
and you go to use the downloads section website

40
00:02:30,759 --> 00:02:35,719
by following the link below the video next find that one password is

41
00:02:35,719 --> 00:02:41,830
very original password hokey dokey

42
00:02:41,830 --> 00:02:46,110
okay a.m. right where to begin we need to begin

43
00:02:46,110 --> 00:02:49,700
by creating what's called a union query

44
00:02:49,699 --> 00:02:54,199
TerraNet credit union query because what we're trying to do

45
00:02:54,199 --> 00:02:57,539
and initially he's

46
00:02:57,539 --> 00:03:01,090
we're trying to take movie

47
00:03:01,090 --> 00:03:04,128
charges and put it in one query

48
00:03:04,128 --> 00:03:08,639
and his two main different places we keep the charges at the moment

49
00:03:08,639 --> 00:03:12,939
one is essentially on the booking details

50
00:03:12,939 --> 00:03:16,199
because you've got checkin day and a check out today

51
00:03:16,199 --> 00:03:19,429
got a room so what we would do is you would

52
00:03:19,430 --> 00:03:22,689
take the charge per night

53
00:03:22,689 --> 00:03:27,479
for the rooms multiplier by the dates for the check in date the check out

54
00:03:27,479 --> 00:03:27,969
today

55
00:03:27,969 --> 00:03:31,699
and I give us the charge levying for that particular

56
00:03:31,699 --> 00:03:36,469
room not particular time period another place we keep

57
00:03:36,469 --> 00:03:39,568
charges is in

58
00:03:39,568 --> 00:03:43,589
table is charges and these extra charges

59
00:03:43,590 --> 00:03:48,430
of things like minibar TV package excellent

60
00:03:48,430 --> 00:03:51,450
such as a truck so we're actually keeping the charges in two different

61
00:03:51,449 --> 00:03:52,298
places

62
00:03:52,299 --> 00:03:57,260
and so good days are gonna take then we're gonna put them together in a

63
00:03:57,259 --> 00:04:00,989
using query so how do we do that well let's close this

64
00:04:00,989 --> 00:04:05,000
an effect as close mine as well

65
00:04:05,000 --> 00:04:08,930
I'm gonna create queries a festival just create

66
00:04:08,930 --> 00:04:15,140
a query that you'd wanna see so we look at the

67
00:04:15,139 --> 00:04:18,750
a charge for the rooms festivals or tables we gonna need to that so again

68
00:04:18,750 --> 00:04:20,560
the table booking details

69
00:04:20,560 --> 00:04:23,750
take bookings and table rooms

70
00:04:23,750 --> 00:04:26,970
at Damen okay

71
00:04:26,970 --> 00:04:31,570
let's just make a stand aquarium I'm not doing anything fancy so let's go to

72
00:04:31,569 --> 00:04:32,529
booking ID

73
00:04:32,529 --> 00:04:37,519
and booking day should run their came back to design view

74
00:04:37,519 --> 00:04:41,198
and then when it is we're gonna create a column called item

75
00:04:41,199 --> 00:04:44,400
and item is gonna list

76
00:04:44,399 --> 00:04:47,659
the I think the room and the

77
00:04:47,660 --> 00:04:51,449
the date difference

78
00:04:51,449 --> 00:04:55,639
so we're gonna abuse in concatenation so click into the top row

79
00:04:55,639 --> 00:04:58,889
of the empty column click on fielder

80
00:04:58,889 --> 00:05:02,470
and fortunate for a few she's face of rising

81
00:05:02,470 --> 00:05:06,660
which is knowing gonna say a.m.

82
00:05:06,660 --> 00:05:12,100
item and then semicolon

83
00:05:12,100 --> 00:05:16,180
in open/close quotation marks so actually building a string

84
00:05:16,180 --> 00:05:19,478
on me so room I'm lovin the

85
00:05:19,478 --> 00:05:22,839
officials to Teixeira marks for the semicolon space

86
00:05:22,839 --> 00:05:26,448
no se and a

87
00:05:26,449 --> 00:05:29,509
table runes stock

88
00:05:29,509 --> 00:05:33,110
room number so I to

89
00:05:33,110 --> 00:05:36,699
so room and tyrants the room number

90
00:05:36,699 --> 00:05:41,780
in the the and then and

91
00:05:41,779 --> 00:05:44,869
as a few quotation marks space dash

92
00:05:44,870 --> 00:05:49,430
space closed for tation Mar and

93
00:05:49,430 --> 00:05:53,918
know if really do you can see here things table

94
00:05:53,918 --> 00:05:59,538
booking details doc checkin day

95
00:05:59,538 --> 00:06:02,550
and

96
00:06:02,550 --> 00:06:06,780
same as before table booking details

97
00:06:06,779 --> 00:06:11,659
top check-out date so it is actual building a string

98
00:06:11,660 --> 00:06:14,840
to the should say something like room 2001

99
00:06:14,839 --> 00:06:19,959
dash and and the check in date test check out a citrus okay let's just

100
00:06:19,959 --> 00:06:25,339
scared distribution runner

101
00:06:25,339 --> 00:06:31,369
room 201 52 my to the nineties with my

102
00:06:31,370 --> 00:06:35,939
from case now we just need to

103
00:06:35,939 --> 00:06:39,939
at a column to create the cost to the booking and cost a booking is going to

104
00:06:39,939 --> 00:06:40,620
be the

105
00:06:40,620 --> 00:06:44,250
number nights times

106
00:06:44,250 --> 00:06:47,649
the cost per night the room

107
00:06:47,649 --> 00:06:51,728
so clicking here say costs

108
00:06:51,728 --> 00:06:55,918
George could cost actually she's gonna charge

109
00:06:55,918 --> 00:06:59,279
switch charge

110
00:06:59,279 --> 00:07:04,469
and will say date dish so many state dysfunction and take the function

111
00:07:04,470 --> 00:07:10,139
is function we parse into dates

112
00:07:10,139 --> 00:07:14,470
sources first the numbers in the summer season 2

113
00:07:14,470 --> 00:07:18,660
so actually start that date function again

114
00:07:18,660 --> 00:07:22,669
K so you parse into a person into balloon to date since phone is

115
00:07:22,668 --> 00:07:25,870
days gonna find a difference in days

116
00:07:25,870 --> 00:07:29,530
1001 table booking details talk

117
00:07:29,529 --> 00:07:33,929
checking the AC to the early days first continue to

118
00:07:33,930 --> 00:07:38,379
the and late today second

119
00:07:38,379 --> 00:07:42,668
now give you the man days so I'll show you that for now

120
00:07:42,668 --> 00:07:46,568
there's about days and we can multiply that

121
00:07:46,569 --> 00:07:52,810
by the

122
00:07:52,810 --> 00:07:56,788
tapeworms top cost tonight

123
00:07:56,788 --> 00:08:02,719
report no

124
00:08:02,720 --> 00:08:08,110
actually km weekend yep to get the charge now super can I D booking day

125
00:08:08,110 --> 00:08:12,319
item and charge so this is the first

126
00:08:12,319 --> 00:08:17,569
query makin and now we're going to have to these are gonna make second query

127
00:08:17,569 --> 00:08:21,210
and then organizacion joined together the key thing when you make in the

128
00:08:21,209 --> 00:08:24,448
second period at the second clear he's going to revolve around these extra

129
00:08:24,449 --> 00:08:25,810
charges here

130
00:08:25,810 --> 00:08:29,060
is that you get basically the same amount feels

131
00:08:29,060 --> 00:08:33,250
the same day to talk so we've got a number

132
00:08:33,250 --> 00:08:36,349
day text

133
00:08:36,349 --> 00:08:39,729
cart see so when I made the single area I'm gonna make

134
00:08:39,729 --> 00:08:43,430
number take tax currency and in a belt for them together

135
00:08:43,429 --> 00:08:48,620
to say crate for design so now we'll go table X charges

136
00:08:48,620 --> 00:08:52,230
at in anything on that

137
00:08:52,230 --> 00:08:56,209
actually charged a item

138
00:08:56,208 --> 00:08:59,669
amount you

139
00:08:59,669 --> 00:09:04,049
Singh booking details I tsk

140
00:09:04,049 --> 00:09:09,088
yeah disco table booking details

141
00:09:09,089 --> 00:09:12,839
and table rooms

142
00:09:12,839 --> 00:09:18,029
okay so we need booking ID to just take that from table booking details next

143
00:09:18,028 --> 00:09:19,379
Wednesday

144
00:09:19,379 --> 00:09:23,200
to use extra charge de the next thing we need is to

145
00:09:23,200 --> 00:09:28,850
column called because a match is called the same is called item

146
00:09:28,850 --> 00:09:32,990
and will say do something actually was a

147
00:09:32,990 --> 00:09:36,430
room

148
00:09:36,429 --> 00:09:43,429
an image from or program number

149
00:09:47,178 --> 00:09:52,009
number so if they weren't certain number and ices room

150
00:09:52,009 --> 00:09:55,249
2007 I mean it's a woody is so

151
00:09:55,249 --> 00:09:58,399
missus item here and

152
00:09:58,399 --> 00:10:01,568
Ste face first stash

153
00:10:01,568 --> 00:10:05,318
and am

154
00:10:05,318 --> 00:10:09,009
table extra charges 20 item

155
00:10:09,009 --> 00:10:14,038
and

156
00:10:14,038 --> 00:10:17,668
space and

157
00:10:17,668 --> 00:10:24,188
a.m. something for one for the amount of

158
00:10:24,188 --> 00:10:27,629
nothing as are actually keeps

159
00:10:27,629 --> 00:10:31,389
leader that to press OK his first wrong

160
00:10:31,389 --> 00:10:34,658
we get iTunes to dinner

161
00:10:34,658 --> 00:10:37,678
and interest the and we just need to charge which

162
00:10:37,678 --> 00:10:42,318
is I'm so we got we got to queries now

163
00:10:42,318 --> 00:10:46,149
okay

164
00:10:46,149 --> 00:10:51,359
so sorry for them together very simple take the

165
00:10:51,359 --> 00:10:56,349
first query and just into

166
00:10:56,349 --> 00:11:01,139
SQL View and then take a second period rock band SQL query

167
00:11:01,139 --> 00:11:04,589
skill view even highlight both

168
00:11:04,589 --> 00:11:08,069
a lines in this case control CEC copied it

169
00:11:08,068 --> 00:11:11,628
am in the first query

170
00:11:11,629 --> 00:11:15,959
delete the semicolon at the end

171
00:11:15,958 --> 00:11:20,768
press Return and go to Union ship should be

172
00:11:20,769 --> 00:11:23,918
capital letters Union all

173
00:11:23,918 --> 00:11:27,369
pressure turn in pace in the second query

174
00:11:27,369 --> 00:11:31,548
numbers run we've actually for the two queries together

175
00:11:31,548 --> 00:11:35,578
and so we've got the charges down here

176
00:11:35,578 --> 00:11:40,168
and we've got the but he's up here the one thing we do wanna do you organize it

177
00:11:40,168 --> 00:11:41,178
but booking day

178
00:11:41,178 --> 00:11:46,098
now when you I'm ordering you have to use a name for the shields

179
00:11:46,099 --> 00:11:50,039
up top you can use a name in the fields down below

180
00:11:50,038 --> 00:11:53,239
so I will say a.m. border

181
00:11:53,239 --> 00:11:57,209
by to

182
00:11:57,208 --> 00:12:01,198
table bookings door King

183
00:12:01,198 --> 00:12:07,618
take and runner

184
00:12:07,619 --> 00:12:14,359
syntex missing operator buddy something silly

185
00:12:14,359 --> 00:12:17,668
sorry space the

186
00:12:17,668 --> 00:12:23,399
a great so now its stake in everything and it or did it by putting day

187
00:12:23,399 --> 00:12:27,418
key point even know you the second query

188
00:12:27,418 --> 00:12:32,688
each table X charges to exit charged a you still user names refer to

189
00:12:32,688 --> 00:12:36,248
in first select query which is table bookings the booking date it does it for

190
00:12:36,249 --> 00:12:36,769
everything

191
00:12:36,769 --> 00:12:41,538
okay okay so now I just name it

192
00:12:41,538 --> 00:12:45,989
saving cooler cleary looking

193
00:12:45,989 --> 00:12:49,939
X costs group booking

194
00:12:49,938 --> 00:12:55,838
costs extra charges

195
00:12:55,839 --> 00:13:00,139
Union Pacific and the second clear you can just

196
00:13:00,139 --> 00:13:03,188
get rid alright so

197
00:13:03,188 --> 00:13:06,348
that's a union query made a complicated

198
00:13:06,349 --> 00:13:09,439
I am Becky place remember you inquiries

199
00:13:09,438 --> 00:13:12,889
you craig to standard select queries

200
00:13:12,889 --> 00:13:17,369
make sure that the shield staff to be named the same make sure the head they

201
00:13:17,369 --> 00:13:19,569
use the same datatype serves a number

202
00:13:19,568 --> 00:13:23,259
for the first column in the first query attorneys to be

203
00:13:23,259 --> 00:13:26,899
a number type for the first column second query menu

204
00:13:26,899 --> 00:13:30,288
day night this case texting currencies eventual solution

205
00:13:30,288 --> 00:13:33,418
found still types here is make sure that the same

206
00:13:33,418 --> 00:13:36,538
and then when you put in Scopus remember little things like

207
00:13:36,538 --> 00:13:39,668
to remove T a.m. semicolon

208
00:13:39,668 --> 00:13:42,968
from the and here I

209
00:13:42,969 --> 00:13:46,859
you you know when the at the second things like order by

210
00:13:46,859 --> 00:13:51,699
I'm you need to put right at the end and any names you referring to

211
00:13:51,698 --> 00:13:55,318
and you were to buy clothes need to be from the first select query

212
00:13:55,318 --> 00:13:59,639
here otherwise it won't work okay so thank you for watching

213
00:13:59,639 --> 00:14:04,509
and OC next video will be doing that all over again i think is a go second union

214
00:14:04,509 --> 00:14:05,278
crease make

215
00:14:05,278 --> 00:14:06,158
so CNN