Chester Tugwell is a freelance Microsoft Office trainer and owner of Blue Pecan Computer Training based in Sussex, UK. He provides a comprehensive set of Access training courses as well as other Microsoft Office training options.
What does QBE mean (QBE grid)
Query By Example – a method of obtaining data pioneered by IBM in the 1970s. Access converts the query design within the grid to Structured Query Language (SQL).
Change the Column Name in a Query
In the field row of the QBE grid, write the new name to the left of the original field name followed by a colon:
NewName: OrginalName
Show Unique Values Only in a Query
In design view right-click and choose Properties. Set the Unique Values property to ‘Yes’.
Show Records relating to the current Year, Month or Day
Year([Order Date])=Year(Now()) And Month([Order Date])=Month(Now()) And Day([Order Date])=Day(Now())
Add Records from one Table to Another
Append query
Delete Specified Records in a Table
Delete query
‘Key Violations’ error message
Occurs when an append query is attempting to add records where the values in the key field are the same as those that already exist. Duplicate values are not allowed in a key field.
Use an update query:
• to change values with a calculation eg [Price]+1.50
• or to update with values from another table [New Table Name].[New Field Name]
Converting Text Case
Type Ucase or Lcase function in field name row of QBE grid to the left of the existing field name which should be enclosed in brackets.
Ucase([FieldName])
You may need to rename the field:
NewName: Ucase([FieldName])
Show all Fields without adding them to the QBE Grid
In the properties of the query, set the OutPut All Fields property to Yes.
To show all fields for all future queries – Tools | Options | [Tables/Queries] – Output All Fields.
Create a Parameter Value that allows you to enter Partial Criteria
Concatenate the parameter value and the wildcard
Like [Parameter:] & “*”
This will also show any empty field records when a parameter value is given.
Like [Parameter:] & “*” Or Is Null
To pick up records with blank field entries only, type a space in the parameter dialogue box.
Create a Parameter Value that allows you to enter Partial Criteria (ends with)
Concatenate the parameter value and the wildcard
Like “*” & [Parameter:]
Create a Parameter Value that allows you to enter Partial Criteria (contains)
Concatenate the parameter value and the wildcard.
Like “*” & [Parameter:] & “*”
Incorporating a LookUp Table
Lookup table may show discounts available on products dependent on price range – fields would be StartPrice, EndPrice, Discount. Create a select query using the Product table (that includes the price of the product) and the lookup table. In the criteria row under the product price field enter the following Between operator.
Between [LookupTable].[StartPrice] And [LookupTable].[EndPrice]
Add the Discount field to the QBE grid to retrieve the discount appropriate to the price of each product.
Refreshing a Crosstab Query
Close and reopen.
Printing the SQL for Queries
Tools | Analyse | Documentor [Queries]
The Options button allows you to select what information will appear in the document
IF Statements
IIF([Field] condition, true, false)
Order Discount:IIf([SumOfQuantity]>2,([Book Price]-([Book Price]*0.1)),[Book Price])
Using Calculations that Involve Expression Fields when Totalling in Queries
In the calculating field that uses expression fields, set Group By to Expression.
Extracting Elements of a Date
Create a custom date in the field properties, using the following syntax:
DD - 05
DDD - Sun
DDDD - Sunday
MM - 12
MMM -Dec
MMMM - December
YY - 99
YYYY - 1999
When a Query Returns an Impossible Number of Records
There is no relationship between the tables included in the query, so every record in one table is linked with every record in another table.
Ways to Tune a Query so it runs Quickly
• Run the performance analyser
• Index the fields that join the tables in the query
• Index fields that contain criteria
• Compact the database
• Avoid the Not In operator
- Related Videos
- Related Articles
- Ask / Related Q&A
- Excel Tips on Conditional Formatting
- 7 More Things you Should Know About Microsoft Word
- proven tips for Microsoft Xbox 360 repair
- 5 Quick & Dirty Tips For Using Microsoft FrontPage To Make Better Looking Websites
- 7 Things you Should Know About Microsoft Word
- Five Tips and Tricks for Using Word for Business
- Microsoft Word Tips - Training
- Microsoft Training Certifications




