Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

News      User Comments     History     Notify Me

3/15/2011 5:39:44 AM
Access SQL Seminar Part 1
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
This seminar will teach you the basics of the SQL programming language. You will learn how to construct SELECT queries, filter and sort your results, and use basic SQL commands and operators.

This seminar was developed to teach you how to use the SQL language to enhance your Access databases. As I create more and more advanced Access seminars, I see that it's VITAL for my students to have a firm grasp of the SQL language. Knowing just a little bit of SQL can help you tremendously. You can use SQL to enhance your forms and reports. You can create dynamic SQL statements to change list and combo box data on the fly. Plus, you can reduce the clutter in your database by getting rid of "saved" queries.

This seminar has several goals. You will:

1. Learn about SQL and what it is
2. Discover why you should learn to program in SQL
3. Create basic SQL SELECT statements
4. Learn the FROM, WHERE, and ORDER BY clauses
5. Use the SQL operators and keywords such as AND, OR, and IS NULL
6. Work with the LIKE command and wildcard characters
7. Learn the IN, DISTINCT, DISTINCTROW, and other commands
8. Build a list box with a dynamic SQL sort
7. Create a form with a dynamic SQL RowSource

Permanent Link
Course Link: Access SQL Seminar Part 1
Keywords: access sql structured query language
Page Tag: whatsnew
Post Reply

If one has a long SELECT statement with a number o Comment from Richard Wilson @ 8/31/2016
If one has a long SELECT statement with a number of fields in it, does the use of "DISTINCT" at the outset make it the functional equivalent of DISTINCTROW?  If so, how does one use DISTINCT when there is a long select clause.  In my situation, I have a query that makes a"FullName" and it is at the first of the SELECT clause which also has address information lots of other stuff.  I only want DISTINCT to apply to FullName.  I tried and it doesn't.  How should I fix it?  I note the SELECT clauses in the video all just contain one field.


Reply from Alex Hedley:

You need to have only the Field you wish to be DISTINCT in the Query, unless you use a subquery.
Show Just This Thread        Post Reply
Phone list Comment from Anonymous @ 6/3/2016
How do you handle a phone list if you have a separate but related table for PhoneT and related to another TypeT which includes such things as "office," "home," "cell," "fax" etc?


Reply from Alex Hedley:

PhoneT could have a TypeID which links to the TypeT.
Combo box on the PhoneF which has the TypeT list.
Show Just This Thread        Post Reply
Recordsets Comment from Mohamed Salim @ 4/16/2016
Hello Mr Richard.
I've learned Access from scratch, thanks to you. I was wondering if I can do with SQL the same thing with ADO Recordset function. I really want to focus on one thing and master it rather than distract myself with different options. So is having a full understanding of SQL is enough to have a full control on my recordset?, or I should also know about Recordsets option??
Thank you


Reply from Alex Hedley:

Recordsets are very powerful for traversing data, SQL is the method to get that data, they have different purposes, if you want to have a lot of control knowing both is key.
Show Just This Thread        Post Reply
vba Comment from Robert Maddox @ 10/26/2015
Issue Need Advice-
I can run the simple VBA of Customer List Form (#6) on my Windows 8.1Pro + Acces MSO 365, however when I try & run same on my Windows 7 Pro +Access 2007 Laptop it will not run. Any suggestions as to the issue?


Reply from Alex Hedley:

Upgrade from 2007, its very buggy.
Show Just This Thread        Post Reply
Questions Comment from Anonymous @ 10/11/2015
Hi Richard, just 2 quick questions: 1)For the LIKE keyword, why would you not put '' around the [FirstLetter] Parameter piece, when you concatenate it with '*'? 2)Why does the * and # symbols from Access use the same symbol (!) in other SQL servers? Thank you very much for your help.


Reply from Alex Hedley:

1) Do you have the full string he uses? It's been a while since I've watched this one

