Query Training

[Teri Bridges]

Can anyone recommend any training materials for writing queries. I have watched a lot of different videos but none seem to get me where I need to be. I want to learn but have not been able to find useful training materials. I could use help with writing if statements in quires.

 

[CJ_London]

can't recommend any training materials per se. Use of the iif function is very specific. Suggest you explain what you are trying to do

 

[plog]

W3 schools s a great site for intros into most languages:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Then for access specific functions I use tech on the net:

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

 

[Pat Hartman]

If you don't know how to write SQL and even if you do, use the QBE. Point and click makes constructing a query very easy. If you want to use functions, you can but be very careful in which functions you use. You want to stick with functions that have direct SQL equivalents such as IIf() and Format()

There are lots of books on writing queries for SQL Server and other "big" databases but very few that concentrate on Access SQL which is 30 years behind the times in the world of SQL. It does all the basics and you can use VBA and UDA functions to expand its reach but it is a blast from the past.

The most useful book I could find on my shelf for your question is
Access Data Analysis Cookbook by Ken Bluttman & Wayne Freeze published by O'Reilly. Published in 2011.

SQL Cookbook by Anthony Molinaro published by O'Reilly in 2012. this one covers SQL for SQL Server, PostgreSQL, Oracle, MySQL, and DB2 but sadly not Access so you have to convert the SQL and you still may not have the features you need. But seeing several versions of the same SQL string can be illuminating and might guide you to an Access solution.

And finally, a really basic book that deals only with select queries. The downside is it is specifically for SQL Server but it is so basic, the queries will almost certainly work in Access. If you want to use the data they are using , you have to download the SQL Server version of the Northwind database. The data in the SQL version is different from that in the Access version so your results would vary so this may be more trouble than it is worth but if you have SQL server express installed, you can download the .bak file and restore it. Then link to the SQL Server database and write the queries with Access
SQL Practice Problems, Sylvia Moestl Vasilik 2016

 

[Teri Bridges]

can't recommend any training materials per se. Use of the iif function is very specific. Suggest you explain what you are trying to do

This is going to be a dumb question I know, but I cannot figure it out. I have a column in my table that contains classroom delivery hours
Class 1) 1.5
Class 2) 3.0
Class 3) 4.0 ect.,

All I want to do is query the table for a total of all those hours. Everything I have seen is much more in depth like totaling how many sales by employee or how many purchases by one customer. I just want to total that column for example the above = 8.5. Yes I am very new but I am trying. I understand querying is asking the database a question, I just don't seem to know how to ask the right question.

 

[arnelgp]

1. on ribbon, you select Query Design.
2. drag the table you want to query to the query designer (Gray Area).
3. double click the delivery hours field from your table to include it in the query.
4. on the Ribbon->Query Design->click Total (sigma symbol).
5. change the "Group By" (on lower Pane) to Sum.
6. View your query.

 

[Teri Bridges]

1. on ribbon, you select Query Design.
2. drag the table you want to query to the query designer (Gray Area).
3. double click the delivery hours field from your table to include it in the query.
4. on the Ribbon->Query Design->click Total (sigma symbol).
5. change the "Group By" (on lower Pane) to Sum.
6. View your query.

I tried that and no go. It changed the field header but data is the same no total anywhere. I do not see where it added up the numbers to give me a total of hours.

 

[Teri Bridges]

I am loving this site, Thank you so much!

 

[SHANEMAC51]

I tried that and no go. It changed the field header but data is the same no total anywhere. I do not see where it added up the numbers to give me a total of hours.

 

[arnelgp]

if you only need the Total hours, just add one field (the hour field).
then sum it.

you also get a free pack (Access Data Analysis Cookbook.pdf)(no expensed paid, limited time only):

Meet Google Drive – One place for all your files

Google Drive is a free way to keep your files backed up and easy to reach from any phone, tablet, or computer. Start with 15GB of Google storage – free.

drive.google.com

 

[SHANEMAC51]

I am loving this site, Thank you so much!

remove unnecessary fields, leave only the necessary grouping fields and fields for the SUM SUM, the ID field can be applied to the quantity, the COUNT function

the problem is that someone else's information is on the site, you need to study on your own base, although you still need to read the textbook