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  
 
NEW Release: Access Developer Level 3   dismiss
 
 

Microsoft Access SQL Seminar, Part 2
 
Learn how to supercharge your databases using SQL
 

 

This nearly five (5) hour long video seminar will pick up where Part 1 left off. You will learn more about the SQL programming language. We will focus on action queries, aliases, joins, crosstabs, union queries, aggregate functions, and more.

Learn More...

Click on the video image to the right to learn more about exactly what's covered in this seminar. The video is about 8 minutes long and fully explains everything that's discussed. Then, continue reading the course outline below if you have further questions.

AccessLearningZone.com
  8 minutes
 
Seminars - Access SQL Part 2
Description: Learn the SQL programming language to enhance your Microsoft Access database projects
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: Access SQL Seminar, Part 1
Running Time: 4 hours, 58 minutes
Cost: $159 - Order multiple courses to receive a discount up to 50% off

 

This seminar has several goals. You will:

1. Learn about Action queries and when to use them
2. Discover how to launch queries from VBA code
3. Create Aliases for your field and table names
4. Learn the different types of Table Joins
5. Construct a Union query
6. Work with a Crosstab query

7. Perform calculations in your SQL statements
8. Explore the different Aggregate query functions
9. Utilize string, math, time/date, and other query functions

We will begin by learning about Action queries. We'll start with an update query to change the data in a table. We'll also learn how to work with an update query that joins data from multiple tables.

 

Next we will build a form that we can use to automatically update our product prices based on new vendor pricing. We'll use an update query and inline SQL code to do this dynamically. The vendor will give us a table with new product pricing (with his product codes) and we'll have to join those products to our product table. We'll also set a default markup for each product category and be able to update the prices based on this markup. This is a great example.

 

Next we'll use an Append query to create a system log that will track everything that happens in the database. You'll also learn how to use an append query to make a batch-backup of large groups of product information - for example, saving historical pricing on all of your products so you can go back and see what you were selling a specific product at 2 years ago.

 

 

You will learn how to use a Make-Table query to make time/date-stamped backups of your tables. You should do this any time you plan on making changes to a table, or running a potentially dangerous action query. Safety first. This is also good for saving multiple copies of your customer table so you can refer back and compare changes that were made later.

 

You will learn how to use a Delete query to remove inactive products from your database (after archiving them or backing them up first, of course).

 

Next we'll learn how to use the TOP X command so you can see the top 10, top 5, top 50%, (and so on) of records in your table. We'll make a form where the user can choose how many records to view, which field to sort by, and how to sort (ascending or descending). We'll use inline SQL to display the results in a form PLUS we'll make a button to generate a printed report with the same criteria.

 

 

Next, we'll learn how to create Aliases to make referring to our field and table names easier. This also allows you to change the name of a field on the fly.

 

You will learn about all of the different kinds of query Joins. There are inner joins, outer joins, left joins, right joins, self joins, full joins, cartesian products, and lots more you can do when you join two or more tables together.

 

I'll show you a nifty example of a join where you can have one table with team names in it and with just one simple SQL statement generate a schedule of games where each team plays each other team once - but not itself.

 

You'll learn more about the IN function; specifically how to nest an SQL statement inside of the IN function to provide its list of values. You'll also see how NOT IN works.

 

 

We'll create a Union query where you can take multiple different tables and join them all together in one result set. This is great if you want to create a mailing list that includes customers, employees, vendors, and everyone else who has a name and address. Union queries are impossible to build in Access unless you know SQL.

 

You will learn how to perform mathematical calculations in your SQL statements. Remember, if it's something you can calculate on the fly, you usually don't need to save it in your table. Keep your databases small and efficient.

 

We'll cover string concatenation, and a really cool trick that you can use in SQL to ignore null strings that doesn't require any fancy functions. For example, notice the third record below is missing a middle initial. Not a problem.

 

You will learn how to use Aggregate queries to group and total based on various functions (sum, average, max, min, etc.) and criteria. You'll also learn some techniques that are specific to SQL and are very difficult to produce with the standard Access query designer. Notice below I'm using an SQL statement as the WHERE condition inside another SQL statement. This will show all of the orders that are over the average of all of the order totals. Great SQL trick.

 

Next, we'll cover many of the popular functions that I like to use with my SQL queries. We'll start with the string functions such as Trim, Left, Right, InStr, Len, UCase, LCase, and StrConv - which can convert to "proper" name case.

 

 

 