2) In what context. Access uses # for dates, * for select all Fields
You use * in T-SQL
Show Just This Thread        Post Reply
Few questions Comment from Joe Beniacar @ 10/11/2015
Hi Richard, I have a few questions about this lesson: 1)Is a CrossTab Query like a Pivot Table (in Excel)? 2)If you had multiple Tables in your query, would you have to specify the "TABLE.fieldname" in the WHERE condition too? 3)For Operator Precedence, why doesn't NOT evaluate before AND/OR? - Isn't it kind of like a Comparison Operator, in that it also applies to just 1 field - while AND/OR applies across multiple conditions to multiple fields at the end? Thank you very much for your help.


Reply from Alex Hedley:

1) You don't have the same power as a Pivot Table since you don't have the drag in columns, filters etc. Think they removed Pivot Tables from Access 2013 (Discontinued features)

2) Yes you would have to be specific about your WHERE clause, just alias your Tables and add them there.

3) MS Article
Show Just This Thread        Post Reply
Multiple Tables Comment from Joe Beniacar @ 10/11/2015
Hi Richard, if you have multiple Tables and want to select the "*" from one of them, would you say: SELECT Table.* ?


Reply from Alex Hedley:

Indeed, you can choose the Fields by putting their Table name in front.
Often you would alias the Table so
SELECT t.field1 FROM Table t
Instead of
SELECT Table.field1 FROM Table
Makes it easier and less typing, we are lazy programmers and anything to make life quicker and easier :p
Show Just This Thread        Post Reply
Listbox Query Comment from Tom Dlugosh @ 1/3/2015
I created a customer form and added a list box to show the customer representatives associated with that customer. Works great! I then added a list box to show the ship to addresses associated with the same customer. It doesn't work! The Ship to addresses show up in different customers, sometimes the one with a customer ID 1 digit higher, sometimes 2 digits higher and occasionally anywhere in the ship to table. The SELECT statements are located in the list box properties and are identical in their format. What am I missing here?
Thanks,
Tom


Reply from Alex Hedley:

Make the Queries as their own object with a Form Criteria.
You can then set the Listbox source to these.
Open the Form and run the Query from the Navigation Pane to see if it's working as expected.
Check you are using the correct criteria and it has the right ID.
Show Just This Thread        Post Reply
List Box Column Width Comment from Grant R @ 2/6/2014
I noticed that my column widths were a little too small after I made my CustomerListF. Is there a way to get back in to edit the column widths? I've tried poking around with little success.
Show Just This Thread        Post Reply
Error prompt Comment from Jim O @ 1/24/2014
I have tried to apply this code to a button as an 'On Click' Event. But I keep getting an error message saying it expect an end statement. The code works if I run it through a regular query but I want to be able to manulipulate the data more easily with a combo box.

ProductList.RowScouce =

SELECT AddressDirectoryT.AddressID, IndividualxGroupT.GroupID, IIf([FirstName],[FirstName] & " " & [LastName],[CompanyName]) AS Contact
FROM AddressDirectoryT INNER JOIN IndividualxGroupT ON[ AddressDirectoryT.AddressID = IndividualxGroupT.AddressID];

End Sub

Jim O



Reply from Richard Rost:

It's probably the bracketing of your join field names. You'd need brackets around EACH field. Also, if possible, I don't like to put functions in my SQL statements. Almost always better to create a helper query with the function then use THAT query for your SQL statement data source.
Show Just This Thread        Post Reply
Error prompt Comment from Jim O @ 1/23/2014
Yes their was an error (my doing) when I copied the name I included the brackets _Click(). As soon as I removed them everything worked fine.
Show Just This Thread        Post Reply
Error prompt Comment from Jim Ogier @ 1/22/2014
I built the forms as shown in the video but when I add the Event Open SQL statement I get an error prompt (highlighted in red) that an = sign is expected and I am unable to proceed from their. Any ideas what is wrong?

Jim O


Reply from Richard Rost:

What's the line of code that's generating the error? You're probably missing something.

Show Just This Thread        Post Reply
Comment from Zied Ben Afia @ 12/13/2013
Hey Richard!

Can we use NameList.RowSource = ""
instead of Me.RecordSource = ""   ?
I don't get the difference between both of them

