1
00:00:00,900 --> 00:00:04,549
hello i'm ok taxes 2013 she two rooms this video I'm gonna be talking you

2
00:00:04,549 --> 00:00:05,220
through

3
00:00:05,219 --> 00:00:09,410
multi-table queries to gonna play along follow the link below the video to the

4
00:00:09,410 --> 00:00:11,300
down a section a website and download

5
00:00:11,300 --> 00:00:15,740
axis 2013 TVs are seven an open it up

6
00:00:15,740 --> 00:00:18,789
just like me a great group

7
00:00:18,789 --> 00:00:22,140
but what we're gonna do okay mostly work where you are

8
00:00:22,140 --> 00:00:27,260
poll also similar to queries and we create query rooms

9
00:00:27,260 --> 00:00:32,289
sorted ascending but remember that causes that have one table

10
00:00:32,289 --> 00:00:37,390
and always able Crary is a query where you use more than one table

11
00:00:37,390 --> 00:00:40,969
and multi-table and why do you need them

12
00:00:40,969 --> 00:00:44,020
I'm well if we take a look

13
00:00:44,020 --> 00:00:48,899
Anna are table who for booking details

14
00:00:48,899 --> 00:00:52,020
10 I'm

15
00:00:52,020 --> 00:00:56,430
if you have a table booking details and table rooms

16
00:00:56,429 --> 00:00:59,780
you can see that they're linked by

17
00:00:59,780 --> 00:01:03,439
this field here which is the remind the foreign key no love the

18
00:01:03,439 --> 00:01:07,170
numbers here correspond to numbers in this

19
00:01:07,170 --> 00:01:12,109
I'm table here and the reason we've broken down it's two tables instead of

20
00:01:12,109 --> 00:01:12,798
having

21
00:01:12,799 --> 00:01:16,118
the room numbers shown here is I

22
00:01:16,118 --> 00:01:20,209
because if balkan normalization if you don't normalization is go back a few

23
00:01:20,209 --> 00:01:21,179
videos

24
00:01:21,180 --> 00:01:26,150
a couple videos and check that one out with the ideas are often have

25
00:01:26,150 --> 00:01:29,659
one enormous table has all the patroness

26
00:01:29,659 --> 00:01:34,070
really unwieldy in yo la serpiente de ti break it down into

27
00:01:34,069 --> 00:01:39,578
discrete truck today to and but what we can do that having broken down as we can

28
00:01:39,578 --> 00:01:41,578
use queries to put it back together again

29
00:01:41,578 --> 00:01:45,809
so for example if shocked close another know why

30
00:01:45,810 --> 00:01:48,899
before when it's actually see this

31
00:01:48,899 --> 00:01:52,978
the said if seeing 123 123 for for

32
00:01:52,978 --> 00:01:57,459
the numbers I wanted to see the actual number I can do that using the query

33
00:01:57,459 --> 00:02:00,649
I will show you how are now so you go to create

34
00:02:00,649 --> 00:02:05,170
and your query design and here we choose the tables we were

35
00:02:05,170 --> 00:02:08,969
so we're gonna choose table booking details and table rooms

36
00:02:08,969 --> 00:02:12,770
from personal control parts of my life I for them click at

37
00:02:12,770 --> 00:02:15,890
now because the two tables

38
00:02:15,889 --> 00:02:21,369
already have a already related a room I T in Ramadi FK

39
00:02:21,370 --> 00:02:24,830
you ready Craigslist join for us which is really useful

40
00:02:24,830 --> 00:02:29,700
in all we do for it thank every single record and the DoubleClick in

41
00:02:29,699 --> 00:02:34,979
with the field so not records from table booking details we have a table rooms

42
00:02:34,979 --> 00:02:36,318
for second in our runner

43
00:02:36,318 --> 00:02:41,488
I'm I just pretty much get the same as stay working details

44
00:02:41,489 --> 00:02:46,219
ice get the numbers if I take al

45
00:02:46,219 --> 00:02:50,229
remind tsk and I track over room number

46
00:02:50,229 --> 00:02:54,738
now when I run a I'm exactly the same table which is