You will learn all of my favorite date and time functions, like DatePart, DateSerial, DateAdd, DateDiff, and lots more. I'll show you how to take a date/time value apart to get its components, and I'll show you how to put together another date using those component parts. We'll learn how to add and subtract dates to determine things like "what date is exactly 6 months in the future from the sale date?"

 

Next, we'll cover a bunch of additional queries like ABS, Int, Fix, Round, Sqr, IsNull, Nz, IIF, and the conversion functions like CStr and CCur. This, and the previous 2 lessons, will teach you about 95% of the functions you will need to use in your queries on a regular basis.

 

Finally, we'll tie everything together with one of my favorite example lessons. I will show you how to make a three-level set of cascading combo boxes. This is where you pick a country and then the state combo box is filtered to show you just states from that country. Then the same thing happens to the next box which shows you just the offices that are in cities in that state. This all happens with custom SQL written in the AfterUpdate events of these combo boxes.

 

But that's not all. Not only will we go top-down, but I'll also show you how to go bottom-up. If you move to a different record, I'll show you how to take the office/city that you know and use it to look up the state and country and then dynamically refresh and update all three combo boxes. This is real cool stuff!

 

Again, this seminar is perfect for anyone who wants to learn how to supercharge their Microsoft Access databases with the added power of custom SQL. It is the second part of a three-part series. Part 3 will teach you how to modify the design of your database (table and query structure) using SQL.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access SQL Seminar 2 Outline

00. Intro (8:32)

01. Update Query 1 (14:47)
Four types of action queries
Update query
Create Product Table
Update query in Access QBE designer
Update with no parameter
SQL View
UPDATE SET
WHERE
Updating multiple fields

02. Update Query 2 (10:27)
Update Query with Multiple Tables
INNER JOIN ON clause
Update vendor product pricing

03. Update Query Form 1 (19:21)
Markup Text Box
Category Combo
SQL Rowsource for Listbox
Custom WhereStr

04. Update Query Form 2 (14:20)
Default markups for each category
Column() combo box property
Docmd.RunSQL
Run update query to perform markup
Format function

05. Append Query 1 (19:21)
Create a Log Table
Append Query with no input table
INSERT INTO SELECT
INSERT INTO VALUES
NULL
VBA Optional for sub parameters
DateTime reserved keyword
Numeric values

06. Append Query 2 (12:48)
Create ProductArchiveT
Archive historic product info
INSERT INTO SELECT FROM
WHERE Condition

07. Make Table Query (6:19)
SELECT INTO FROM
Creating time-based table backups

08. Delete Query (7:04)
Add IsActive to Listbox
DELETE FROM WHERE

09. Top X Items 1 (13:35)
SELECT TOP
SELECT TOP PERCENT
Top X Records Form

10. Top X Items 2 (15:42)
Custom TopStr
InStr function
Replace function
Ascending Descending Combo
Open Report with Custom SQL
Forms!FormName!Field.Property
.RowSource
.RecordSource

11. Aliases (9:57)
SELECT AS
Cartesian Product
Alias field names
Alias table names
AS optional with table names

12. Joins 1 (10:24)
Types of Joins
Inner Join
Equi-Join
Left, Right Outer Join
Full Join
Cartesian Join
Self Join
INNER join two tables
Joining multiple tables

13. Joins 2 (11:33)
LEFT JOIN
Left Outer Join
Right Outer Join
Full Joins not support in Access
SELF Join
Employees and Supervisors
Alias in the Access QBE grid
Cartesian Example
Softball Team Schedule

14. More with IN (4:08)
IN with SELECT statement
NOT IN

15 Union Query (11:10)
UNION
UNION ALL
Simulating a Full Join Query

16. Crosstab Query (9:01)
TRANSFORM
PIVOT
CrossTab like a PivotTable
Orders by State by Month

17. Calculations (5:13)
Operations
Addition, Subtraction
Multiplication, Division
Exponentiation
Integer Division
Modulus

18. Concatenation (5:45)
Ampersand
Plus with NULL values

19. Aggregate Queries 1 (13:17)
Sum
Avg
Count(Field)
Count(*)
Max
Min
First
Last
Total Row
GROUP BY
Month function
Criteria on Group By Field
Criteria on Aggregate Field
HAVING keyword
Nested Query