Show Just This Thread        Post Reply
Comment from Genet Pinna @ 12/10/2013
I use Access 2013 version.
Thanks,
Show Just This Thread        Post Reply
Comment from Genet Pinna @ 11/16/2013
If you do want to see data from cutomerT table which you did not use them when you run a query, how do you define?
Show Just This Thread        Post Reply
Comment from  MK @ 4/5/2012
One last question on this video...the prompts aren't coming up automatically in the build event programming terminal. For example you started typing "open form" and it came up as a tag. Is there a way to turn this feature on? And do you know off hand the hotkeys to toggle back and forth between the build event terminal and the access spreadsheet? Thanks again.


Reply from Richard Rost:

What version of Access are you using?
Show Just This Thread        Post Reply
Comment from  MK @ 4/5/2012
When I press the button I made, it prompts me to enter a parameter value. Any idea what I've done wrong?


Reply from Richard Rost:

You probably have something spelled wrong. A parameter value prompt pops up when Access can't find a field you're referencing.
Show Just This Thread        Post Reply
Comment from  MK @ 4/4/2012
How do I "select all" in Access? Ctrl+A doesn't seem to be working for me. Thank you.


Reply from Richard Rost:

Select all of what? CTRL-A does work, but only if you're in certain circumstances. If you're in a table, for example, and you want to select all of the records, make sure you have ONE record highlighted first (click on the record selector button on the left) and then hit CTRL-A.
Show Just This Thread        Post Reply
Comment from WILL @ 7/20/2011
in video #7 can u use a group option insead of buttons? if so how?
Show Just This Thread        Post Reply
Comment from David @ 5/13/2011
Richard,

Do you have enough students requesting part 2 and part 3 yet? I hope so because I'm looking forward to taking the seminars.
David


Reply from Richard Rost:

Yep. Finishing up Part 2 now. Shouldn't be much longer...
Show Just This Thread        Post Reply
Access SQL Seminar Part 1 Comment from David @ 5/12/2011
Richard,

Again the class.  When do will you release Part2 and Part3?

And how is the PDF Handbook coming along?
Show Just This Thread        Post Reply
Comment from  Monty @ 4/6/2011
What if you had over 1000 items(five fields listed per item). Would it still be applicable to use a list box for the applications you're applying to them in SQL 1, lesson 6? I ask because I have just such a sitution.


Reply from Richard Rost:

Sure. The number of items (records) doesn't really matter.
Show Just This Thread        Post Reply
Access SQL Seminar Part 1 Comment from R Fleming @ 3/26/2011
Great stuff - when will Part 2 be ready


Reply from Richard Rost:

Working on it. I'm on the road right now for a few weeks, but when I get back, it's first on my list.
Show Just This Thread        Post Reply
Access SQL Seminar Part 1 Comment from Thomas Fries @ 3/18/2011
This is exactly what I wanted


Reply from Richard Rost:

Wait 'til you see part 2. :)
Show Just This Thread        Post Reply
Comment from  Bob Lilly @ 3/16/2011
Well you hit and other HomeRun, looking forward to SQL 2!


Reply from Richard Rost:

Thanks!
Show Just This Thread        Post Reply
Access SQL Seminar Part 1 Comment from Harry Goldstein @ 3/16/2011
Will there be a PDF handbook for these lessons?


Reply from Richard Rost:

Yes. It's in the works.
Show Just This Thread        Post Reply
Access SQL Seminar Part 1 Comment from jimmy @ 3/15/2011
i watch this seminor part 1 i very injoied And this  enrich my knowledge.

thousand thanks for the wonderful course
jimmy


Reply from Richard Rost:

Thanks, Jimmy.
Show Just This Thread        Post Reply
Access SQL Seminar Part 1 Comment from Alex Hedley @ 3/15/2011
Brilliant Seminar,
Loads covered!
Great to have it all in one place for reference.
A great grounding in SQL, roll on Part 2 :)


Reply from Richard Rost:

Thanks.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 1+8:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

7/19/2017Access Developer 2 and 3 Released
7/19/2017Microsoft Access Developer 3
7/19/2017Microsoft Access Developer 2
6/30/2017Access Developer 2 is Finished
6/7/2017Microsoft Access Developer 1
6/6/2017Access Developer 1 is Finished
5/18/2017Microsoft Access Advanced 6
5/17/2017Access Advanced 6 is Online
4/9/2017Microsoft Access Advanced 5
4/9/2017Microsoft Access Advanced 4
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP