Tip:
Highlight text to annotate it
X
I've made a copy of
odb17 intro to queries and I'm calling this
to odb19 alias
and sort after making the copy I want to mention that
I took the advice up postman 1372
in reduced all my photos to be 300 pixels in height
I used paint.net to do this
but any photo editor will work I clicked
on image resize
I clicked on that maintain aspect ratio
and I change the height to be 300 pixels
clicking OK
I reduced are my pictures this way
and saved them to a file looking at the size
all for pictures between 10 and 16 kilobytes
and I was able to add all my photos
just as I did in video 15 I was able to load them
all in at one time without any problems
thanks postman 1372 for that suggestion
and we can see our pictures on our form
at 300 pixels high looks just fine
there is no problems with the size picture and our file size:
is still kept very low let's go back into
queries and I'm going to click on design view
also known as QBE I like design mode At least for these introductory examples
because it will prevent us from making typographical errors when we
type in the table name, let's add that in or when
adding in our field names let's put in last name, first name,
Department and group so we don't have to worry about
typing those names in wrong and we also have
a list here to work from were as when I open it up in
sequel view we just had a blank screen and nothing really to work with
these row names on the side here
we've got something in field, table, and visible
these are all checked showing us that we want them all the show up
in our result set. table and field shows us
exactly what table and field will show up in our result set the table name
is important because we could have the same field name
on two different tables the Alias
what this will do will allow us to
change the name the column in our result set. right now
we've got last name let's change this to
surname I'm going to come over to department
and type in Dept.
so we should see our alias names instead of the field names
in our result set I'm going to hit f5
and run the query and we see
surname did replace last name but Dept.
did not replace department and we have nothing down in this cell
that happen because, although I typed it in, D e p t .
I didn't hit enter or tab
or click off this box. I'm going to hit enter
now it's locked in there now when I hit f5
we see that it replaced Department
with Dept. period let's see what the SQL code looks like for this
clicking on design view on/off we see it's not formatted
I will reformat this - now we can see how to add an alias
in SQL View we need to
follow the field you want to give the alias with as
and then our Alias name in double quotes
let's turn off our preview by hitting F4
and go back into design view
the next thing we see down here is sort
let's say we want to sort by department
will make this ascending and then last name
will also make this ascending I'm also been sure to hit
tab or enter or click of that cell to make sure gets locked in
otherwise we'll have the same problem we had with Department in it won't be
recognized
I'm going to run this
and it looks like it sorted by last name
but not Department
they both.... they both made it in there
let's go over SQL View and see if we can tell what the problem is from there
again we have our single line... I'll reformat this
with this formatted it's a lot easier to read
and we can see that we use order by in SQL View
instead of sort we can also see that it's sorting by
surname before it sorts by department
that's why it looks like it departments not getting sorted at all
because we don't have any two people with the same surname
lets swap these around
now we're going to sort by department then by surname
I'm going to hit f5 and we see
we get our department and then alphabetically
all our surnames. let's see how this looks
in design view and see where we went wrong
so looking at our design view
we have last name in here twice the first time it's visible
the second time its invisible but we're sorting on this
so in design view its sorts left to right
and in this case we want it to show up to the left
of the department that were are already sorting so we don't sort
on this occurrence but we add it in again to the right
and we make it invisible or
we don't check the visible checkbox
so in design view we sort from left
to right and in SQL View
we use Order By and go top to bottom
one last thing I'll mention in this video is if we want to rearrange
our fields in design view we need to drag them left
and moving last name over in over
you can move it all the way to the end
but then to get this back over here we can't just move it to the right
that doesn't seem to work so I need to move the original last name
then the first name then Department
then group and now we've got it back to where we had it
I'm going to save this query as
odb19-qry-sort
I'm going to save the database
and while we are in here I'm going to change the other query name
to be odb17
query and renaming this to odb17
is just to make sure we know which tutorial
we created that query in
and once again, I need to save the database
all all cool all
all