20. Aggregate Queries 2 (11:14)
Where option
Expression option
SQL statement as WHERE condition
Show all below average orders

21. Functions 1 (9:17)
String Functions
LTrim
RTrim
Trim
Left
Right
Mid
Len
InStr
UCase
LCase
StrConv for proper case

22. Functions 2 (15:16)
Date & Time Functions
Format function
Format parameter values
Date
Time
Now
Month
Day
Year
Hour
Minute
Second
DatePart
TimePart
DateSerial
TimeSerial
DateAdd
DateDiff

23. Functions 3 (11:36)
Misc Functions
ABS
INT
FIX
ROUND
SQR
IsNull
Nz
IIF
CSTR
CINT
CLNG
CDBL
CCUR
CDATE
CDEC

24. Cascading Combo 1 (12:51)
Country > State > City
SQL RowSource
AfterUpdate

25. Cascading Combo 2 (10:39)
Reverse the order
City looks up state
State looks up country
Boxes requeried
DLOOKUP

26. Review (4:18)

 


 

 
 

Student Interaction: Access SQL Seminar Part 2

Richard on 5/17/2011:  This nearly five (5) hour long video seminar will pick up where Part 1 left off. You will learn more about the SQL programming language. We will focus on action queries, aliases, joins, crosstabs, union queries, aggregate functions, and more. This seminar has several goals. You will: 1. Learn about Action queries and when to use them 2. Discover how to launch queries from VBA code 3. Create Aliases for your field and table names 4. Learn the different types of Table Joins 5. Construct a Union query 6. Work with a Crosstab query 7. Perform calculations in your SQL statements 8. Explore the different Aggregate query functions 9. Utilize string, math, time/date, and other query functions Click here for more information on the SQL SEMINAR PART 2.
Mubeezi Micah on 5/17/2011: Wow! I have just had a pick at the contents of the seminar. Now i know why it took you a bit longer than you had earlier anticipated. Great work Richard!
Alex Hedley on 5/19/2011: Another Excellent Seminar.

What a great find with the & and +, that will save extra coding :)

[Loved the Superman 3 comment, check out a film called Office Space for other references about that.]

Reply from Richard Rost:

Thanks.

I love Office Space. One of my favorite movies. My wife and I were calling each other "no talent ass clowns" for weeks after seeing that the first time.

David Spens on 5/24/2011: I have watched but not yet learned and digested the whole of this seminar and have nothing bur praise for it and its presentation. Well done, Rick.
Can you help on one small point woncerning string concatenation, please.
Is there a code or character or other means to force a Line Feed and Carriage Return within a concatenation?
I have a database containing the following fields,
Address1, Address2, Address3, Town, County, PCode, and would like to amalgamate all those three into one field called Address but with each original field on a new line. Can that be done?
Many thanks,
David

Reply from Richard Rost:

Sure. You can say:

FullAddress: [Address1] & Chr(13) & Chr(10) & [Address2] & Chr(13) & Chr(10) & [Address3]

That will insert CARRIAGE RETURN and LINE FEED characters between your fields, giving you the desired effect.

Keep in mind, however, that I strongly recommend you leave all 3 address fields SEPARATE in your table, and just assemble them as needed in a query.


David Spens on 5/24/2011: Thank you so much Richard - it worked a treat, although because some of the intermediate fields were NULL I had to use the + sign in places, experimenting with the arrangement of +'s and &'s. This seems to work fine:
Address: [Address1] & Chr(13) & Chr(10) & [Address2]+Chr(13)+Chr(10) & [Address3]+Chr(13)+Chr(10) & [Town]+Chr(13)+Chr(10) & [County]+" " & [PostCode]
It has saved me a lot of headache with nested IIf's!
Many thanks,
David

Reply from Richard Rost:

Like that trick, huh? :)

Robert Fleming on 5/24/2011: I'm looking for the Access Student Database folder, can tell me how to get it.

Reply from Richard Rost:

Look here.

Mubeezi Micah on 5/28/2011: Dear Richard,

Once again you made me look a fool. While watching Video 10 time index 13.50, i couldn't believe my eyes. How i wish i had watched this single line of code 3 months ago? It looks so simple but never did it ever cross my brain that it is possible. Have you demonstrated using the RecordSource property like this before? If you ever did, then i missed that class.

Regards,

MICAH

Reply from Richard Rost:

I know I've showed you how to create a query and then use that same query to feed a form and a report, but I don't think I've ever showed you this exact technique before (pulling the RowSource property out of a listbox to use it as the RecordSource of a report). Pretty cool, eh? That's why you want to learn SQL... once you know it, you'll realize that it sits under almost EVERYTHING in your database.

What I really like is that it's SO much easier to quickly edit, append, or delete records once you know the SQL action queries. You can do with one line of code (DoCmd.RunSQL) the same thing that takes 10 to 15 lines of code to do with a recordset... and I love recordsets!

 David on 6/6/2011: Richard,

I finished the SQL 2 Seminar a few days ago and already applying it to my databases. Enjoyed the course and learned alot.
Are you going to release the SQL 2 Handbook? And are you still planning to release SQL 3?

Reply from Richard Rost:

Thank you. Yes, and yes. The handbook should be ready any day now. SQL3 will be coming soon. No date yet.

 Mubeezi Micah on 7/19/2011: Dear Richard,
I recently went through another scenario with using aliases which i will share.
For instance, at time index 1.00, If i wanted to say Product Name as Prod. Name, it wouldn't work because of the DOT after Prod. How could i add a dot in an alias? Do i add double quotes?
MICAH

eddy geijselaers on 9/24/2011: Dear Richard,
The Access Seminar SQL 2 Database is not yet available, can you tell when it will be?

With kind regards
eddy peanut

Rick: I'm embarrassed to say I can't find it! I don't think that I was planning on saving it because there isn't much to it... but I'll keep looking.

eddy geijselaers on 9/24/2011: Richard,
SQL 2 Seminar Database
I forgot to mention that some of us (me) still working with Access XP or other older versions. Is it possible to convert to the older versions from AC 2010?
again
eddy peanut

Reply from Richard Rost:

Yes, you can backward-convert an Access 2010 database (under File > Save & Publish). If you're using features that are unique to 2010, you will lose them, however. A simple database is no problem.

Rob Miller on 11/14/2011: Hello Richard... awesome seminar! I have a question about counts. At 03:35 you show count of orders = 9 (with one being null and not counted) and then you show total count of all records = 10. Apparently the Count(*) does not count all records in the table if you use a WHERE clause in your SQL. For example, if you add WHERE State = "NY" would return 5 OrderCount and 5 TotalCount. How would I go about seeing the count of orders from "NY" but total record count would be 10? I hope this makes sense.

Thanks!
Rob


Reply from Richard Rost:

If you add a WHERE clause, you're limiting your set of data. You would need to use two queries to generate both numbers (and then UNION them)... OR you could throw in a DCOUNT() function which could then count all of your records in the same query.

 Bonnie on 4/22/2012: Richard, I am having problems with the VBA working in this. I will send a email with my code and further explanation, because the format is larger!
Bonnie

BONNIE STAIB on 4/30/2012: Richard, on 4/22/12 I sent my discussion question briefly here, and a longer email with the code, thinking that was better. The longer email (direct to you) has not yet been connected to this, so I will paste it here and hope that works:
Richard, I am stuck in the mud! No matter how many times I have gone over Lesson 3 of SQL2Seminar I cannot get my VendorPriceUpdateF to alter by my choice of the category. I have the tables and queries you created – and they look identical to yours. The zoom SQL for the list box price list (@ 7:45) also looks the same as yours:
SELECT [ProductVendorNewPricingQ].[ProductID], [ProductVendorNewPricingQ].[ProductCode], [ProductVendorNewPricingQ].[ProductName], [ProductVendorNewPricingQ].[UnitPrice], [ProductVendorNewPricingQ].[Category], [ProductVendorNewPricingQ].[LastUpdated], [ProductVendorNewPricingQ].[NewPrice] FROM ProductVendorNewPricingQ ORDER BY [ProductName];
I think I am doing the VBA correctly and it also looks like yours (at 10:18):

Private Sub CategoryCombo_AfterUpdate()

ProductList.RowSource = "SELECT ProductID, ProductCode, ProductName, " & _
"UnitPrice, Category, LastUpdated, NewPrice FROM ProductVendorNewPricingQ " &
"WHERE Category = " & CategoryCombo & " " & _
"ORDER BY ProductName;"

End Sub

But I am not sure how to save the VBA after I put this in, other than Ctr-S. Maybe I am doing something wrong at this point. When I try to change the category in the categorycombo (at 10:48), eg looking at only keyboards I get an error message: “error accessing file. Network connection may have been lost.”

I have started over at least 6-8 times, have found typos or other coding mistakes and corrected them, but I can no longer see what could be wrong. Does this info help? Can you pull me out of the mud?

Thanks so much!
Bonnie

 Bonnie on 8/1/2012: Richard, I had gotten stuck in Lesson 3 of the SQL Sem 2 but have returned to it. I wrote the forum about it, but have now discovered my problem (at 7:45 on the video): I was doing the correct VBA for the afterupdate event, but doing it in the list box rather than the CategoryCombo property! So, that is solved now and I have finished Lesson 3 fine, except for 1 more question. At 12:58 in the video, when you show having no category listed (in order to get all the records) it looks like you just delete hard drive or whatever is chosen from the categorycombo box and immediately you get the list of all the records. I delete it, and then must press enter to get the list box to respond. Is that normal (but not obvious in your video? Or is there something wrong with mine? Thanks, Bonnie

Reply from Richard Rost:

Yep. You'll have to press ENTER or TAB or something to get that event to fire. Sorry if it wasn't obvious.

Bonnie on 8/3/2012: Thanks, Richard, for that clarification. It made me think and that is good! You do a good job of that in fact!
Bonnie

Reply from Richard Rost:

:)

shamsMomin on 9/8/2012: Hi Richard I create order entery form same as you make in access 302 Lesson now I want to change unit Cost on order Detail sub form in order form.when I run sql code after update event in unit Cost field Its works but its takes all productId from order DetailT and change all of them in ProductT and I want to change only 1 record.
Jim on 11/15/2012: Richard,

If a person wanted to update the original database with new products from the vendor would you just change the relationship in the table to include all records from each table?

Reply from Richard Rost:

You could do that, sure. If the products are all NEW then you don't have to worry about duplicates.

Jim on 11/29/2012: Richard,
I am thinking of updating my unit cost as a part of the receiving of the purchase order. Can't I just write the update SQL code into the build event of the receive PO button thus updating cost to current cost from the vendor? I would link the inventory file with the order detail file on inventory id since the orderdetail table would automatically have the matching inventoryid and vendorid as part of it's generation. I could also make sure that the date of receipt was after the last update date to make sure that I have the latest vendor cost...Does all this sound viable to you?

Reply from Richard Rost:

Sure... just be aware that the vendor could change his price between the time you order and the time the parts are actually received... but aside from that I see no reason why this wouldn't work.

Jes on 1/17/2013: Hi Richard. I am having a problem with lesson 3 and the after update event. My code is below..

Private Sub CategoryCombo_AfterUpdate()

ProductList.RowSource "SELECT ProductID, ProductCode, ProductName, UnitPrice, Category, LastUpdated, NewPrice FROM ProductVendorNewPriceQ WHERE Category = " & CategoryCombo & "ORDER BY ProductName;"

End Sub

When I run this I receive an error stating "Compile Error: invalid use of property" and in my WHERE statement, CategoryCombo is highlighted.

I have tried multiple times and written it right off of the video and have gotten the same error.

Thanks in advance for your help!!!

Reply from Richard Rost:

You need a SPACE before the "ORDER BY ProductName"

Otherwise you're going to get this:

...WHERE Category=100ORDER BY ProductName

See the problem? Always something simple, eh?

Ala Qabaja on 3/21/2013: Hey Richard,

I have a database that stores info for mothers with a yes/no field to indicate if the mother has a baby or not. The problem I am having is that, in this table I have duplicated values for mothers since the mother's ID is not the primary key. These duplicated values need to be deleted an they are one of the following two forms:
1- Repeated mother's ID but one record with yes for baby and other with no. I want the one with yes. (Note that it is okay to have no for other moms if their record is not repeated).

2- repeated record but with unique IDs and I want to deleted the one that ends with two zeros. For example C1666-12 and C1666-00 are repeated records and I only need the C1666-12.

Thanks in advanced for your help

Reply from Richard Rost:

This is going to be tricky. It's too in-depth for me to explain in great detail here. You're going to need some complex logic, a DCOUNT or two, a DELETE query, or a RECORDSET. If you can't figure it out, submit it via my TechHelp page and either I or Alex will do our best to help you with it.


MUBEEZI MICAH on 5/7/2013: Dear Richard,

I just reviewed this lesson at time index 12:14. I notice that the DateSerial function takes it Year, Month and Day but the result of the query is in Month/Day/Year format.
I wonder why it was designed this way!

MICAH

Reply from Richard Rost:

Yes, that's very odd indeed. Probably developed by two different teams at Microsoft. :)

Lone Vistoft on 6/10/2013: My VendorProductUpdateF only changes the UnitPrice with Markup range of 50%. This means the UnitPrice will not be changed when for example, Category: Hard Drive changed from 5% to 10%. What have I done wrong?

Reply from Richard Rost:

I don't understand your question. Can you elaborate, please?

Lone Vistoft on 6/13/2013: Hi Richard, You did not understand my previous question, so I will here try to clarify it a bit more. Here is my program:

Private Sub Kommandoknap9_Click()
LogIt "Price Updated", "Category" & CategoryCombo.Column(1)
If IsNull(CategoryCombo) Or IsNull(Markup) Then Exit Sub
Dim M
M = Format(Markup, "0,00")
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE ProductT INNER JOIN VendorProductT ON " & _
"ProductT.ProductCode = VendorProductT.PRID " & _
"SET ProductT.UnitPrice = [PPRICE]*(1+" & M & "), " & _
"ProductT.LastUpdated = Now() " & _
"WHERE ProductT.Category=" & CategoryCombo
DoCmd.SetWarnings True
RefreshProductList
End Sub

When VendorPriceUpdateF, Markup stands at 45%, nothing happens. When VendorPriceUpdateF, Markup stands at 50% is the Unit Price 100% bigger than the New Price. When VendorPriceUpdateF, Markup stands at 51% to 99 % is the Unit Price 100% bigger than the New Price. When VendorPriceUpdateF, Markup stands at 150% is the Unit Price 300% or three times larger than the New Price. Kind regards Lone


Reply from Richard Rost:

Make sure you DIM M as something. In this case, you probably want a DOUBLE. Don't bother formatting it, you're not displaying it anywhere.

Where is PPRICE coming from? If that's in the tutorial, I apologize, it's been two years since I recorded that lesson. Can't remember everything. :)

Lone Vistoft on 6/14/2013: PPRICE comes from VendorProductT. It is the supplier's price.

If you do not immediately see what the problem is. You do not need to go more into it for my fault. I count on that this would be a one-time error. I have used the wrong table several times during the following exercises, without this has caused any problems.
Kind regards Lone

Zied Ben Afia on 7/9/2013: Hi
when I write my SQL codes (on Access 2010), it doesn't turn to capital letters like VBA does... anybody knows how to fix it ?

Reply from Richard Rost:

The query editor for SQL VIEW doesn't auto-capitalize field and table names like the VBA editor does. It's not a problem on your system... that's just how Access is.

Jyotsana J on 12/31/2013: Hi Richard
I am having trouble working SQl code after update where we select single category such as keyboard to shows just keyboard records. I have seen the videos so many times but SQL is not working. Here is my code
Private Sub CategoryCombo_AfterUpdate()


ProductList.RowSource = "SELECT ProductID, ProductCode, ProductName, " & _
"UnitPrice, Category, LastUpdated, NewPrice FROM ProductVendorNewPricingQ " & _
"WHERE Category = " & CategoryCombo & " " & _
"ORDER BY ProductName;"


End Sub

Please tell me where i am wrong.

Jim Ogier on 1/23/2014: Having a problem getting a code to be accepted in the code builder section.
Here is the code that was built in the query builder but will not be accepted in the code builder.

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

As written it works if I change the group number in the query but I want to run it through a combo box to select the group, however I keep getting an error message that it is expecting the end of Sub and the code is highlighted in red.

Any Ideas where I should look to resolve this problem? Jim O



Bonnie on 1/25/2014: Richard, I am working on SQL Seminar 2, lesson 4. At 4:52 in the video you format the markup box as percentage. I do this, but it does not show as such. I saved and closed the form, but -- unlike when you closed and reopened-- it did not change to a percentage. I gave up and decided to continue, but at 12:34 you can change the Markup value manually. I try and Access gives me a message: The value you entered isn't valid for this field. Eg, you may have entered a text in a numberic field or a number that is larger than the FieldSize permits." I tried typing 0.06, 6, and 6% but none were accepatable. I am working in Access 2007. I can choose which category I want from the CategoryCombo and it works, but it shows the percentage as 0.05 or 0.75 in the markup box. It shows it as a percentage in the CategoryCombo. My UpdatePricing button works fine, but again, Bonnie
Jerry Parkinson on 1/29/2014: Comments:

Having the databases for SQL 2 & SQL 3 would be very nice. Really nice, I think. What do you say?

Questions:
Also, why did you not use .value when converting from a percent to a number in #4 so you would not have to use a temp variable?

David Koehler on 6/1/2014: Been doing SQL for 30 years and needed a quality refresh, especially the differences between Access SQL and the other corporate variants I know. Very, very good!!!
Robert J on 9/6/2014: Richard
I purchased this seminar hoping that you would provide instructions on how to write SQL code to make a table in another data base file. I cannot see that you have covered this anywhere. Specifically I am trying to create a command button in a form that prompts for a DB file location and then creates a table in that file. Can you assist me with a SQL phrase that would accomplish this?

Reply from Richard Rost:

I cover MODIFYING table design in Part 3. However, I'm pretty sure you can't use SQL statements alone to modify tables in a linked (other) database file. You can only do that with DAO Recordsets.

Patrick Trekels on 11/16/2014: Hi Richard,

Does this also work within subforms ? (Multiple forms)



Reply from Alex

Do you mean with Cascading Combos?
You just need to make sure you reference the forms correctly.

=Forms!FormName!FieldName

=Forms!ParentForm!SubForm.Form!FieldName

Marc S on 3/31/2015: Hi Richard.
In response to your inquiry for future seminars and access lessons, I would suggest an comprehensive seminar on syntax. I always understand what i need to put in my sql statement but I always err when it come to the syntax. I loose hours on a missing space or quotation mark. It's quite frustrating to waste time on syntax, wish there was a complete guide somewhere.

For a specific example : on a form I have two buttons that load a report based on the value loaded in txt box.

The first button loads the report based on on an id (number) works great.
WHERE "[RubricID]=" & [GbselectedrubricID]

The second button loads the report according to a second text box (date).
WHERE "[Dateofeval]=" & "#" & [GBdateselected] & "#"

These two button work great on their own but when I combine them into one
WHERE "[Dateofeval]=" & "#" & [GBdateselected] & "#" AND "[RubricID]=" & [GbdateselectedID]
Does not work, logically it should work I do not understand why, I blame it on syntax.
Marc

Reply from Alex Hedley:

You need to be careful with where your quotes are.
1 tip would be to write the whole WHERE in quotes first
"WHERE [RubricID]=1"
Now you want to replace the 1 with a variable x
"WHERE [RubricID]=" & x

WHERE "[RubricID]=" & [GbselectedrubricID]
=>
"WHERE [RubricID]=" & [GbselectedrubricID]

You need to move the quote here and you don't need the extra &
WHERE "[Dateofeval]=" & "#" & [GBdateselected] & "#"
=>
"WHERE [Dateofeval]=#" & [GBdateselected] & "#"

WHERE "[Dateofeval]=" & "#" & [GBdateselected] & "#" AND "[RubricID]=" & [GbdateselectedID]
=>
"WHERE [Dateofeval]=#" & [GBdateselected] & "# AND [RubricID]=" & [GbdateselectedID]

Do you see how you've closed your quote too early
& "#" AND "[Ru ...

Another option is to build the Query first in the Query Builder.
Change to SQL View
Copy Paste this into your VBA, then switch out the values for variables

You could also build your strSQL string then
Debug.Print strSQL
Then you can see what you've built.

Robert Jacoby on 6/18/2015: Richard,
I have been trying to apply the information presented in the seminar to build a make table query. My intent is to create a table in a another database. Every thing works with the exception of the IN statement: INTO [Project Directory] IN [ProjectPath]

[ProjectPath] is a text field derived from Launch CD command. It is the location of the database for the new table.
In this case it is: \DUAL\Central\PROJECTS\2014-07 USDB Salt Lake Center\00-General\Project Data\Project Data.mdb"

When I run the query I receive the following message: Could not find file'\\Dual\Central\ProjectPath'

Do you have any idea how i can modify the IN statement to recognize the full path name?

Reply from Alex Hedley:

You could just use the Linked Table Manager to link to the other db then use a normal INSERT.

Can you build up your SQL as a String and Debug.Print it before you run the command to see exactly what it says.
There is a space in the folder name you will need to quote the string.
I take it you can copy paste the full path into Explorer and it opens.

Ferida Oe on 7/13/2015: Hi Richard, how to format the number in the field of Category List without decimal place and right align.

Reply from Alex Hedley:

Is this a Listbox?
If it is there isn't much formatting you can do, you would have to swap it to a SubForm which would give more options.

Richard Lanoue on 10/15/2015: This is a blanket general question, Once in a while when I design a db, especially with Update Query Buttons, I get this weird popup that says save to clipboard, save to record and something else. It's very annoying. Infact, once I clicked save Record and the fields in my record when into Asian icono graph. What causes it, why does it do that, how do I make it not do that?????

Reply from Alex Hedley:

Do you have a screenshot of the error you could share?

Richard Lanoue on 1/2/2016: I'm having a problem with criteria... on my reportformF I have a list of states if I so desire to narrow my criteria of records to a specific state. I use Forms!ReportInputF!StateCombo. If MA shows up I only get Companies from MA etc... But if it's null, I get nothing...I want the companies to show up regardless of what state. How do I do that? is there a specific lesson that dealt with it?

Reply from Alex Hedley:

I think B5 shows NULLS

You can check with an OR IS NULL

Richard Lanoue on 1/22/2016: Can you do this but instead of from a Table, From an SQL that was generated by a search Form?

Reply from Alex Hedley:

Are you wanting to create a Table from SQL?

Richard L on 1/22/2016: Yes, I made an input form not unlike the ones from the "Search Seminars" usind VB Code(Lesson 23). If I had a query I could just use.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QUERYNAME", _
"C:\Users\Rix Computer\Desktop\TRUSTED\Tonys Project\Results.xlsx"

BUT I don't have a query I have a vb code that takes an SQL string and places it in a listbox:

ResultsList.RowSource = SQL

I made a query that on the ID I putas a criterion. Forms!InputF!ListBox...
problem it only takes the record highlighted in the listbox, not all the records in the listbox once I requery it to do a search.

This wouild be clearer if I could email you an attachment and you can see what I'm getting at...

I want an input form where I enter specific WHERE conditions hit search and generate a result in the listbox and then have ALL the results export to a spreadsheet... I have made a request to even pay for this help.

Reply from Alex Hedley:

Unfortunately TechHelp isn't available at the minute, I'm sure there's a message on the page.

I'm hoping for Rich to release a written tutorial I've done on Exporting Objects from Access which covers this in much detail but for now I'll show you a quick example:


Dim db As DAO.Database
Dim qdf As QueryDef

Dim strTempTable As String: strTempTable = "tblTempExport"
Dim strTempQuery As String: strTempQuery = "qryTempExport"

strSQL = "SELECT ..."

On Error Resume Next
DoCmd.DeleteObject acTable, strTempTable
On Error Resume Next
DoCmd.DeleteObject acQuery, strTempQuery

strSQL = strSQL & "... INTO " & strTempTable & " FROM ...;"

Set db = CurrentDb()
Set qdf = db.CreateQueryDef(strTempQuery)
qdf.SQL = strSQL
qdf.Execute

qdf.Close


Now I'm putting data from a Query into a Table so I use the Table elsewhere, you could just use the query you've created.

Now you can use this Query in your export command.

Errol Babel on 2/21/2016: hello your the best, where can i find the
sample database files for Access SQL 2 and 3 . i cant not find them in the samples.

Reply from Alex Hedley:

There weren't any for that course, you could follow along and make them for yourself or it's in the handbook to copy from

James Gray on 12/8/2016: Richard,

I have owned this seminar for quite a while now and am using it to develop SQL to put into existing forms and reports to replace formal queries as the data source. In one instance I have a crosstabe query that is the data source for one of my reports. The query works just fine for the report but when I copy the SQL and place it into the report data source I get an error: "Cannot use the crosstab of a non-fixed column as a subquery". The report uses only one query as its datasource and that query is not dependent upon any other query. If I return to using the query itself as the datasource, everything returns to normal and the report runs fine. Any ideas?

Reply from Alex Hedley:

So you aren't setting the SQL in vba you are copy and pasting it into the Record Source of the object? I'm not sure why this would be better that using a Query Object.

James G on 12/15/2016: Just trying to clean up the many, many queries and trying to learn SQL so I might start writing queries into the datasource myself, no other reason at this point. My curiosity is that it works when it is a query without issue and does not work when I copy and paste the SQL into the datasource.
 

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

 

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