Know more about compact flash 16gb
By: philipwots | 31/12/2009It is almost a known fact that compact flash 16gb is a mass storage device format which is used in portable electronic devices in the market.
Pass4side 000-111 exam questions
By: Adela1987 | 30/12/2009Pass4side 000-111 Exam Resources include Questions and Answers, Practice Testing Software, Stud Guides, Audio Learning and Preparation Labs. These IBM 000-111 Exam Preparation Materials will make you provide the accurate answers of real 000-111 exam questions. With our Exam Resources you can test your knowledge and readiness for exam, assess your performance in a given time, get scores and highlighted weaknesses with suggestions to improve the weak areas.
8 GB USB DRIVE
By: Phillip Roth | 29/12/2009USB flash drive consists of a small printed circuit board carrying the circuit elements and a USB connector insulated electrically and protected inside a plastic, metal, or rubberized case which can be carried in a pocket.
Support Information About AA Online
By: Ed Philips | 26/12/2009Discover how to quit drinking as revealed in Ed Philips "Alcoholics Anonymous Online" support guide, which offers tried and tested alcoholic addiction methods to quit drinking within 21 days.
Know the information USB sticks
By: John Milton | 24/12/2009Now in the market there is high demand of usb sticks among its users. Seeing this the producers of usb sticks now focussing more on its reliability.
A Photography Course For All The Family
By: Dan Feildman | 23/12/2009Excellent Digital Photography Courses Introducing Free Digital Photography Guide called "How To Take Beautiful Digital Pics Made Simple". The latest tips on how to use a digital camera like a pro.
Reinforce Database Management with Remote DBA
By: Kashif Mukhtar | 23/12/2009Databases are the foundation of the digital economy. They store the information that run businesses and organize the vast amount of data that is created on a regular basis. Even though e-business infrastructures are requiring stronger database support to sustain their 24x7 operations, shrinking IT budgets are forcing companies to reduce the size of their DBA staff. With databases growing in size and complexity, the role of the already overburdened DBA is becoming increasingly difficult.
Download Full Version Movies - Downloading Movies Have Never Been Easier - Download as much and as often as you like for one fee!
By: Dimitar Mishev | 22/12/2009We as movie watchers have come a long way since the introduction of movie tapes and watching movies from our home. From beta max to the VCR tapes, we have rented and recorded thousands of movies and still do.
Creating a User Defined Function in Excel using VBA
By: Chester Tugwell | 10/07/2009 | SoftwareExcel includes many different functions that help you complete calculations, but have you ever wished for a function that that doesn’t exist in Excel? If you have, this tutorial will explain how to create a function DIY style.
Excel SUMIFS Function
By: Chester Tugwell | 03/07/2009 | SoftwareSUMIFS is a function that first appears in Excel 2007. You may have previously used SUMIF to sum up values in a list based on criteria in another column. SUMIFS works the same way but allows you to use multiple criteria either in different columns or the same column.
Excel Mortgage Payment Formula
By: Chester Tugwell | 22/06/2009 | SoftwareA walk through of the PMT function in Excel which allows you to calculate monthly repayments on a mortgage.
Tips and Tricks for working with Microsoft Access Reports
By: Chester Tugwell | 18/06/2009 | DatabasesA selection of tips and tricks for working in Microsoft Access reports. Not all tips will be relevant to all versions of Access
Tips and Tricks for working with Microsoft Access Forms
By: Chester Tugwell | 18/06/2009 | DatabasesA selection of tips for working with Microsoft Access Forms. Not all tips will be relevant to all versions of Access.
Tips and Tricks for working with Microsoft Access Queries
By: Chester Tugwell | 18/06/2009 | DatabasesA collection of tips and tricks that you may find useful when working with queries in Microsoft Access.
Displaying Parameter Values in a Microsoft Access Report
By: Chester Tugwell | 18/06/2009 | DatabasesIf you use parameter queries and then base reports on those queries this tutorial is for you. It will show you to display the parameter values that have been used in the heading of the report.