47
00:02:54,739 --> 00:02:58,050
booking details bar I have

48
00:02:58,050 --> 00:03:01,950
room number is so the room I D which is for me better to see

49
00:03:01,949 --> 00:03:05,329
so what I've done using this query is i've

50
00:03:05,330 --> 00:03:10,569
taken two entities are split down enough so put back together again

51
00:03:10,568 --> 00:03:15,219
and that's kinda why we use multi table query so that we can reconstitute

52
00:03:15,219 --> 00:03:19,408
data into information it's useful for us so it kind of

53
00:03:19,408 --> 00:03:24,549
I it's the bit where once we normalize we put it all back together again so

54
00:03:24,550 --> 00:03:24,890
it's

55
00:03:24,889 --> 00:03:27,949
popular with a magic trick is a look exactly the same as

56
00:03:27,949 --> 00:03:32,348
if you just a single table with room numbers and booking details

57
00:03:32,348 --> 00:03:36,348
by actually follows the rules normalization I so much better data

58
00:03:36,348 --> 00:03:36,979
model

59
00:03:36,979 --> 00:03:41,359
and makes everything whose dis but the better way of creating a database

60
00:03:41,360 --> 00:03:46,760
at now question Christian Christian Christian

61
00:03:46,759 --> 00:03:51,449
and when you create a most same query like this

62
00:03:51,449 --> 00:03:55,000
I have the moment I said I've taken

63
00:03:55,000 --> 00:03:58,848
room ID out not in room number I would

64
00:03:58,848 --> 00:04:03,369
just moms make his point I would do something like this for report but for

65
00:04:03,370 --> 00:04:04,310
wanted to create a

66
00:04:04,310 --> 00:04:08,739
report because I save them a quick query testimony

67
00:04:08,739 --> 00:04:12,459
in closer for cooking query test

68
00:04:12,459 --> 00:04:19,459
and I create I report now obviously haven't started to

69
00:04:19,870 --> 00:04:23,329
Burke when of not having crazy the report you can see that I've got all the

70
00:04:23,329 --> 00:04:26,409
information about the booking details but also got the room number now which

71
00:04:26,410 --> 00:04:26,740
is

72
00:04:26,740 --> 00:04:29,819
why wanna see other wanna see 1234

73
00:04:29,819 --> 00:04:33,089
87 so doesn't mean anything to me if you're creating

74
00:04:33,089 --> 00:04:36,329
report no close to

75
00:04:36,329 --> 00:04:39,930
let you know a close early like to ukraine report this

76
00:04:39,930 --> 00:04:43,100
wave a.m. having the two tables

77
00:04:43,100 --> 00:04:47,449
and then dropping a disorder related

78
00:04:47,449 --> 00:04:52,860
I'm a tribute from the other table linens case for number is great for

79
00:04:52,860 --> 00:04:53,970
reports

80
00:04:53,970 --> 00:04:57,370
if ukraine form and I'm

81
00:04:57,370 --> 00:05:00,459
would you do this with you

82
00:05:00,459 --> 00:05:04,899
crop room number in there or would you stick room I T

83
00:05:04,899 --> 00:05:08,099
we first thought would be actually are the room number cuz on creating a former

84
00:05:08,100 --> 00:05:09,650
want to actually see

85
00:05:09,649 --> 00:05:13,069
number the room is no good to me to see one two three four and the

86
00:05:13,069 --> 00:05:16,430
I D keys but actually

87
00:05:16,430 --> 00:05:20,819
we closed our what's click on table booking details

88
00:05:20,819 --> 00:05:24,379
and I create form

89
00:05:24,379 --> 00:05:27,459
now missus a moving away from over tailhook responders

90
00:05:27,459 --> 00:05:30,779
resume explaining this I just want you to know so when

91
00:05:30,779 --> 00:05:33,859
used in I'm and

92
00:05:33,860 --> 00:05:38,430
like I said one of them is if ukraine report you definitely to mod table query

93
00:05:38,430 --> 00:05:42,069
if you just basing I E form of a

94
00:05:42,069 --> 00:05:47,009
table I view myself base of a query for example you might want to sort it but

95
00:05:47,009 --> 00:05:50,319
you don't have to and doesn't actually three mo table because if I go to worse

96
00:05:50,319 --> 00:05:51,918
Israel mighty

97
00:05:51,918 --> 00:05:55,149
working do I'm seeing the one at the moment I can actually change this

98
00:05:55,149 --> 00:06:00,250
to reflect the room number and without actually changing the underlying data so

99
00:06:00,250 --> 00:06:02,329
who is still holds the

100
00:06:02,329 --> 00:06:06,788
primary case too far right click here and go

101
00:06:06,788 --> 00:06:11,889
changed to combo box working nowadays I can actually

102
00:06:11,889 --> 00:06:15,240
changed combo box to select dates firm

103
00:06:15,240 --> 00:06:19,360
room and I do that by going to data row source

104
00:06:19,360 --> 00:06:24,410
click on the three docks I'm interested in Table rooms

105
00:06:24,410 --> 00:06:28,580
by drop in Table remove remind the and room number

106
00:06:28,579 --> 00:06:32,818
you know what while Amir I'm going to sort in ascending this runner

107
00:06:32,819 --> 00:06:36,870
straight repairing a query insider form

108
00:06:36,870 --> 00:06:39,978
close to so at the moment

109
00:06:39,978 --> 00:06:45,490
of still saying number one but I can change the column count 2-2

110
00:06:45,490 --> 00:06:49,750
and I can change the column widths

111
00:06:49,750 --> 00:06:53,949
a let's just say one and three

112
00:06:53,949 --> 00:06:56,960
networks

113
00:06:56,959 --> 00:07:01,620
well maybe has actually me for you so if you click here

114
00:07:01,620 --> 00:07:05,478
because you see when you click in a although was storing the

115
00:07:05,478 --> 00:07:10,060
ID field reconnecting now see the room number

116
00:07:10,060 --> 00:07:13,089
we could take that step for the because

117
00:07:13,089 --> 00:07:16,168
if we so clicked in Ramadi if we click on

118
00:07:16,168 --> 00:07:19,568
column with fear and change it 0 which refers to the

119
00:07:19,569 --> 00:07:23,610
ID field first column is 0 with 0 which means in a pissy

120
00:07:23,610 --> 00:07:27,120
an essay for which refers to the room number

121
00:07:27,120 --> 00:07:32,629
now we've completely hidden the ID field and I would just seen the room number

122
00:07:32,629 --> 00:07:36,399
so I've been used in more detail query of tissues I've actually basis just of a

123
00:07:36,399 --> 00:07:37,629
normal table

124
00:07:37,629 --> 00:07:40,659
and we've used the ID filled

125
00:07:40,660 --> 00:07:43,900
I and still storing the ID number but we've used

126
00:07:43,899 --> 00:07:47,128
a century lookup to actually display

127
00:07:47,129 --> 00:07:50,289
day to this meaningful for us okay so

128
00:07:50,288 --> 00:07:54,529
now the small digression but I think who were the one as well

129
00:07:54,529 --> 00:07:59,619
you consider and so just to recap

130
00:07:59,620 --> 00:08:03,478
movie table pre-release bill more uses than

131
00:08:03,478 --> 00:08:08,029
marv to describe the we remain uses so reconstitute data

132
00:08:08,029 --> 00:08:11,359
from a normal my state back into something that we can actually

133
00:08:11,360 --> 00:08:14,590
visually see and obviously very common to use them in

134
00:08:14,589 --> 00:08:18,179
reports I'm now look who said

135
00:08:18,180 --> 00:08:22,098
I if you're Craig form have a serious think about whether you would need to

136
00:08:22,098 --> 00:08:22,848
put

137
00:08:22,848 --> 00:08:26,128
two tables together because they are you better off using the ID

138
00:08:26,129 --> 00:08:29,139
from way factory have to use the ID from

139
00:08:29,139 --> 00:08:32,240
take the table and then change it to a combo box in user

140
00:08:32,240 --> 00:08:36,399
lookup field so that's more a table queries hope that wasn't too confusing

141
00:08:36,399 --> 00:08:37,668
and hopes in exterior